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

Question

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.

Answer 1

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