Pandas: Assigning values using values from another dataframe

Question

Suppose I have 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:

        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.

jezrael · Accepted Answer · 2017-10-24 06:19:30Z

2

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

answered Oct 24, 2017 at 6:19

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

piRSquared · Accepted Answer · 2017-10-24 07:31:32Z

1

Along the lines of @juanpa.arrivillaga

lookup version 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

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

edited Oct 24, 2017 at 7:31

answered Oct 24, 2017 at 6:36

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Comments

user3362840 · Accepted Answer · 2017-10-24 06:50:05Z

0

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')

answered Oct 24, 2017 at 6:50

user3362840

1511 gold badge2 silver badges11 bronze badges

1 Comment

jezrael Over a year ago

What is size of your real data? The best is use timings for test solutions.

Collectives™ on Stack Overflow

Pandas: Assigning values using values from another dataframe

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related