dataframe has a column as dictionary.How to convert it in columns?

Question

Df below has columns which consist a dictionary of the name, gender, and net_salary. The dictionary has many columns and their corresponding values. i need to convert that dictionary in columns form.

 index    columns
 9        {"name": "namo     ", "gender": "MALE", "net_salary":...
 10       {"name": "pappu", "gender": "MALE", "net_salary":...
 11       {"name": "Deepak", "gender": "MALE", "net_sala...
 12       {"name": "Arun", "gender": "MALE", "net_salary...
                                                       {}

i want output in this form.

 index    name    gender       net_salary
 9        namo     MALE          151515
 10       pappu    MALE          151454
 11       Deepak   MALE          42512
 12       Arun     MALE          51654

1

`pd.DataFrame.from_dict` – Zelazny7 Mar 20 '19 at 19:24

Farhood ET · Answer 1 · 2019-03-20T19:31:13.760

1

You need to first slice the column that consists of dicts, and then create a new dataframe of those dicts.

columnsList = list(dframe["columns"])
sub_dframe = pd.DataFrame(columnsList)
result = pd.concat([dframe["index"],sub_dframe], axis=1)

edited Mar 20 '19 at 19:31

answered Mar 20 '19 at 19:25

Farhood ET

1,432
15
32

and how this will work if two dfs have no common values to merger on? – mad_ Mar 20 '19 at 19:28
@mad_ oh my bad. I confused merge and concat. – Farhood ET Mar 20 '19 at 19:32

score 0 · Answer 2 · answered Mar 20 '19 at 19:30

You need:

df = pd.DataFrame({'columns': [{"name": "namo", "gender": "MALE", "net_salary":151515}, 
                           {"name": "pappu", "gender": "MALE", "net_salary":151454}]}, index=[9,10])


print(df['columns'].apply(pd.Series))

Output:

    name gender  net_salary
9   namo   MALE      151515
10  pappu   MALE      151454

score 0 · Answer 3 · answered Mar 20 '19 at 19:30

0

I would do somethings like this (python 3):

df["name"] = [*map(lambda x: x["name"],df["columns"])]
df["gender"] = [*map(lambda x: x["gender"],df["columns"])]
df["salary"] = [*map(lambda x: x["net_salary"],df["columns"])]

You can then delete the original column if it bothers you.

answered Mar 20 '19 at 19:30

BossaNova

1,509
1
13
17

TypeError: string indices must be integers – Aamir Siddiqui Mar 20 '19 at 19:58
Strange, I don't get this error, using a df that replicates your example. Maybe you built the df differently in a way. – BossaNova Mar 21 '19 at 13:40

score 0 · Answer 4 · answered Mar 20 '19 at 19:35

0

A crisp solution could be

pd.concat([df,df['columns'].apply(pd.Series)],axis=1).drop('columns',axis=1)

answered Mar 20 '19 at 19:35

mad_

8,121
2
25
40

dataframe has a column as dictionary.How to convert it in columns?

4 Answers4