Counting column values based on values in other columns for Pandas dataframes

Question

I'm trying to count the number of each category of storm for each unique x and y combination. For example. My dataframe looks like:

x   y  year  Category
1   1  1988     3
2   1  1977     1
2   1  1999     2
3   2  1990     4

I want to create a dataframe that looks like:

x   y   Category 1   Category 2   Category 3  Category 4
1   1        0           0            1           0
2   1        1           1            0           0
3   2        0           0            0           1

I have tried various combinations of .groupby() and .count(), but I am still not getting the desired result. The closet thing I could get is:

df[['x','y','Category']].groupby(['Category']).count()

However, the result counts for all x and y, not the unique pairs:

Cat       x           y     
1       3773         3773
2       1230         1230
3       604          604
4       266          266
5       50           50
NA      27620        27620
TS      16884        16884

Does anyone know how to do a count operation on one column based on the uniqueness of two other columns in a dataframe?

busybear · Accepted Answer · 2019-02-05 03:41:04Z

2

pivot_table sounds like what you want. A bit of a hack is to add a column of 1's to use to count. This allows pivot_table to add 1 for each occurrence of a particular x-y and Category combination. You will set this new column as your value parameter in pivot_table and the aggfunc paraemter to np.sum. You'll probably want to set fill_value to 0 as well:

df['count'] = 1
result = df.pivot_table(
    index=['x', 'y'], columns='Category', values='count',
    fill_value=0, aggfunc=np.sum
)

result:

Category  1  2  3  4
x y                 
1 1       0  0  1  0
2 1       1  1  0  0
3 2       0  0  0  1

If you're interested in keeping x and y as columns and having the other column names as Category X, you can rename the columns and use reset_index:

result.columns = [f'Category {x}' for x in result.columns]
result = a.reset_index()

edited Feb 5, 2019 at 3:41

answered Feb 5, 2019 at 2:59

busybear

10.7k1 gold badge29 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mohit Motwani Over a year ago

That count column and aggfunc is pretty smart.

busybear Over a year ago

Thanks, I was kind of hoping someone would point out a cleaner way to do it though!

Mohit Motwani Over a year ago

It's clean. But just explain why you used count = 1. Because other readers might get confused.

Mohit Motwani · Accepted Answer · 2019-02-05 03:35:38Z

1

You can use groupby first:

df_new = df.groupby(['x', 'y', 'Category']).count()
df_new
                  year  count
x   y   Category        
1   1      3       1    1
2   1      1       1    1
           2       1    1
3   2      4       1    1

Then pivot_table

df_new = df_new.pivot_table(index=['x', 'y'], columns='Category', values='count', fill_value=0)
df_new
Category    1   2   3   4
x   y               
1   1       0   0   1   0
2   1       1   1   0   0
3   2       0   0   0   1

edited Feb 5, 2019 at 3:35

answered Feb 5, 2019 at 3:05

Mohit Motwani

4,8124 gold badges21 silver badges50 bronze badges

Comments

Scott Boston · Accepted Answer · 2019-02-05 03:54:02Z

1

You can use pd.get_dummies after setting index using set_index, then use sum with level parameter to collapse rows:

pd.get_dummies(df.set_index(['x','y'])['Category'].astype(str),
               prefix='Category ', 
               prefix_sep='')\
  .sum(level=[0,1])\
  .reset_index()

Output:

   x  y  Category 1  Category 2  Category 3  Category 4
0  1  1           0           0           1           0
1  2  1           1           1           0           0
2  3  2           0           0           0           1

answered Feb 5, 2019 at 3:54

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Comments

U13-Forward · Accepted Answer · 2019-02-05 03:12:47Z

0

Or use groupby twice, with a lot of additional, i.e get_dummies with apply etc...

Like:

>>> df.join(df.groupby(['x','y'])['Category']
           .apply(lambda x: x.astype(str).str.get_dummies().add_prefix('Category ')))
           .groupby(['x','y']).sum().fillna(0).drop(['year','Category'],1).reset_index()
   x  y  Category 1  Category 2  Category 3  Category 4
0  1  1         0.0         0.0         1.0         0.0
1  2  1         1.0         1.0         0.0         0.0
2  3  2         0.0         0.0         0.0         1.0
>>>

edited Feb 5, 2019 at 3:12

answered Feb 5, 2019 at 3:04

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

1 Comment

U13-Forward Over a year ago

@MohitMotwani Done it.

Collectives™ on Stack Overflow

Counting column values based on values in other columns for Pandas dataframes

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related