41

I have data like this in a csv file

Symbol  Action  Year
  AAPL     Buy  2001
  AAPL     Buy  2001
   BAC    Sell  2002
   BAC    Sell  2002

I am able to read it and groupby like this

df.groupby(['Symbol','Year']).count()

I get 

             Action
Symbol Year        
AAPL   2001       2
BAC    2002       2

I desire this (order does not matter)

             Action
Symbol Year        
AAPL   2001       2
AAPL   2002       0
BAC    2001       0
BAC    2002       2

I want to know if its possible to count for zero occurances

Arturo Sbr
  • 4,419
  • 2
  • 23
  • 51
NinjaGaiden
  • 2,904
  • 5
  • 24
  • 46

5 Answers5

40

You can use this:

df = df.groupby(['Symbol','Year']).count().unstack(fill_value=0).stack()
print (df)

Output:

             Action
Symbol Year        
AAPL   2001       2
       2002       0
BAC    2001       0
       2002       2
Joe
  • 11,147
  • 5
  • 36
  • 50
  • 1
    This is a nice solution! Elegant and intuitive and better than using `pivot_table`, unless the latter has any advantages or specific use-cases. Do you know of any? – avg Dec 28 '18 at 06:45
  • 4
    Does this work for only one group by object? it doesn't seem to work and it is fiving me AttributeError: 'Series' object has no attribute 'stack' – haneulkim Apr 16 '20 at 05:01
25

You can use pivot_table with unstack:

print df.pivot_table(index='Symbol', 
                     columns='Year', 
                     values='Action',
                     fill_value=0, 
                     aggfunc='count').unstack()

Year  Symbol
2001  AAPL      2
      BAC       0
2002  AAPL      0
      BAC       2
dtype: int64

If you need output as DataFrame use to_frame:

print df.pivot_table(index='Symbol', 
                     columns='Year', 
                     values='Action',
                     fill_value=0, 
                     aggfunc='count').unstack()
                                     .to_frame()
                                     .rename(columns={0:'Action'})

             Action
Year Symbol        
2001 AAPL         2
     BAC          0
2002 AAPL         0
     BAC          2
jezrael
  • 729,927
  • 78
  • 1,141
  • 1,090
  • This makes a beautiful pivot table but using fill_value=0 still doesn't display the rows with a count of 0 for me. I thought fill_value was just for rows with missing data or NaNs? – ale19 May 03 '16 at 15:53
  • Yes parameter fill_value replace NaN to 0. – jezrael May 03 '16 at 16:30
3

Datatype category

Maybe this feature didn't exist back when this thread was opened, however the datatype "category" can help here:

# create a dataframe with one combination of a,b missing
df = pd.DataFrame({"a":[0,1,1], "b": [0,1,0]})
df = df.astype({"a":"category", "b":"category"})
print(df)

Dataframe looks like this:

   a  b
0  0  0
1  1  1
2  1  0

And now, grouping by a and b

print(df.groupby(["a","b"]).size())

yields:

a  b
0  0    1
   1    0
1  0    1
   1    1

Note the 0 in the rightmost column. This behavior is also documented in the pandas userguide (search on page for "groupby").

jonas
  • 143
  • 1
  • 7
0

If you want to do this without using pivot_table, you can try the below approach:

midx = pd.MultiIndex.from_product([ df['Symbol'].unique(), df['Year'].unique()], names=['Symbol', 'Year'])
df_grouped_by = df_grouped_by.reindex(midx, fill_value=0)

What we are essentially doing above is creating a multi-index of all the possible values multiplying the two columns and then using that multi-index to fill zeroes into our group-by dataframe.

Punit S
  • 2,661
  • 1
  • 19
  • 25
  • this sets all counts to zero for me, instead of the ones that don't appear in the data – KLaz Mar 27 '18 at 16:26
0

Step 1: Create a dataframe that stores the count of each non-zero class in the column counts

count_df = df.groupby(['Symbol','Year']).size().reset_index(name='counts')

Step 2: Now use pivot_table to get the desired dataframe with counts for both existing and non-existing classes.

df_final = pd.pivot_table(count_df,
                       index=['Symbol','Year'],
                       values='counts',                            
                       fill_value = 0,
                       dropna=False,
                       aggfunc=np.sum)

Now the values of the counts can be extracted as a list with the command

list(df_final['counts'])
Anjul Tyagi
  • 194
  • 1
  • 10