CODEWITHSUNDEEP
pythonpandasaggregateaggregate-functions
dingaro
dingaro

Reputation: 2342

How to aggregate DataFrame to stay rows with the highest date and add new column in Python Pandas?

I have DataFrame in Python Pandas like below ("date_col" is in "datetime64" format):

ID  | date_col   | purchase
----|------------|-------
111 | 2019-01-05 | apple
111 | 2019-05-22 | onion
222 | 2020-11-04 | banana
333 | 2020-04-19 | orange

I need to aggregate above table in the following way:

  1. add column "col1" with number of purchases which was made by client ("ID")
  2. If some client ("ID") is duplicated - stay only one row with the highest date

So as a result I need something like below:

ID  | date_col   | purchase | col1
----|------------|----------|-----
111 | 2019-05-22 | onion    | 2
222 | 2020-11-04 | banana   | 1
333 | 2020-04-19 | orange   | 1

Upvotes: 1

Views: 59

Answers (4)

baifeng
baifeng

Reputation: 1

df['col1'] = df.groupby('ID')['ID'].transform('count')
df.sort_values('date_col').drop_duplicates('ID',keep='last')

Upvotes: 0

Ynjxsjmh
Ynjxsjmh

Reputation: 30050

You can try create a new count column using groupby.transform and get the max date by selecting with groupby.idmax

df['date_col'] = pd.to_datetime(df['date_col'])
df = (df.assign(col1=df.groupby('ID')['purchase'].transform('count'))
      .loc[lambda df: df.groupby('ID')['date_col'].idxmax()])

print(df)

    ID   date_col purchase  col1
1  111 2019-05-22    onion     2
2  222 2020-11-04   banana     1
3  333 2020-04-19   orange     1

Upvotes: 1

eshirvana
eshirvana

Reputation: 24603

here is one way:

df['col1'] = df.groupby('ID')['ID'].transform('count')
df = df.sort_values('date_col').groupby('ID').tail(1)

output:

>>
    ID    date_col purchase  col1
1  111  2019-05-22    onion     2
3  333  2020-04-19   orange     1
2  222  2020-11-04   banana     1

Upvotes: 1

Shubham Sharma
Shubham Sharma

Reputation: 71707

Assuming the dataframe is sorted on date_col column, you can use groupby:

g = df.groupby('ID', as_index=False)
g.last().merge(g.size())

    ID    date_col purchase  size
0  111  2019-05-22    onion     2
1  222  2020-11-04   banana     1
2  333  2020-04-19   orange     1

Upvotes: 1

Related Questions