CODEWITHSUNDEEP
pythonpandasdataframepivot-tablepython-3.6
Mish
Mish

Reputation: 51

Python 3.6: Creating a pivot table summarizing counts of values for multiple columns in dataframe

I have the following data frame:

df = pd.DataFrame({'X': ['Agree', 'Disagree', 'Agree', 'Neutral', 'Agree','Neutral'],
               'Y': ['Disagree', 'Neutral', 'Agree', 'Disagree', 'Agree', 'Neutral'], 
               'Z': ['Agree', 'Neutral', 'Neutral', 'Disagree', 'Neutral','Neutral']})

I want to create a table summarizing a count of how many 'Agree', 'Neutral' and 'Disagree' there are for each category (column) X, Y and Z.

The output should look like this:

df_answer = pd.DataFrame({'Response': ['Agree', 'Neutral', 'Disagree'],
               'X': [3,2,1],
               'Y': [2,2,2], 
               'Z': [1,4,1]})

I tried to find an answer to this but cant seem to find one that addresses this in particular.

I would prefer for there to be a separate index but it's also okay if the 'Response' is the index if it makes it easier.

Upvotes: 1

Views: 148

Answers (2)

ansev
ansev

Reputation: 30930

We can use DataFrame.apply + pd.value_counts:

new_df=df.apply(pd.value_counts)
print(new_df)

          X  Y  Z
Agree     3  2  1
Disagree  1  2  1
Neutral   2  2  4

We can also do:

df2=df.melt()
new_df=pd.crosstab(df2['value'],df2['variable'])
print(new_df)
variable  X  Y  Z
value            
Agree     3  2  1
Disagree  1  2  1
Neutral   2  2  4

Upvotes: 1

Derek Eden
Derek Eden

Reputation: 4638

I'm not sure how to do this with a pivot_table, but if it helps, this can easily be achieved without:

out = pd.DataFrame()
for col in df.columns:
    out = out.append(df[col].value_counts())

out = out.transpose()

            X    Y    Z
Agree     3.0  2.0  1.0
Disagree  1.0  2.0  1.0
Neutral   2.0  2.0  4.0

you can also name a 'Response' column based on the index if you want

Upvotes: 1

Related Questions