Changing values in a column in a Pandas dataFrame by using another dataFrame

user_14954932 0
Viewed 86

I have two dataFrames:

The first dataframe df contains the data:

df = pd.DataFrame({'Standort': ['Vereinigte Staaten', 'Australien', 'Belgien'],
                'value': [100, 300, 150]})

The second dataframe Lookup_Country is a lookup table to link the column 'Standort' to column 'Land' and replace the value of 'Standort' with the value of 'Country'

Lookup_Country = pd.DataFrame({'Land': ['Vereinigte Staaten', 'Großbritannien (UK)', 'Belgien'],
                'Country': ['United States', 'United Kingdom', 'Belgium']})

How can I replace the value of the column 'Standort' by using the dataframe Lookup_Country so that I get a third dataframe

df3= pd.DataFrame({'Standort': ['United States', 'Australien', 'Belgium'],
                'value': [100, 300, 150]})
pandas dataframe python
3 Answers

You can use pd.Series.map

df['Standort'] = df['Standort'].map(Lookup_Country.set_index('Land')['Country']).fillna(df['Standort'])

        Standort  value
0  United States    100
1     Australien    300
2        Belgium    150
user_91778770

This should do the trick:

for i, cell_df in enumerate(df["Standort"]):
    for j, cell_lc in enumerate(Lookup_Country["Land"]):
        if cell_df == cell_lc:
            df.at[i, "Standort"] = Lookup_Country.at[j, "Country"]
user_160384590 
>>> df.replace({"Standort": Lookup_Country.set_index("Land").squeeze().to_dict()})
        Standort  value
0  United States    100
1     Australien    300
2        Belgium    150

In fact, you don't need Lookup_Country is a dataframe, a simple dict is sufficent:

Lookup_Country = {'Vereinigte Staaten': 'United States',
                  'Großbritannien (UK)': 'United Kingdom',
                  'Belgien': 'Belgium'}
user_152399510
Related