Pandas: Max or min function according to another column

Question

I have this data frame:

| 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 wanted to create a new data frame where I can get min value in the column Numb if my string in the column Word is ab and max value if my string is bc for each Date. For example, The output of the data frame should be:

| | | Numb |

| Date | Word | |

|------------|------|------|

| 2020/01/01 | ab | -12 |

| | bc | 34 |

| 2020/01/02 | ab | -8 |

| | bc | 123 |

I am using groupby function, but it only produces a data frame with a min value in all the cases:

ans=df.groupby(['Date','Element']).min()

Answer 1

You can chain with groupby and pass a 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

Do check out Python Data Science Course which helps you understand from scratch