Convert nested string field to dict in Python
When importing data from a tabular file, you may have nested fields inside a column. To work with them, you first need to convert them to a proper dictionary. This can be done using json.loads.
# Import libraries
import json
import pandas as pd
# Load sample JSON data
df = pd.read_csv('data/dict.csv', sep=';')
df
|
place |
grades |
| 0 |
Ariel |
{"count": 12,"value": 4.3} |
| 1 |
Nerval |
{"count": 8,"value": 3.9} |
| 2 |
Zigzag |
{"count": 24,"value": 2.8} |
| 3 |
Camelia |
{"count": 16,"value": 3.1} |
| 4 |
Big Ben |
{"count": 4,"value": 3.7} |
# Convert nested column to a dict
df['grades'] = df['grades'].apply(json.loads)
df
|
place |
grades |
| 0 |
Ariel |
{'count': 12, 'value': 4.3} |
| 1 |
Nerval |
{'count': 8, 'value': 3.9} |
| 2 |
Zigzag |
{'count': 24, 'value': 2.8} |
| 3 |
Camelia |
{'count': 16, 'value': 3.1} |
| 4 |
Big Ben |
{'count': 4, 'value': 3.7} |
# You can now read the dict inside the column
df['grade_count'] = df['grades'].apply(lambda x: x['count'])
df
|
place |
grades |
grade_count |
| 0 |
Ariel |
{'count': 12, 'value': 4.3} |
12 |
| 1 |
Nerval |
{'count': 8, 'value': 3.9} |
8 |
| 2 |
Zigzag |
{'count': 24, 'value': 2.8} |
24 |
| 3 |
Camelia |
{'count': 16, 'value': 3.1} |
16 |
| 4 |
Big Ben |
{'count': 4, 'value': 3.7} |
4 |