1
# test.csv
co11,col2
a,"{'Country':'USA', 'Gender':'Male'}"
b,"{'Country':'China', 'Gender':'Female'}"

df = pd.read_csv('test.csv')
  • I have a column in a csv file, with each cell containing a python dictionary like data structure.
  • How should I convert this cell in the csv into two columns named Country and Gender using Python?
Trenton McKinney
  • 56,955
  • 33
  • 144
  • 158
MSStudent
  • 45
  • 3

2 Answers2

0

test.csv

  • I have a column in a csv file
co11,col2
a,"{'Country':'USA', 'Gender':'Male'}"
b,"{'Country':'China', 'Gender':'Female'}"

Code

  • The dictionaries in the column will be read as strings unless they are converted, using ast.literal_eval.
  • Use pd.json_normalize to convert the dicts, with keys as headers and values for rows.
import pandas as pd
from ast import literal_eval

# read the csv and convert string to dict
df = pd.read_csv('test.csv', converters={'col2': literal_eval})

# display(df)
  co11                                      col2
0    a      {'Country': 'USA', 'Gender': 'Male'}
1    b  {'Country': 'China', 'Gender': 'Female'}

# unpack the dictionaries in col2 and join then as separate columns to df
df = df.join(pd.json_normalize(df.col2))

# drop col2
df.drop(columns=['col2'], inplace=True)

# df
  co11 Country  Gender
0    a     USA    Male
1    b   China  Female
Trenton McKinney
  • 56,955
  • 33
  • 144
  • 158
0

Reading CSV File:
ast.literal_eval is needed or pd.read_csv will read dictionary as string

import ast

df = pd.read_csv('/data_with_dict.csv', converters={'dict_col': ast.literal_eval})

Processing the dataframe that contains a dictionary:

# Example dataframe
df = pd.DataFrame({'unk_col' : ['foo','bar'], 
                   'dict_col': [{'Country':'USA',   'Gender':'Male'}, 
                                {'Country':'China', 'Gender':'Female'}]})

# Convert dictionary to columns
df = pd.concat([df.drop(columns=['dict_col']), df['dict_col'].apply(pd.Series)], axis=1)

# Write to file
df.to_csv(''/data_no_dict.csv'', index=False)

print(df)

Output:

  unk_col Country  Gender
0     foo     USA    Male
1     bar   China  Female
Jakub
  • 489
  • 3
  • 13