CODEWITHSUNDEEP
pythonpandasdataframe
Sadan A.
Sadan A.

Reputation: 1107

Pandas: Display nested dictionary values in separate column

I have a list of dictionaries something like this

[
  {
    "detail": {
    "name": "boo",
    "id": 1
    },
    "other": {
      "gender": "m",
      "no": "234"
    }
  },
  {
    "detail": {
      "name": "hoo",
      "id": 2
    },
    "other": {
      "gender": "f",
      "no": "456"
    }
  }
]

And i want to print this data in an excel file in the following format

  detail          other
name    id    gender    no
boo    1      m        234   
hoo    2      f        456

In short i want to display the nested values in columns under the parent key column. How can i achieve this using Pandas?

Or is their any other library through which I can achieve this because pandas is quiet heavy one.

Upvotes: 2

Views: 992

Answers (1)

cs95
cs95

Reputation: 402263

Use pd.io.json.json_normalize - 

df = pd.io.json.json_normalize(data)

This results in column names looking like this - 

df.columns
Index(['detail.id', 'detail.name', 'other.gender', 'other.no'], dtype='object')

We'll need to convert this to a MultiIndex, using df.columns.str.split - 

i = list(map(tuple, df.columns.str.split('.')))

Call pd.MultiIndex.from_tuples and assign the result back - 

df.columns = pd.MultiIndex.from_tuples(i)
df

  detail       other     
      id name gender   no
0      1  boo      m  234
1      2  hoo      f  456

If your data is more complicated, you might want to make an additional sort_index call on the columns - 

df = df.sort_index(axis=1)

Upvotes: 2

Related Questions