Pivoting a table containing form replies in Pandas

user_11590130 0
Viewed 32

I have a a dataframe containing replies from a form (20 questions -> 20 columns in the dataframe) that looks as follows :

Name | Q1         | Q2         | Q3         |  Q4        | ...

A    | Very well  | Quite well | Quite well |Not at all  | ...
B    | Fairly well| Little     | Very Little|Not at all  | ...
C    | Very well  | Quite well | Quite well |Fairly well | ...
 ....

Note that each proficiency level is not necessarily represented in each column.

I would like to pivot this table to get a table that looks as follows :

Proficiency| Q1         | Q2         | Q3         |  Q4        | ...

Very Little|NaN         | NaN        | B          | NaN        | ...      
Little     | NaN        | B          | ...
Well       | NaN        | 
Quite well | NaN        | A,C        | ...
Fairly well| B          | 
Very  well | A,C        | .......
 ....

Basically I would like to get a table that contains the name of the people clustered by question and proficiency level. I tried to use pandas pivot_table function but didn't manage to get what I needed.

I managed to create the table by iterating on the original dataframe and creating a new dataframe. However, I was wondering if there was a simplest way to do it using some pandas functions.

pandas pivot-table dataframe python
1 Answers

Try:

print(
    df.melt("Name").pivot_table(
        index="value", columns="variable", values="Name", aggfunc=", ".join
    )
)

Prints:

variable       Q1    Q2    Q3    Q4
value                              
Fairly well     B   NaN   NaN     C
Little        NaN     B   NaN   NaN
Not at all    NaN   NaN   NaN  A, B
Quite well    NaN  A, C  A, C   NaN
Very Little   NaN   NaN     B   NaN
Very well    A, C   NaN   NaN   NaN

df used:

  Name           Q1          Q2           Q3           Q4
0    A    Very well  Quite well   Quite well   Not at all
1    B  Fairly well      Little  Very Little   Not at all
2    C    Very well  Quite well   Quite well  Fairly well
user_100359850
Related