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

Asked 2 months ago, Updated 2 months ago, 1 views

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


© 2022 OneMinuteCode. All rights reserved.