CODEWITHSUNDEEP
pandasdataframe
Daniele M.
Daniele M.

Reputation: 31

Group and count entries in a DataFrame

I'm new to programming and Pandas, I'd like to have an example of how to apply a grouping function that also applies some counters to reduce the following DataFrame:

child groupName state
name1 A ok
name2 A ko
name3 B ok

to a new DataFrame like:

groupName noOfChildren noOfOk noOfKo
A 2 1 1
B 1 1 0

Given the allChildren DataFrame, I can create the Series counting the entries by groupName:

childrenByGroupName= allChildren.groupby(['groupName'])['name'].count();

And also the Series to filter them by 'ok' state:

okChildrenByGroupName= allChildren.where(['state']=='ok').groupby(['groupName'])['name'].count();

But I cannot build the merged DataFrame as per the above expectation. Any help?

Upvotes: 2

Views: 48

Answers (2)

Pygirl
Pygirl

Reputation: 13349

you can try like this:

df1 = df.groupby(['groupName']).agg({'child': 'count', 'state': lambda x: x.value_counts().to_dict()}).add_prefix('noOf').reset_index()
df2 = pd.concat([df1.drop('noOfstate', axis=1), pd.DataFrame(df1['noOfstate'].tolist()).add_prefix('noOf')], axis=1).fillna(0)

df2:

    groupName   noOfchild   noOfok  noOfko
0   A           2           1       1.0
1   B           1           1       0.0

Upvotes: 2

Quang Hoang
Quang Hoang

Reputation: 150745

Try:

pd.crosstab(df['groupName'], df['state'], margins='sum')

Output:

state      ko  ok  All
groupName             
A           1   1    2
B           0   1    1
All         1   2    3

and to (almost) match the expected output:

(pd.crosstab(df['groupName'], df['state'], margins='sum', margins_name='Children')
   .drop('Children')
   .add_prefix('noOf')
   .reset_index()
)

Upvotes: 3

Related Questions