Group by multiple columns creating new column in pandas dataframe

Question

I have a pandas dateframe of two columns ['company'] which is a string and ['publication_datetime'] which is a datetime.

I want to group by company and the publication_date , adding a new column with the maximum publication_datetime for each record.

so far i have tried:

issuers = news[['company','publication_datetime']]
issuers['publication_date'] = issuers['publication_datetime'].dt.date
issuers['publication_datetime_max'] = issuers.groupby(['company','publication_date'], as_index=False)['publication_datetime'].max()

my group by does not appear to work.

i get the following error

ValueError: Wrong number of items passed 3, placement implies 1

jeffhale · Accepted Answer · 2022-02-23 02:36:08Z

1

You need the transform() method to cast the result in the original dimension of the dataframe.

issuers['max'] = issuers.groupby(['company', 'publication_date'])['publication_datetime'].transform('max')

The result of your groupby() before was returning a multi-indexed group object, which is why it's complaining about 3 values (first group, second group, and then values). But even if you just returned the values, it's combining like groups together, so you'll have fewer values than needed.

The transform() method returns the group results for each row of the dataframe in a way that makes it easy to create a new column. The returned values are an indexed Series with the indices being the original ones from the issuers dataframe.

Hope this helps! Documentation for transform here

edited Feb 23, 2022 at 2:36

jeffhale

4,1127 gold badges44 silver badges57 bronze badges

answered Mar 17, 2020 at 16:04

economy

4,2717 gold badges31 silver badges37 bronze badges

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

Comments

Jules · Accepted Answer · 2020-03-17 16:31:45Z

0

The thing is by doing what you are doing you are trying to set a DataFrame to a column value. Doing the following will get extract only the values without the two indexe columns:

issuers['publication_datetime_max'] = issuers.groupby(['company','publication_date'], as_index=False)['publication_datetime'].max().tolist()

Hope this help !

edited Mar 17, 2020 at 16:31

answered Mar 17, 2020 at 16:04

Jules

4557 silver badges18 bronze badges

Collectives™ on Stack Overflow

Group by multiple columns creating new column in pandas dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related