CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
maninekkalapudi
maninekkalapudi

Reputation: 1118

Show zero values for a column after performing conditional groupby count in pandas

I've a dataframe as mentioned below:

[1]: https://i.sstatic.net/nDMa5.png

I'm using the following code to get the count of resolved for all the items in column A:

resolved = df[df['B']== 'resolved'].groupby('A', as_index=False)['B'].size()

and similarly for unresolved:

unresolved = df[df['B']== 'unresolved'].groupby('A', as_index=False)['B'].size()

For unresolved, SRVCAM-AM BI-Data doesn't have unresolved value in column B. So, the resulting dataframe will not have it

The result obtained for unresolvedis as below:

work_queue count SRVCAM-AM BI-Reports Admin 1

but I want the result as follows:

work_queue count SRVCAM-AM BI-Reports Admin 1 SRVCAM-AM BI-Data 0

Upvotes: 1

Views: 148

Answers (1)

jezrael
jezrael

Reputation: 862741

You can compare column B and aggregate sum - Trues are processes like 1s:

resolved = (df['B'] == 'resolved').groupby(df['A'], as_index=False).sum().astype(int)

If want all columns by B use crosstab:

resolved = pd.crosstab(df['A'], df['B'])

Upvotes: 4

Related Questions