How to prevent json_normalize from repeating column headings in Pandas?

Question

My json is structured as follows:

{
   "data": {
              "item1": {
                       "field1": "foo",
                       "field2": "bar",
                       "field3": "baz"
                       },
              "item2": {
                       "field1": "foo1",
                       "field2": "bar1",
                       "field3": "baz1"
                       },
           }
}

When I normalize this data structure using pd.io.json.json_normalize, I get a data frame with 1 row and the column headings repeated for each data item. How do I get the repeated columns to appear as rows instead of columns?

I currently get this

field1 | field2 | field3 | field1 |field2 | field3

foo     | bar    | baz    | foo1   | bar1  | baz1

What I want is:

field1 | field2 | field3 |


foo     | bar    | baz

foo1    | bar1   | baz1

score 2 · Accepted Answer · answered Apr 08 '17 at 11:01

2

Is that what you want?

In [31]: d
Out[31]:
{'data': {'item1': {'field1': 'foo', 'field2': 'bar', 'field3': 'baz'},
  'item2': {'field1': 'foo1', 'field2': 'bar1', 'field3': 'baz1'}}}

In [32]: pd.DataFrame(d['data']).T
Out[32]:
      field1 field2 field3
item1    foo    bar    baz
item2   foo1   bar1   baz1

answered Apr 08 '17 at 11:01

MaxU - stand with Ukraine

205,989
36
386
419

1

I suspect you already upvoted... but I [**edited the post**](http://stackoverflow.com/a/43292413/2336654)... this is weird stuff :-) – piRSquared Apr 08 '17 at 11:34

score 1 · Answer 2 · answered Apr 08 '17 at 11:03

You can try to convert the data to list, and then use json_normalize to normalize the data:

from pandas.io.json import json_normalize

content={"data": {"item1": {"field1": "foo","field2": "bar","field3": "baz"},"item2": {"field1": "foo1","field2": "bar1","field3": "baz1"},}}

content["data"]=content["data"].values()

result = json_normalize(content,"data")

print(result)

Output:

  field1 field2 field3
0   foo1   bar1   baz1
1    foo    bar    baz

How to prevent json_normalize from repeating column headings in Pandas?

2 Answers2