How to transform row values into column based on specific values from multiple columns?

Question

This is an example of bigger data.

I have a dataframe like this:

df = pd.DataFrame({"Year":[2023, 2023, 2023, 2024, 2024, 2024],
                   "Value":[0, 2, 3, 1, 5, 2],
                   "Field":["A", "A", "B", "A", "B", "B"],
                   "ID":["X", "X", "X", "X", "Z", "Y"],
                   "Type":["class1","class2","class1","class1","class1","class2"]})

df
Out[8]: 
   Year  Value Field ID    Type
0  2023      0     A  X  class1
1  2023      2     A  X  class2
2  2023      3     B  X  class1
3  2024      1     A  X  class1
4  2024      5     B  Z  class1
5  2024      2     B  Y  class2

I would like to create new columns based on df["Type"] values and take the values from the column df["Value"] based on the same data in columns Field and ID. So my output should be something like this:

   Year Field ID  class1  class2
0  2023     A  X     0.0     2.0
1  2023     B  X     3.0     NaN
2  2024     A  X     1.0     NaN
3  2024     B  Z     5.0     NaN
4  2024     B  Y     NaN     2.0

Anyone could help me?

`df.pivot(index=['Year', 'Field', 'ID'], columns='Type', values='Value').reset_index()` — mozway, Mar 09 '23 at 17:51

score 1 · Answer 1 · answered Mar 09 '23 at 17:50

Apply df.pivot:

res = df.pivot(columns='Type', values='Value', index=['Year', 'Field', 'ID']).reset_index()

Type  Year Field ID  class1  class2
0     2023     A  X     0.0     2.0
1     2023     B  X     3.0     NaN
2     2024     A  X     1.0     NaN
3     2024     B  Y     NaN     2.0
4     2024     B  Z     5.0     NaN

How to transform row values into column based on specific values from multiple columns?

1 Answers1