How to transpose dataframe and perform count?

jii0302 0
Viewed 20

I'm trying to transpose my dataframe and perform a count by the values in var1 and var2, state, and year variables.

This is my dataframe:

year    var1    var2     state
2019    1       1        AL
2019    4       3        AL
2020    1       2        AL
2019    2       3        CA
2020    1       2        CA
2020    3       2        CA
2020    4       1        CA

This is what I'm trying to do:

variable    value    state    2019    2020
var1        1        AL       1       1
var1        2        AL       0       0
var1        3        AL       0       0
var1        4        AL       1       0
var1        1        CA       0       1 
var1        2        CA       1       0
var1        3        CA       0       1
var1        4        CA       0       1
var2        1        AL       1       0
var2        2        AL       0       1
var2        3        AL       1       0
var2        1        CA       0       1
var2        2        CA       0       2
var2        3        CA       1       0

How can I go about doing this?

pandas python
1 Answers

One possibility what melt and pivot_table:

out = (df
   .melt(['year', 'state'])
   .assign(x=1)
   .pivot_table(index=['variable', 'value', 'state'],
                columns='year', values='x',
                aggfunc='count', fill_value=0
                )
   .reset_index()
 )

Or melt and crosstab:

df2 = df.melt(['year', 'state'])
out = (pd.crosstab([df2['variable'], df2['value'], df2['state']],
                   df2['year'])
         .reset_index()
       )

Output:

year variable  value state  2019  2020
0        var1      1    AL     1     1
1        var1      1    CA     0     1
2        var1      2    CA     1     0
3        var1      3    CA     0     1
4        var1      4    AL     1     0
5        var1      4    CA     0     1
6        var2      1    AL     1     0
7        var2      1    CA     0     1
8        var2      2    AL     0     1
9        var2      2    CA     0     2
10       var2      3    AL     1     0
11       var2      3    CA     1     0
mozway0
Related