Pandas apply value from a JSON into a dataframe

October 20, 2022

I have the following JSON file:

{"xx":1,"bb":2,"cc":3}

I want to add a column to a data frame by using the value from the JSON

My data frame

df = pd.DataFrame([{"region": "xx"}, {"region": "xx"}, {"region": "cc"}])

So, using the column region, I want to add a column with the value of the column region on the data frame, in this case, the data frame will be something like this

[{"region": "xx", "value": 1}, {"region": "xx", "value": 1}, {"region": "cc", "value": 3}]

>Solution :

IIUC, you can call map

df['value'] = df['region'].map(d)

print(df)

  region  value
0     xx      1
1     xx      1
2     cc      3

print(df.to_dict('records'))

[{'region': 'xx', 'value': 1}, {'region': 'xx', 'value': 1}, {'region': 'cc', 'value': 3}]