Pandas Dataframe Groupby multiple columns

Question

I have the following DataFrame:

FACTORY_ID  SUPPLIER_ID DELIVERY_DATETIME
A   1   05/09/2015 11:00
A   1   05/09/2015 11:00
A   2   05/09/2015 11:00
A   2   08/09/2015 11:00
A   1   08/09/2015 11:00
A   1   08/09/2015 11:00
A   1   08/09/2015 11:00
A   2   08/09/2015 11:00
A   2   13/09/2015 11:00
A   3   13/09/2015 11:00
A   3   13/09/2015 11:00
A   3   13/09/2015 11:00
A   3   13/09/2015 11:00
A   3   13/09/2015 11:00

which I intend to transform by using a GroupBy clause to show a summarized view of the data for presentation. (DELIVERY_DATETIME is a column that is concatenated and comma-delimited.) Desired results in this example:

FACTORY_ID  SUPPLIER_ID DELIVERY_DATETIME
A   1   05/09/2015  11:00:00 AM, 08/09/2015  11:00:00 AM
A   2   05/09/2015  11:00:00 AM, 13/09/2015  11:00:00 AM
A   3   13/09/2015  11:00:00 AM

Have tried GroupBy/drop_duplicates but have been unable to get what I wanted. How should I go about doing this?

BENY · Accepted Answer · 2018-08-16 04:02:45Z

3

IIUC groupby with agg

newdf=df.groupby(['FACTORY_ID','SUPPLIER_ID']).DELIVERY_DATETIME.agg(['first','last'])
newdf.loc[newdf['first']==newdf['last'],'last']=''
newdf
Out[69]: 
                             first        last
FACTORY_ID SUPPLIER_ID                        
A          1            05/09/2015  08/09/2015
           2            05/09/2015  13/09/2015
           3            13/09/2015

answered Aug 16, 2018 at 4:02

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

user1008697 Over a year ago

This might work. If I have multiple dates and would like to retrieve all dates, I could use 'nunique'. Just curious, how come first and last headers are at a level higher than the usual FACTORY_ID\SUPPLIER_ID? If I want it to be the same level (i.e. as a column) and reference it later for other purposes, is it still the same as accessing it via a df.column method?

user1008697 Over a year ago

ok i figured it out. This can be resolved via reset_index(). Thanks for the help!

Collectives™ on Stack Overflow

Pandas Dataframe Groupby multiple columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related