pandas reset index after performing groupby and retain selective columns

user_8484261 0
Viewed 14801

I want to take a pandas dataframe, do a count of unique elements by a column and retain 2 of the columns. But I get a multi-index dataframe after groupby which I am unable to (1) flatten (2) select only relevant columns. Here is my code:

import pandas as pd
df = pd.DataFrame({
'ID':[1,2,3,4,5,1],
'Ticker':['AA','BB','CC','DD','CC','BB'],
'Amount':[10,20,30,40,50,60],
'Date_1':['1/12/2018','1/14/2018','1/12/2018','1/14/2018','2/1/2018','1/12/2018'],
'Random_data':['ax','','nan','','by','cz'],
'Count':[23,1,4,56,34,53]
})

df2 = df.groupby(['Ticker']).agg(['nunique'])

df2.reset_index()

print(df2)

df2 still comes out with two levels of index. And has all the columns: Amount, Count, Date_1, ID, Random_data.

How do I reduce it to one level of index?

And retain only ID and Random_data columns?

pandas python
2 Answers

Try this instead:

1) Select only the relevant columns (['ID', 'Random_data'])

2) Don't pass a list to .agg - just 'nunique' - the list is what is causing the multi index behaviour.

df2 = df.groupby(['Ticker'])['ID', 'Random_data'].agg('nunique')
df2.reset_index()

  Ticker  ID  Random_data
0     AA   1            1
1     BB   2            2
2     CC   2            2
3     DD   1            1
user_102015800

Use SeriesGroupBy.nunique and filter columns in list after groupby:

df2 = df.groupby('Ticker')['Date_1','Count','ID'].nunique().reset_index()
print(df2)
  Ticker  Date_1  Count  ID
0     AA       1      1   1
1     BB       2      2   2
2     CC       2      2   2
3     DD       1      1   1
user_29010020
Related