2

I have a single column pandas dataframe that looks like where each dictionary is currently a string. I want to convert each item in the pandas to a dictionary and then split it out into separate columns.

Current Look

0         {'Owl': '8109284', 'county': '27'}
1         {'Kid': '298049', 'county': '28'}
2         {'Tree': '190849', 'county': '29'}
3         {'Garden': '9801294', 'county': '30'}
4         {'House': '108094', 'county': '31'}

End Look

           Item     Count      County   County Number
0         'Owl'    '8109284' 'county'     '27'
1         'Kid'    '298049'  'county'     '28'
2         'Tree'   '190849'  'county'     '29'
3         'Garden' '9801294  'county'     '30'
4         'House'  '108094'  'county'     '31'


Can anyone help resolve this. I've tried a bunch of different things.
  • related [Convert a String representation of a Dictionary to a dictionary?](https://stackoverflow.com/questions/988228/convert-a-string-representation-of-a-dictionary-to-a-dictionary) – anky Feb 10 '21 at 15:23

2 Answers2

1

You can use literal_eval() to get the dict object out of string. After that get the items using items() method on the dictionary. Lastly, construct the dataframe.

import ast
import pandas as pd

d = [[0,         "{'Owl': '8109284', 'county': '27'}"],
[1,         "{'Kid': '298049', 'county': '28'}"],
[2,         "{'Tree': '190849', 'county': '29'}"],
[3,         "{'Garden': '9801294', 'county': '30'}"],
[4,         "{'House': '108094', 'county': '31'}"]]

df = pd.DataFrame(d, columns=['a', 'b'])


pd.DataFrame(df['b'].apply(lambda x: [j for i in ast.literal_eval(x).items() for j in i]).to_list(), 
             columns=['Item', 'Count', 'County', 'County Number'])

        Item    Count   County  County Number
0       Owl     8109284 county  27
1       Kid     298049  county  28
2       Tree    190849  county  29
3       Garden  9801294 county  30
4       House   108094  county  31
Epsi95
  • 8,832
  • 1
  • 16
  • 34
0

If the format of your dicts is the same (2 keys, 2 values), you can use apply function. 'data' is your dict column:

df['Item']=df.data.apply(lambda x: list(eval(x).keys())[0])
df['Count']=df.data.apply(lambda x: list(eval(x).values())[0])
df['County']=df.data.apply(lambda x: list(eval(x).keys())[1])
df['County Number']=df.data.apply(lambda x: list(eval(x).values())[0])

del df['data']

Output

    Item    Count  County County Number
0  Owl     8109284  county  8109284
1  Kid     298049   county  298049
2  Tree    190849   county  190849
3  Garden  9801294  county  9801294
4  House   108094   county  108094
IoaTzimas
  • 10,538
  • 2
  • 13
  • 30