1

I have 2 csv files. One dataset, df1, with a single column that looks like this

   deviceNames
0     12132182
1     12134086
2     12203676
3     12131211
4     12129534

And another, df2, with many columns but the relevant columns are

`       deviceNames        macAddress
0         12080084  001350050039517e
1         12080085  001350050039448c
2         12080086  00135005003954c9
3         12080087  00135005003943bc
4         12080088  0013500500394ff5
...            ...               ...
107549    C0524751  0013500500EA4DEB
107550         NaN               NaN
107551         NaN               NaN
107552         NaN               NaN
107553    C0591266  00135005010FB39D`

What I want is to bring over the mac address information from df2 to df1 based on the device names in df1

So I want the output to look like this


       deviceNames        macAddress
0         12132182  0013500124039517e
1         12134086  0013501340039448c
2         12203676  001350440031954c9
3         12131211  0013503300w3943bc
4         12129534  00135032500394ff5

My attempts

  • Here are a few attempts I made at trying to solve this issue

1

df2[df2['deviceNames'].isin(df1['deviceNames'])]

2

 s = (df2.loc[df2.deviceNames.isin(df1.deviceNames.values.tolist())]
     .drop_duplicates('df1')
     .set_index('df1')['macAddress'])

df1['newcolumn'] = df1['newcolumn'].map(s)
jondidathing
  • 35
  • 5

1 Answers1

1

What you are looking for is merge but your expected output doesn't match your two input dataframes so maybe I'm wrong:

out = df1.astype({'deviceNames': str}).merge(df2, on='deviceNames', how='left')
Corralien
  • 109,409
  • 8
  • 28
  • 52
  • This prints a blank csv file when I try to do it. Is it not possible to bring over the mac address column in df2 based on the deviceNames in df1? – jondidathing Mar 28 '23 at 13:48
  • Blank? You should have at least your first dataframe. – Corralien Mar 28 '23 at 13:49
  • You are right the first dataframe is there but the rest of the columns are blank is what I meant to say – jondidathing Mar 28 '23 at 15:01
  • 1
    What is the output of `df1['deviceNames'].dtype` and `df2['deviceNames'].dtype`? I think the dtypes are not the same. – Corralien Mar 28 '23 at 16:50
  • 1
    You were right both of the dataframes were of a different type. After I changed them to strings I was able to bring over the data from df2 to df1. Thanks! – jondidathing Mar 28 '23 at 17:25