How to fill dataframe value based on row:column position key-value?-CodePudding

Suppose I have a dataframe

id   0   1   2

001  0   0   0
003  0   0   0
007  0   0   0

and a dataframe of {id:column} , for example

id   col
001   2
007   0

How can I put value in dataframe to become

id   0   1   2

001  0   0   1
003  0   0   0
007  1   0   0

I think using apply / applymap with list comprehension is the way but still cannot figure out.

CodePudding user response：

IIUC you can crosstab and update:

df = pd.DataFrame({'id': {0: 1, 1: 3, 2: 7}, 0: {0: 0, 1: 0, 2: 0}, 1: {0: 0, 1: 0, 2: 0}, 2: {0: 0, 1: 0, 2: 0}})
df2 = pd.DataFrame({'id': {0: 1, 1: 7}, 'col': {0: 2, 1: 0}})

df = df.set_index("id")

df.update(pd.crosstab(df2["id"], df2["col"]))

print (df.reset_index())

   id    0  1    2
0   1  0.0  0  1.0
1   3  0.0  0  0.0
2   7  1.0  0  0.0