Transform dataframe and count values in pandas

Question

I have a dataframe df that looks like

I need to transform it and count the occurence of each Type for each year. So my expected output is:

    1998 2003 2006 2007 2010 2015
 A   1   0     0     0   1    0
 B   0   1     0     0   0    2 
 C   0   1     2     0   0    0
 D   1   0     0     1   1    0

As i understood, i need to usepivot here, right? Something like df.pivot(index='Type', columns='Data', values=???)

Actually I think what you want is [crosstab](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.crosstab.html), not pivot. — Ajean, May 31 '16 at 15:33
look at http://stackoverflow.com/questions/36864929/creating-one-dataframe-from-another-using-pivot (create a new column filled with 1, then use pivot) — ysearka, May 31 '16 at 15:39

score 4 · Accepted Answer · edited May 23 '17 at 11:58

You can use pivot_table and pass len as the aggregate function:

df.pivot_table(index="Type", columns="Date",  aggfunc=len).fillna(0).astype(int)
Out[152]: 
Date  1998  2003  2006  2007  2010  2015
Type                                    
A        1     0     0     0     1     0
B        0     1     0     0     0     2
C        0     1     2     0     0     0
D        1     0     0     1     1     0

Like Ajean mentioned, you can use crosstab too:

pd.crosstab(df['Type'], df['Date'])
Out[153]: 
Date  1998  2003  2006  2007  2010  2015
Type                                    
A        1     0     0     0     1     0
B        0     1     0     0     0     2
C        0     1     2     0     0     0
D        1     0     0     1     1     0

score 2 · Answer 2 · answered May 31 '16 at 15:57

If you don't want the empty values to show up, you can also use :

df.groupby(['Date', 'Type']).size().reset_index()

Which would give :

   Date Type  0
0  1998    A  1
1  1998    D  1
2  2003    B  1
3  2003    C  1
4  2006    C  2
5  2007    D  1
6  2010    A  1
7  2010    D  1
8  2015    B  2

Transform dataframe and count values in pandas

2 Answers2