Pandas: Replace column values in DataFrame based on the json object

Question

I have a json object called 'countries' like below with all the countries ISO code list:

countries = [{"name":"Afghanistan","alpha-2":"AF","country-code":"004"},{"name":"Åland Islands","alpha-2":"AX","country-code":"248"},{"name":"Albania","alpha-2":"AL","country-code":"008"},{"name":"Algeria","alpha-2":"DZ","country-code":"012"}]

I have a pandas dataframe with 'Country' column:

Country
--------
Albania
Algeria
Algeria

I want to replace the Country column 'name' with the 'alpha-2' value from the json object. The result should be like :

Country
---------
AL
DZ
DZ

I am trying to do something like this which does not give any error nor changes the values.

df['Country'] = df['Country'].replace(lambda y: (x['alpha-2'] for x in countries) if y in (x['name'] for x in countries) else y)

score 3 · Accepted Answer · answered Feb 01 '19 at 17:09

A row-wise lambda is not recommended with Pandas, for the same reason pd.Series.apply is not recommended. A much better idea is to construct a single mapping dictionary and then use vectorised pd.Series.map:

# setup dataframe
df = pd.DataFrame({'Country': ['Albania', 'Algeria', 'Algeria']})

# construct mapping dictionary and apply mapping
mapper = {dct['name']: dct['alpha-2'] for dct in countries}
df['Country'] = df['Country'].map(mapper).fillna(df['Country'])

print(df)
#   Country
# 0      AL
# 1      DZ
# 2      DZ

This is good in the sense OP wants convert the json to a single map +1 — Karn Kumar, Feb 01 '19 at 17:20

score 1 · Answer 2 · answered Feb 01 '19 at 17:16

You can do this way to make a new {country:country_code} dictionary pattern using country_to_country_code= {v['name']:v['alpha-2'] for v in countries} and then just map() your Country column with this country_to_country_code dictionary.

import pandas as pd
df = pd.DataFrame({"Country":["Albania", "Algeria", "Algeria"]})
countries = [{"name":"Afghanistan","alpha-2":"AF","country-code":"004"},{"name":"Åland Islands","alpha-2":"AX","country-code":"248"},{"name":"Albania","alpha-2":"AL","country-code":"008"},{"name":"Algeria","alpha-2":"DZ","country-code":"012"}]

country_to_country_code= {v['name']:v['alpha-2'] for v in countries}
df.loc[:, 'Country'] = df['Country'].map(country_to_country_code)
print(df)

OUTPUT

  Country
0      AL
1      DZ
2      DZ

score 0 · Answer 3 · answered Feb 01 '19 at 17:03

0

You are accessing a column Country in df['Country'] so if you have other fields as well like one in question alpha-2 then why not simply df['Country']=df['alpha-2'] which will be faster than lambda anyways

answered Feb 01 '19 at 17:03

mad_

8,121
2
25
40

Karn Kumar · Answer 4 · 2019-02-01T17:36:02.727

0

If You have already converted the Json into pandas dataFrame and you have the DataFrame as you have shown with the column Country then you either simply can use map() function or use replace() method both will work here.

df['Country'] = df['Country'].map({'Albania': 'AL', 'Algeria': 'DZ'})

OR:

>>> df.Country.replace(to_replace=dict(Albania='AL', Algeria='DZ'))
0    AL
1    DZ
2    DZ
Name: Country, dtype: object

OR alternatively you can create a dictionary to make multiple replacements at a time as follows.

new_vals = {
  'Albania': 'AL',
  'Algeria': 'DZ',
  }


df['Country'].replace(new_vals)
# df['Country'].replace(new_vals, inplace=True)

edited Feb 01 '19 at 17:36

answered Feb 01 '19 at 17:10

Karn Kumar

8,518
3
27
53

I think the problem is OP is starting with the json object, so they are looking for a way to convert the json to a single map (if using `pd.Series.map`). – jpp Feb 01 '19 at 17:17
@jpp, that's may be true :-) , otherwise `map()` would be appropriate. – Karn Kumar Feb 01 '19 at 17:18

score 0 · Answer 5 · edited Feb 01 '19 at 19:29

You can convert the list to a Pandas DataFrame (e.g. df2) and then do a replace.

import pandas as pd

countries = [{"name":"Afghanistan","alpha-2":"AF","country-code":"004"},{"name":"Åland Islands","alpha-2":"AX","country-code":"248"},{"name":"Albania","alpha-2":"AL","country-code":"008"},{"name":"Algeria","alpha-2":"DZ","country-code":"012"}]
df2 = pd.DataFrame(countries)

co = [('Country', ['Afghanistan', 'Algeria', 'Albania'])]  # your original dataframe with country
df1 = pd.DataFrame.from_items(co)



df1['Country'] = df1['Country'].replace(df2.set_index('name')['alpha-2'])

df1 should look like:
[enter image description here][1]

Pandas: Replace column values in DataFrame based on the json object

5 Answers5