how to change the whole dataframe values based on another one in Python

Question

I have two dataframes:

df = pd.DataFrame({'A': [1,2,3,5,4,2], 'B':[1,2,4,1,2,3], 'C':[2,3,1,4,2,3]})
df1 = pd.DataFrame({'num':[1,2,3,4,5],'col':['red','red','blue','orange','orange']})

They look like this:

df:
        A   B   C
    0   1   1   2
    1   2   2   3
    2   3   4   1
    3   5   1   4
    4   4   2   2
    5   2   3   3

df1:

       col    num
   0    red     1
   1    red     2
   2    blue    3
   3    orange  4
   4    orange  5

what i want is the dataframe df with values of df1:

df_new:
        A       B     C
    0   red     red     red
    1   red     red     blue
    2   blue    orange  red
    3   orange  red     orange
    4   orange  red     red
    5   red     blue    blue

Bearing in mind that I have lots of rows in df, and have thousands of different values as well.

Many Thanks!

EdChum · Accepted Answer · 2016-07-01 10:36:10Z

1

if you set the index to the num col on df1 then you can call apply with a lambda and use map to perform the lookup on each column:

In [11]:
df2 = df1.set_index('num')
df.apply(lambda x: x.map(df2['col']))

Out[11]:
        A       B       C
0     red     red     red
1     red     red    blue
2    blue  orange     red
3  orange     red  orange
4  orange     red     red
5     red    blue    blue

answered Jul 1, 2016 at 10:36

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

2 Comments

Sapling Over a year ago

Thanks so much for quick response, i have been thinking of this for a while! Bless you!

Sapling Over a year ago

yes, had to wait for a couple of minutes to be able to accept the answer. It's fast enough for me.

Collectives™ on Stack Overflow

how to change the whole dataframe values based on another one in Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related