Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in Data Science by (36.8k points)

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()

1 Answer

0 votes
by (18.4k points)

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 

Browse Categories

...