CODEWITHSUNDEEP
pythonpandasdataframe
GetSome _
GetSome _

Reputation: 59

How to transpose a column and get the count and percentage of same values in pandas?

i have dataframe like below.

  Q1   Q2
   1    2    
   2    3 
   3    4
   4    1
   1    4
   2    3

what i want to do is, i want to transpose each column into rows and get the count of same values and percentage.

my expected output should be

    1    2  33%
Q1  2    2  33%
    3    1  16% 
    4    1  16%

    1    1  16%
Q2  2    1  16% 
    3    2  33%
    4    2. 33%

Upvotes: 1

Views: 220

Answers (1)

jezrael
jezrael

Reputation: 862761

Use DataFrame.melt with SeriesGroupBy.value_counts and then convert Series to one colum DataFrame by Series.to_frame. For percentages divide by Series.div summed values per first a level:

df1 = (df.melt(var_name='a', value_name='b')
         .groupby('a')['b']
         .value_counts(sort=False)
         .to_frame(name='count'))
df1['perc'] = df1['count'].div(df1['count'].sum(level=0), 0).mul(100)
print (df1)
      count       perc
a  b                  
Q1 1      2  33.333333
   2      2  33.333333
   3      1  16.666667
   4      1  16.666667
Q2 1      1  16.666667
   2      1  16.666667
   3      2  33.333333
   4      2  33.333333

If need percentages:

df1['perc'] = df1['perc'].astype(int).astype(str).add('%')
print (df1)
      count perc
a  b            
Q1 1      2  33%
   2      2  33%
   3      1  16%
   4      1  16%
Q2 1      1  16%
   2      1  16%
   3      2  33%
   4      2  33%

Upvotes: 1

Related Questions