Normalizing Complex Json in python

Question

I am trying to normalize json function using pandas but not able to flatten the nested arrays inside json

I have tried this reading one of the example but it only gives me one record using this code

from pandas.io.json import json_normalize
data ={
"id":"001",
    "counties" : [ {"name":"y"},{"name":"X"}], 
"extendedDescriptionFrench" : "Fromage Brick Petit Gaspesien",
"brand" : "PETIT GASPESIEN",
"brandFrench" : "PETIT GASPESIEN",
"productLife" : "90",
"digitalAssetFoodservice" : [ {
"digitalAssetFormatFoodservice" : "JPG",
"digitalAssetGDTIFoodservice" : "754000000016500000000002167445",
"digitalAssetImageVersionDateTimeFoodservice" : "2016-06-28T20:06:06.000-04:00",
"digitalAssetStateFoodservice" : "P"
}, {
"digitalAssetFormatFoodservice" : "JPG",
"digitalAssetGDTIFoodservice" : "754000000016500000000002167597",
"digitalAssetImageTypeFoodservice" : "M",
"digitalAssetImageVersionDateTimeFoodservice" : "2016-06-28T20:06:06.000-04:00"
}, {
"digitalAssetFormatFoodservice" : "JPG",
"digitalAssetGDTIFoodservice" : "754000000016500000000002167687",
"digitalAssetImageTypeFoodservice" : "C",
"digitalAssetImageVersionDateTimeFoodservice" : "2016-06-28T20:06:06.000-04:00",
"digitalAssetStateFoodservice" : "C"
} ]
}

a=json_normalize(data)
    print(a)

Is there anyway to flatten "digitalAssetFoodservice" array into columns.

Here is the output that i am getting

And Also what if i have more than one nested array fields

What do you intend to do, what is not working? What about the output of a=json_normalize(data["digitalAssetFoodservice"]) ? — aveuiller
– aveuiller, Commented Sep 19, 2019 at 15:21
can we normalize and output of this field + the other scalar fields, because i want the whole json to be normalized — Usman Rafiq
– Usman Rafiq, Commented Sep 19, 2019 at 15:36
Can you add an example of more than one nested array fields, because it wouldn't be proper to append them to a table if they are not matching the same index (from what I understand you mean). — Celius Stingher
– Celius Stingher, Commented Sep 19, 2019 at 17:04
However, what is your expected output? Everyting duplicated, once for name: x and one for name: y ? — Celius Stingher
– Celius Stingher, Commented Sep 19, 2019 at 17:48

Celius Stingher · Accepted Answer · 2019-09-19 18:08:57Z

1

I believe that you need to pass the key of the nested array together with the non-nested ones.

a=json_normalize(data,'digitalAssetFoodservice',['id','extendedDescriptionFrench','brand','productLife'])

print(a)
print(a.columns)

Out:

  digitalAssetFormatFoodservice     digitalAssetGDTIFoodservice digitalAssetImageVersionDateTimeFoodservice  ...      extendedDescriptionFrench            brand productLife
0                           JPG  754000000016500000000002167445               2016-06-28T20:06:06.000-04:00  ...  Fromage Brick Petit Gaspesien  PETIT GASPESIEN          90
1                           JPG  754000000016500000000002167597               2016-06-28T20:06:06.000-04:00  ...  Fromage Brick Petit Gaspesien  PETIT GASPESIEN          90
2                           JPG  754000000016500000000002167687               2016-06-28T20:06:06.000-04:00  ...  Fromage Brick Petit Gaspesien  PETIT GASPESIEN          90

[3 rows x 9 columns]
Index(['digitalAssetFormatFoodservice', 'digitalAssetGDTIFoodservice',
       'digitalAssetImageVersionDateTimeFoodservice',
       'digitalAssetStateFoodservice', 'digitalAssetImageTypeFoodservice',
       'id', 'extendedDescriptionFrench', 'brand', 'productLife'],
      dtype='object')

When trying for two cases:

This is what I came up with given I couldn't not find a way to generate in one line your expected output. I also changed the script a bit to make it easier. (Based on: "Question: What is your expected output? Everyting duplicated, once for name: x and one for name: y ? OP: yes , duplicated"

