CODEWITHSUNDEEP
pythonpandas
spidermarn
spidermarn

Reputation: 939

Python Data-wrangling

I have a dataframe in Python below:

print (df)
         Date  Hour  Weight
0  2019-01-01     8       1
1  2019-01-01    16       2
2  2019-01-01    24       6
3  2019-01-02     8      10
4  2019-01-02    16       4
5  2019-01-02    24      12
6  2019-01-03     8      10
7  2019-01-03    16       6
8  2019-01-03    24       5

How can I create a column (New_Col) that will return me the value of 'Hour' for the lowest value of 'Weight' in the day. I'm expecting:

Date       Hour  Weight New_Col
2019-01-01  8    1      8
2019-01-01  16   2      8
2019-01-01  24   6      8
2019-01-02  8    10     16 
2019-01-02  16   4      16
2019-01-02  24   12     16
2019-01-03  8    10     24
2019-01-03  16   6      24
2019-01-03  24   5      24

Upvotes: 1

Views: 142

Answers (1)

jezrael
jezrael

Reputation: 863166

Use GroupBy.transform with DataFrameGroupBy.idxmin, but first create index by Hour column for values from Hour per minimal Weight per groups:

df['New'] = df.set_index('Hour').groupby('Date')['Weight'].transform('idxmin').values
print (df)
         Date  Hour  Weight  New_Col  New
0  2019-01-01     8       1        8    8
1  2019-01-01    16       2        8    8
2  2019-01-01    24       6        8    8
3  2019-01-02     8      10       16   16
4  2019-01-02    16       4       16   16
5  2019-01-02    24      12       16   16
6  2019-01-03     8      10       24   24
7  2019-01-03    16       6       24   24
8  2019-01-03    24       5       24   24

Alternative solution:

df['New'] = df['Date'].map(df.set_index('Hour').groupby('Date')['Weight'].idxmin())

Upvotes: 2

Related Questions