pythongroup-bymultiple-columnstranspose

Python Dataframe: Transpose 4/5 Columns in a Dataframe And Hold Date Column Without Transposing


I have a sample input dataframe:

Name Date Score Target Difference
Jim 2023-10-09 9 12 3
Jim 2023-10-16 13 16 3
Andy 2023-10-09 7 7 0
Andy 2023-10-16 5 20 15

Python code to create table:

import pandas as pd
df = pd.DataFrame({'Name':["Jim","Jim","Andy", "Andy"], 'Date':['2023-10-09', '2023-10-16', '2023-10-09', "2023-10-16"], 'Score':["9","13","7", "5"], 'Target':["12","16","7", "20"], 'Difference':["3","3","0", "15"]})

I want to transpose the above table by Name and have the rows be Date, Score, Target, and Difference. The desired output table is below:

Date Category Jim Andy
2023-10-09 Score 9 7
Target 12 7
Difference 3 0
2023-10-16 Score 13 5
Target 16 20
Difference 3 15

I tried doing this with the code below but it doesn't produce the desired transposed table grouping by Date and Category columns.

df_2 =df.T 

df_2 produces the following output which is transposing "Date" column which I don't want.

enter image description here


Solution

  • You can first melt your "Score", "Target", "Difference" columns into two key-value columns, and then apply pivot:

    df \
        .melt(
            id_vars=["Name", "Date"],
            value_vars=["Score", "Target", "Difference"],
            var_name="Category"
        ) \
        .pivot(
            index=["Date", "Category"],
            values="value",
            columns="Name"
        )
    

    Output:

    Name                  Andy Jim
    Date       Category           
    2023-10-09 Difference    0   3
               Score         7   9
               Target        7  12
    2023-10-16 Difference   15   3
               Score         5  13
               Target       20  16