from pandas.io.json import json_normalize
data ={
"id":"001",
"counties" : [ {"name":"y"},{"name":"X"}], 
"eDF" : "Fromage Brick Petit Gaspesien",
"brand" : "PETIT GASPESIEN",
"brandFrench" : "PETIT GASPESIEN",
"productLife" : "90",
"dAF" : [ {
"dAFF" : "JPG",
"dAGDTIF" : "75401652167445",
"dAIVDTF" : "2016-06-28",
"dASF" : "P"
}, {
"dAFF" : "JPG",
"dAGDTIF" : "75401652167597",
"dAITFa" : "M",
"dAIVDTF" : "2016-06-28"
}, {
"dAFF" : "JPG",
"dAGDTIF" : "7540162167687",
"dAITF" : "C",
"dAIVDTF" : "2016-06-28",
"dASF" : "C"
} ]
}

repetitive = ['id','eDF','brand','brandFrench','productLife']
a=json_normalize(data,'counties',repetitive)
b=json_normalize(data,'dAF',repetitive)
c = a.merge(b,how='inner',left_on=repetitive,right_on=repetitive)
print(a)

Output:

 name   id                            eDF            brand      brandFrench productLife
0    y  001  Fromage Brick Petit Gaspesien  PETIT GASPESIEN  PETIT GASPESIEN          90
1    X  001  Fromage Brick Petit Gaspesien  PETIT GASPESIEN  PETIT GASPESIEN          90

Now the other b:

print(b)

Output:

  dAFF         dAGDTIF     dAIVDTF dASF dAITFa dAITF   id                            eDF            brand      brandFrench productLife
0  JPG  75401652167445  2016-06-28    P    NaN   NaN  001  Fromage Brick Petit Gaspesien  PETIT GASPESIEN  PETIT GASPESIEN          90
1  JPG  75401652167597  2016-06-28  NaN      M   NaN  001  Fromage Brick Petit Gaspesien  PETIT GASPESIEN  PETIT GASPESIEN          90
2  JPG   7540162167687  2016-06-28    C    NaN     C  001  Fromage Brick Petit Gaspesien  PETIT GASPESIEN  PETIT GASPESIEN          90

Finally c:

print(c)

Output:

  name   id                            eDF            brand      brandFrench productLife dAFF         dAGDTIF     dAIVDTF dASF dAITFa dAITF
0    y  001  Fromage Brick Petit Gaspesien  PETIT GASPESIEN  PETIT GASPESIEN          90  JPG  75401652167445  2016-06-28    P    NaN   NaN
1    y  001  Fromage Brick Petit Gaspesien  PETIT GASPESIEN  PETIT GASPESIEN          90  JPG  75401652167597  2016-06-28  NaN      M   NaN
2    y  001  Fromage Brick Petit Gaspesien  PETIT GASPESIEN  PETIT GASPESIEN          90  JPG   7540162167687  2016-06-28    C    NaN     C
3    X  001  Fromage Brick Petit Gaspesien  PETIT GASPESIEN  PETIT GASPESIEN          90  JPG  75401652167445  2016-06-28    P    NaN   NaN
4    X  001  Fromage Brick Petit Gaspesien  PETIT GASPESIEN  PETIT GASPESIEN          90  JPG  75401652167597  2016-06-28  NaN      M   NaN
5    X  001  Fromage Brick Petit Gaspesien  PETIT GASPESIEN  PETIT GASPESIEN          90  JPG   7540162167687  2016-06-28    C    NaN     C

edited Sep 19, 2019 at 18:08

answered Sep 19, 2019 at 15:37

Celius Stingher

18.4k6 gold badges26 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Usman Rafiq Over a year ago

Can i also pass other nested array fields in json_normalize function?

Celius Stingher Over a year ago

Yes, edit your question and I'll edit my asnwer accordingly.

Usman Rafiq Over a year ago

Okay So for every nested field we have to create another variable and then merge altogether right using inner Right?

Celius Stingher Over a year ago

I just couldn't find a way to work it in one line, I've been trying to search for some examples and also couldn't make lostCode's answer work. You might try here, maybe you can find something stackoverflow.com/questions/47242845/… However it is not the same, I hope my answer was helpful enough to help you solve your problem.

Usman Rafiq Over a year ago

Yes it was useful , Thank you :)

Collectives™ on Stack Overflow

Normalizing Complex Json in python

1 Answer 1

When trying for two cases:

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

When trying for two cases:

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related