CODEWITHSUNDEEP
pythonpython-3.xpandasdataframepandas-groupby
daiyue
daiyue

Reputation: 7448

How to groupby on a column while doing sort on another column?

I have the a df,

   date    amount    code    id
2018-01-01   50       12      1
2018-02-03   100      12      1
2017-12-30   1        13      2
2017-11-30   2        14      2

I want to groupby id, while in each group the date is also sorted in ascending or descending order, so I can do the following,

grouped = df.groupby('id')

a = np.where(grouped['code'].transform('nunique') == 1, 20, 0)
b = np.where(grouped['amount'].transform('max') > 100, 20, 0)
c = np.where(grouped['date'].transform(lambda x: x.diff().dropna().sum()).dt.days < 5, 30, 0)

Upvotes: 1

Views: 83

Answers (2)

Matina G
Matina G

Reputation: 1582

Adding to the previous answer, if you wish indexes to remain as they were, you might consider the following : import pandas as pd

df = {'a':[1,2,3,0,5], 'b':[2,2,3,2,5], 'c':[22,11,11,42,12]}
df = pd.DataFrame(df)
e = (df.groupby(['c','b', 'a']).size()).reset_index()
e = e[['a', 'b', 'c']]
e = e.sort_values(['c','a'])
print(e)

Upvotes: 0

Shaido
Shaido

Reputation: 28322

You can sort the data within each group by using apply and sort_values:

grouped = df.groupby('id').apply(lambda g: g.sort_values('date', ascending=True))

Upvotes: 2

Related Questions