python提取df1的列标题,将单元格值转换为列标题，如果在python中匹配则将其填充为1...

I have a dataframe:

df

ID 0 1 2 3 4 ....

1 10 20 5 1 2 ....

2 3 4 NaN 10 1 ....

And I need to transpose the cell values of the column 0,1,2,3,4... to the column headers, and fill it for the Id's with 1 if the cell value is present for the respective ID.

Desired Output:

ID 1 2 3 4 5 ... 10 20 ..

1 1 1 0 0 1 ... 1 1 ..

2 1 0 1 1 0 ... 1 0 ..

Note that some entries can be NaN.

How can I get the desired output?

解决方案

Use DataFrame.set_index with DataFrame.stack for remove missing values, then create indicators by get_dummies and return 1/0 by max by first level, last convert columns to integers:

df1 = (pd.get_dummies(df.set_index('ID').stack())

.max(level=0)

.rename(columns=int)

.reset_index())

print (df1)

ID 1 2 3 4 5 10 20

0 1 1 1 0 0 1 1 1

1 2 1 0 1 1 0 1 0

EDIT:

print (df)

ID 0 1 2 3 4 5

0 1 10 20 5.0 1 2 5

1 2 3 4 NaN 10 1 2

If use max then always in output are 0/1 values (check 5 column):

df1 = (pd.get_dummies(df.set_index('ID').stack())

.max(level=0)

.rename(columns=int)

.reset_index())

print (df1)

ID 1 2 3 4 5 10 20

0 1 1 1 0 0 1 1 1

1 2 1 1 1 1 0 1 0

But if use sum it count values (check 5 column):

df2 = (pd.get_dummies(df.set_index('ID').stack())

.sum(level=0)

.rename(columns=int)

.reset_index())

print (df2)

ID 1 2 3 4 5 10 20

0 1 1 1 0 0 2 1 1

1 2 1 1 1 1 0 1 0