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=';')
dfplace | 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)
dfplace | 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'])
dfplace | 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 |