Pandas：使用另一个数据帧中的值分配值

Suppose I have df1:

假设我有df1:

        col1      col2      
Day0     'A'        NaN      
Day1     'B'        'C'       
Day2     'C'        'A'

and df2:

       'A'    'B'  'C'
Day0    1      4     3
Day1    2      7     6
Day2    5      1    10

How can I replace values in df1 using values from df2 to get output like this:

如何使用df2中的值替换df1中的值以获得如下输出:

        col1      col2      
Day0      1        Nan       
Day1      7         6       
Day2     10         5

What I have in my mind is that I cbind these two dataframes and try to replace the values on each column based on the specified column but did not seem to have a short way to do.

我在脑海中的想法是,我将这两个数据帧组合在一起,并尝试根据指定的列替换每列的值,但似乎没有简短的方法。

3 个解决方案

#1

Use replace by nested dictionaries, but it working only with columns, so double transpose is necessary:

使用嵌套字典替换,但它只适用于列,因此需要双转置:

d = df2.to_dict(orient='index')
print (d)
{'Day2': {"'C'": 10, "'A'": 5, "'B'": 1}, 
'Day1': {"'C'": 6, "'A'": 2, "'B'": 7}, 
'Day0': {"'C'": 3, "'A'": 1, "'B'": 4}}

df = df1.T.replace(d).T
print (df)
      col1  col2
Day0   1.0   NaN
Day1   7.0   6.0
Day2  10.0   5.0

#2

Along the lines of @juanpa.arrivillaga

沿着@ juanpa.arrivillaga的路线

lookup version 1

查找版本1

df1.stack().pipe(
    lambda x: pd.Series(
        df2.lookup(x.index.get_level_values(0), x.values),
        x.index
    )).unstack()

      col1  col2
Day0   1.0   NaN
Day1   7.0   6.0
Day2  10.0   5.0

lookup version 2

查找版本2

df1.apply(
    lambda y: (
        lambda x: pd.Series(
            df2.lookup(x.index, x.values), x.index
        ))(y.dropna()))

      col1  col2
Day0     1   NaN
Day1     7   6.0
Day2    10   5.0

Comprehension

pd.DataFrame({
    c: {
        r: df2.stack().get((r, v), None)
        for r, v in df1[c].items()
    } for c in df1
})

      col1  col2
Day0     1   NaN
Day1     7   6.0
Day2    10   5.0

#3

So I tried this and it kinda work and it is very fast, not sure what you guys think.

所以我尝试了这个,它有点工作,它很快,不知道你们的想法。

result = df1.copy()
result[result.notnull()] = 0
for name in df2.columns:
    result += (df1 == name).astype(int).multiply(df2[name], axis='index')

#1