I want to add columns referenced from the pandas list data frame

I recently asked pandas how to reference and replace data frames from another data frame.
The above has been resolved, but the reference method in the data frame below does not work.
[Example]
I want to see [df2] and add the CityCode corresponding to 'City' to [df1]

import pandas as pd

df1 = pd.DataFrame({
    'Name': ['John', 'Milke', 'Saya', 'Taro',
    'Residence': ['Osaka', 'Kyoto', 'Nagoya', 'Kyoto']
})

df2 = pd.DataFrame({
    'City': ['Osaka', 'Kyoto', 'Fukuoka', 'Nagoya'],
    'CityId': [1001,1002,1003,1004]
})

[Results you want]

 [df1]
Name, Residence, CityId
John, Osaka, 1001
Mike, Kyoto, 1002
Saya, Nagoya, 1004
Taro, Kyoto, 1002

[Tried]
In addition to the map method, I tried the isin method.

#First add 'CityCode' to df1 and replace 0
df1['CityCode'] = 0

df1['CityCode'] = df2[df2['CityCode'].isin(df1['Residence'])]

Results Error

KeyError: 'CityCode'

During handling of the above exception, another exception occurred:

I'm sorry for the rudimentary question, but I don't understand much about Pandas yet, but please let me know.

python pandas

2022-09-30 14:23

1 Answers

The following illustrates how to use pandas.Series.map+pandas.DataFrame.assign and pandas.DataFrame.merge.

pandas.Series.map+pandas.DataFrame.assign

>>df1=df1.assign(CityId=df1.Residence.map(df2.set_index('City').CityId))
>>df1
    Name Residence CityId
0 John Osaka 1001
1 Milky Kyoto 1002
2 Saya Nagoya 1004
3Taro Kyoto 1002

pandas.DataFrame.merge

>>>df1=pd.merge(df1, df2, left_on='Residence', right_on='City', how='left').drop(columns='City')
>>df1
    Name Residence CityId
0 John Osaka 1001
1 Milky Kyoto 1002
2 Saya Nagoya 1004
3Taro Kyoto 1002

2022-09-30 14:23

If you have any answers or tips

Popular Tags

python x 4628

android x 1593

java x 1493

javascript x 1425

c x 924

c++ x 877

ruby-on-rails x 696

php x 692

python3 x 683

html x 656