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=???)

look at stackoverflow.com/questions/36864929/… (create a new column filled with 1, then use pivot) — ysearka
– ysearka, Commented May 31, 2016 at 15:39

Community · Accepted Answer · 2017-05-23 11:58:42Z

4

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

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered May 31, 2016 at 15:41

user2285236

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

Comments

3kt · Accepted Answer · 2016-05-31 15:57:27Z

1

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

answered May 31, 2016 at 15:57

3kt

2,5432 gold badges19 silver badges30 bronze badges

Collectives™ on Stack Overflow

Transform dataframe and count values in pandas

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related