0

Hard to describe problem so let me know you what I'm trying to do. The 3rd column of the first dataframe needs its values set to those corresponding values matching on column A of the second.

import pandas as pd
from random import randint

df1 = pd.DataFrame({'A': [1,1,1,2,2,2,3,3,3,3],
                   'B': [randint(1, 9)*10 for x in range(10)],
                   'C': [randint(1, 9)*100 for x in range(10)]})

df2 = pd.DataFrame({'A': [1,2,3],
                   'D': [randint(9, 19)*0.1 for x in range(3)]})

So we get:

>>> df1
   A   B    C
0  1  90  600
1  1  90  100
2  1  20  900
3  2  10  300
4  2  60  200
5  2  40  500
6  3  80  100
7  3  30  100
8  3  30  100
9  3  80  700
>>> df2
   A    D
0  1  1.4
1  2  1.2
2  3  1.0

But what I need is df1's column C to equal the values of D where A is the same in both df1 and df2. Does that make sense? So then the modified df1 would be:

>>> df1
   A   B    C
0  1  90  1.4
1  1  90  1.4
2  1  20  1.4
3  2  10  1.2
4  2  60  1.2
5  2  40  1.2
6  3  80  1.0
7  3  30  1.0
8  3  30  1.0
9  3  80  1.0

How can I do this? My first time using pandas; very impressive library so far though :)

JDS
  • 16,388
  • 47
  • 161
  • 224
  • 1
    Use [merge](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.merge.html#pandas-dataframe-merge) -> `df1 = df1.merge(df2, how='left', on='A')` – Henry Ecker Jul 08 '21 at 01:35
  • 1
    @HenryEcker fantastic exactly! Feel free to put in the answer and I'll accept. Appreciate the quick response. – JDS Jul 08 '21 at 01:39

1 Answers1

1

Let us try

df1['C'] = df1.A.map(df2.set_index('A')['D'])
BENY
  • 317,841
  • 20
  • 164
  • 234