CODEWITHSUNDEEP
pythonpandaspivot-table
Anbu
Anbu

Reputation: 35

How to use pivot table for my excel data using Pandas

Please find my source data below.

Device   AppVersion Rating
Rolex       5.8.0   3
i5i_2018    5.7.0   2
Galaxy A71  5.6.0   3
vivo1807    5.8.0   1
Redmi Note5 5.7.0   2
realme3     5.7.0   3
Redmi7      5.8.0   1
vivo1816    5.7.1   3
Redmi 8     5.6.0   2
o7prolte    5.8.0   3

I want the total count of ratings for each version as output like below.

Count of Rating    Column Labels            
RowLabels          5.6.0    5.7.0   5.7.1   5.8.0
1                                           2
2                  1        2       
3                  1        1       1       2

I got this using a Pivot table in excel but was not able to do the same using Pandas. Could anyone provide your insights?

Upvotes: 0

Views: 38

Answers (2)

sammywemmy
sammywemmy

Reputation: 28644

You can achieve the pivot table in Pandas using the pivot table method : 

 df.pivot_table(index='Rating', 
                columns='AppVersion',
                aggfunc='count',
                fill_value=0)


                                   Device
AppVersion  5.6.0   5.7.0   5.7.1   5.8.0
Rating              
1            0       0       0       2
2            1       2       0       0
3            1       1       1       2

Upvotes: 1

Michael C
Michael C

Reputation: 81

Try .groupby, and then .unstack the result
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.groupby.html
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.unstack.html

df = ...your dataframe...
counts = df.groupby(['Rating','AppVersion']).count()
result = counts.unstack('AppVersion')

This will put blanks in as NaNs pd.np.nan. If you really want to see blank space, fill with an empty string:

result_with_blanks = result.fillna('')

This will change the data type to object though, so I don't recommend this.

Upvotes: 1

Related Questions