Pandas: Max or min function according to another column

user_14068806 0
Viewed 585

I have the following dataframe:

| Date       | Word    | Numb |
|------------|---------|------|
| 2020/01/01 | ab      | 12   |
| 2020/01/01 | bc      | 24   |
| 2020/01/01 | ab      | -12  |
| 2020/01/01 | bc      | 34   |
| 2020/01/02 | ab      |  3   |
| 2020/01/02 | bc      | 123  |
| 2020/01/02 | ab      | -8   |
| 2020/01/02 | bc      | 12   |

I would like to create a new dataframe where I get the min value in the column Numb if the string in the column Word is ab and the max value if the string is bc for each Date. For example, the result with the above dataframe should be:

|            |      | Numb |
| Date       | Word |      |
|------------|------|------|
| 2020/01/01 | ab   | -12  |
|            | bc   | 34   |
| 2020/01/02 | ab   | -8   |
|            | bc   | 123  |

I am using the following groupby function,but it only produce a dataframe with the min value in all the cases:

ans=df.groupby(['Date','Element']).min()
pandas-groupby pandas python
5 Answers

We can chain with groupby and pass the result to np.where

s=df.groupby(['Date','Word']).Numb.agg(['min','max'])
s['number']=np.where(s.index.get_level_values(1)=='ab',s.min(1),s.max(1))
s
Out[38]: 
                 min  max  number
Date       Word                  
2020/01/01 ab    -12   12     -12
           bc     24   34      34
2020/01/02 ab     -8    3      -8
           bc     12  123     123
user_79645270

you can use pd.concat with each selected dataframe with the Word, then use sort_values once ascending True (to get the min first) and once False (to get the max first), then drop_duplicates and keep the first.

res = (pd.concat([df[df['Word'].eq('ab')].sort_values('Numb', ascending=True), 
                  df[df['Word'].eq('bc')].sort_values('Numb', ascending=False)])
         .drop_duplicates(['Date','Word'], keep='first')
      )
print(res)
         Date Word  Numb
2  2020/01/01   ab   -12
6  2020/01/02   ab    -8
5  2020/01/02   bc   123
3  2020/01/01   bc    34
user_92747320

IIUC,

groups = df.groupby(['Date','Word'], as_index=False)['Numb']
new_df = groups.min().where(lambda x: x['Word'].eq('ab'), groups.max())
print(new_df)

# s = new_df.set_index(['Date','Word']) #for expected output

Output

         Date Word  Numb
0  2020/01/01   ab   -12
1  2020/01/01   bc    34
2  2020/01/02   ab    -8
3  2020/01/02   bc   123
user_118842370

You can do some fancy reshaping, grouping and reshaping again:

dfs = df.set_index(['Date','Word'], append=True)['Numb'].unstack()
dfg = dfs.groupby('Date').agg(ab=('ab','min'), bc=('bc','max'))
dfg.stack().to_frame(name='Numb').rename_axis(['Date', 'Word'])

Output:

                   Numb
Date        Word       
2020/01/01  ab    -12.0
            bc     34.0
2020/01/02  ab     -8.0
            bc    123.0
user_63615310

You could stack the results after the aggregation :

df.groupby("Date").agg(["min", "max"]).stack().droplevel(-1)

         Word   Numb
Date        
2020/01/01  ab  -12
2020/01/01  bc  34
2020/01/02  ab  -8
2020/01/02  bc  123
user_71757130
Related