Ungroup/Unpivot 1 column in pandas

Question

My data is like this

g1    g2   g3    value1    value2
A     X    True    1         2
A     X    False   3         4
B     Y    True    5         6

It was grouped by (g1, g2, g3) and then reset_index. What I am trying to do is to ungroup/unpivot the column g3 so that the output looks something like this?

g1    g2   value1_True    value2_True   value1_False    value2_False
A     X       1                2            3               4
B     Y       5                6            0               0

I have tried to search online but could not find any answer for my particular case. Appreciate any help.

You probably want something starting with `df.set_index(["g1", "g2", "g3"]).unstack("g3", fill_value=0)` — user3483203, Jul 29 '22 at 16:32

score 2 · Accepted Answer · answered Jul 29 '22 at 17:18

# Make the True/False into strings, this helps later.
df.g3 = df.g3.astype(str)

# Pivot your dataframe.
df = df.pivot(index=['g1', 'g2'], columns='g3')

# flatten the multiindex columns and join like you wanted.
df.columns = df.columns.to_flat_index().str.join('_')
print(df.reset_index().fillna(0))

Output:

  g1 g2  value1_False  value1_True  value2_False  value2_True
0  A  X           3.0          1.0           4.0          2.0
1  B  Y           0.0          5.0           0.0          6.0

Ungroup/Unpivot 1 column in pandas

1 Answers1