Python Data-wrangling

Question

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

jezrael · Accepted Answer · 2019-05-19 17:25:00Z

2

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

edited May 19, 2019 at 17:25

answered May 19, 2019 at 17:19

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

cs95 Over a year ago

All your codes just return 8, so your print(df) output is misleading.

jezrael Over a year ago

@cs95 - ya, unfornately first and second sample data are different. I first cannot find problem why all values are 8, but then I notice it.

cs95 Over a year ago

I came up with this answer before you had posted anything it but I refrained from posting because the data was wrong. Sometimes the best action is to not answer. Just my 2c. Better inform OP so they can fix it first.

jezrael Over a year ago

Ya, created solution and 3 minutes find problem, then found it, so post answer.

Collectives™ on Stack Overflow

Python Data-wrangling

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related