Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dictionary column in pandas dataframe

I've got a csv that I'm reading into a pandas dataframe. However one of the columns is in the form of a dictionary. Here is an example:

ColA, ColB, ColC, ColdD
20, 30, {"ab":"1", "we":"2", "as":"3"},"String"

How can I turn this into a dataframe that looks like this:

ColA, ColB, AB, WE, AS, ColdD
20, 30, "1", "2", "3", "String"

edit I fixed up the question, it looks like this but is a string that needs to be parsed, not dict object.

like image 734
user1274037 Avatar asked Mar 29 '15 03:03

user1274037


2 Answers

As per https://stackoverflow.com/a/38231651/454773, you can use .apply(pd.Series) to map the dict containing column onto new columns and then concatenate these new columns back into the original dataframe minus the original dict containing column:

dw=pd.DataFrame( [[20, 30, {"ab":"1", "we":"2", "as":"3"},"String"]],
                columns=['ColA', 'ColB', 'ColC', 'ColdD'])
pd.concat([dw.drop(['ColC'], axis=1), dw['ColC'].apply(pd.Series)], axis=1)

Returns:

ColA    ColB    ColdD   ab  as  we
20      30      String  1   3   2
like image 141
psychemedia Avatar answered Sep 23 '22 17:09

psychemedia


So starting with your one row df

    Col A   Col B   Col C                           Col D
0   20      30      {u'we': 2, u'ab': 1, u'as': 3}  String1

EDIT: based on the comment by the OP, I'm assuming we need to convert the string first

import ast
df["ColC"] =  df["ColC"].map(lambda d : ast.literal_eval(d))

then we convert Col C to a dict, transpose it and then join it to the original df

dfNew = df.join(pd.DataFrame(df["Col C"].to_dict()).T)
dfNew

which gives you this

    Col A   Col B   Col C                           Col D   ab  as  we
0   20      30      {u'we': 2, u'ab': 1, u'as': 3}  String1 1   3   2

Then we just select the columns we want in dfNew

dfNew[["Col A", "Col B", "ab", "we", "as", "Col D"]]

    Col A   Col B   ab  we  as  Col D
0   20      30      1   2   3   String1
like image 40
Bob Haffner Avatar answered Sep 22 '22 17:09

Bob Haffner