How to unpivot table from boolean form-CodePudding

I have a table like this where type (A, B, C) is represented as boolean form

ID	A	B	C
One	1	0	0
Two	0	0	1
Three	0	1	0

I want to have the table like

ID	Type
One	A
Two	C
Three	B

CodePudding user response：

You can melt and select the rows with 1 with loc while using pop to remove the intermediate values:

out = df.melt('ID', var_name='Type').loc[lambda d: d.pop('value').eq(1)]

output:

      ID Type
0    One    A
5  Three    B
7    Two    C

CodePudding user response：

You can do:

x,y = np.where(df.iloc[:, 1:])
out = pd.DataFrame({'ID': df.loc[x,'ID'], 'Type': df.columns[y]})

Output:

      ID Type
0    One   ID
1    Two    B
2  Three    A

CodePudding user response：

You can also use the new pd.from_dummies constructor here as well. This was added in pandas version 1.5

Note that this also preserves the original order of your ID column.

df['Type'] = pd.from_dummies(df.loc[:, 'A':'C'])

print(df)
      ID  A  B  C Type
0    One  1  0  0    A
1    Two  0  0  1    C
2  Three  0  1  0    B

print(df[['ID', 'Type']])
      ID Type
0    One    A
1    Two    C
2  Three    B