pandas - Compare values under multiple conditions of one column in Python

Question

I have the following data:

data = {
    "index": [1, 2, 3, 4, 5],
    "name": ["A", "A", "B", "B", "B"],
    "type": ['s1', 's2', 's1', 's2', 's3'],
    'value': [20, 10, 18, 32, 25]
}
df = pd.DataFrame(data)

I need to check if the value under same name follow constraint (say there only three type and not all exist under same name): s1 < s2 < s3, which means, under same name, if the value of s1 is smaller than s2 or s3, then return True, if s2 is smaller than s3, then return True. Otherwise, return False or NaN. Here is the output I expected:

    index   name    type    value   result
0     1      A       s1      20      False
1     2      A       s2      10        
2     3      B       s1      18      True
3     4      B       s2      32      False
4     5      B       s3      25        

How can I do it in Python? Thanks for your help.

Answer 1

Try:

#Use pd.Categorical to ensure sorting if column is not lexicographical ordered.
df['type'] = pd.Categorical(df['type'], ordered=True, categories=['s1','s2','s3'])

df['result'] = df.sort_values('type').groupby('name')['value'].diff(-1)

df['result'] = df['result'].lt(0).mask(df['result'].isna(),'')

df

Output:

   index name type  value result
0      1    A   s1     20  False
1      2    A   s2     10       
2      3    B   s1     18   True
3      4    B   s2     32  False
4      5    B   s3     25