pandas json normalize not working as expected

Question

I'm using pandas json normalize to transform a json into a pandas dataframe. This is the json that's giving me problems:

    {
   "P48PrecTer":{
      "@xmlns":"http://sujetos.esios.ree.es/schemas/2010/05/31/P48PrecTer-esios-MP/",
      "IdSesion":"16",
      "SeriesTemporales":{
         "IdentificacionSeriesTemporales":"STP0",
         "TipoNegocio":"A10",
         "UnidadPrecio":"EUR:MWH",
         "Periodo":[
            {
               "IntervaloTiempo":"2020-07-12T00:00Z/2020-07-12T01:00Z",
               "Resolucion":"PT60M",
               "Intervalo":{
                  "Pos":"1",
                  "PrecioBaj":"25.20"
               }
            },
            {
               "IntervaloTiempo":"2020-07-12T03:00Z/2020-07-12T11:00Z",
               "Resolucion":"PT60M",
               "Intervalo":[
                  {
                     "Pos":"1",
                     "PrecioSub":"27.36"
                  },
                  {
                     "Pos":"2",
                     "PrecioBaj":"23.50"
                  },
                  {
                     "Pos":"8",
                     "PrecioBaj":"16.90"
                  }
               ]
            },
            {
               "IntervaloTiempo":"2020-07-12T12:00Z/2020-07-12T16:00Z",
               "Resolucion":"PT60M",
               "Intervalo":[
                  {
                     "Pos":"1",
                     "PrecioSub":"29.90"
                  },
                  {
                     "Pos":"4",
                     "PrecioBaj":"15.75"
                  }
               ]
            }
         ]
      }
   }
}

I'm using this piece of code:

prueba=pd.json_normalize(body,record_path=['P48PrecTer','SeriesTemporales','Periodo','Intervalo'], meta=[['P48PrecTer','IdSesion'], ['P48PrecTer','SeriesTemporales','Periodo','IntervaloTiempo']])

I expect something with a csv structure. However, I get this:

0|P48PrecTer.IdSesion|P48PrecTer.SeriesTemporales.Periodo.IntervaloTiempo
Pos|06|2020-07-12T22:00Z/2020-07-12T23:00Z
PrecioBaj|06|2020-07-12T22:00Z/2020-07-12T23:00Z
PrecioSub|06|2020-07-12T22:00Z/2020-07-12T23:00Z
{'Pos': '1', 'PrecioBaj': '4.41', 'PrecioSub': 'null'}|06|2020-07-13T00:00Z/2020-07-13T03:00Z
{'Pos': '2', 'PrecioBaj': '9.00', 'PrecioSub': 'null'}|06|2020-07-13T00:00Z/2020-07-13T03:00Z
{'Pos': '3', 'PrecioBaj': '10.10', 'PrecioSub': 'null'}|06|2020-07-13T00:00Z/2020-07-13T03:00Z

I am guessing that the problem may be the list associated with 'Periodo' key, as I've worked with other json with a similar structure and didn't have any problem. Is there any way to get to the point I'm aiming? Thank you.

Partha Mandal · Accepted Answer · 2020-07-14T13:43:12.960

Yeah, the issue is the lists present in the dict, but, we can solve it this way:

df = pd.json_normalize(data['P48PrecTer']['SeriesTemporales'],['Periodo'],errors='ignore')
df = df.explode('Intervalo')
df = pd.concat([df,df['Intervalo'].apply(pd.Series)],axis=1).drop(columns=[0,'Intervalo'])

for i in ['Pos','PrecioBaj']:
    df.loc[df[i].isna(),i] = df.loc[df[i].isna(),'Intervalo.'+i]
    df = df.drop(columns=['Intervalo.'+i])

print(df)

Output

                       IntervaloTiempo Resolucion Pos PrecioBaj PrecioSub
0  2020-07-12T00:00Z/2020-07-12T01:00Z      PT60M   1     25.20       NaN
1  2020-07-12T03:00Z/2020-07-12T11:00Z      PT60M   1       NaN     27.36
1  2020-07-12T03:00Z/2020-07-12T11:00Z      PT60M   2     23.50       NaN
1  2020-07-12T03:00Z/2020-07-12T11:00Z      PT60M   8     16.90       NaN
2  2020-07-12T12:00Z/2020-07-12T16:00Z      PT60M   1       NaN     29.90
2  2020-07-12T12:00Z/2020-07-12T16:00Z      PT60M   4     15.75       NaN

Actually, I was a little scared of the code at first, but I think I'm gonna implement this code as I really like the logic behind. Thank you! — Rorepio, Jul 14 '20 at 14:20

score 2 · Answer 2 · answered Jul 14 '20 at 13:08

The problem is you have an array inside an array in the JSON, you won't be able to call json_normalize just once. It will have to be in a loop and then you would need to concatenate the DataFrames and then merge with the outer DataFrames.

for i in data['P48PrecTer']['SeriesTemporales']['Periodo']:
    df = pd.json_normalize(i, record_path=['Intervalo'], meta=[['IntervaloTiempo'], ['Resolucion']])
    print(df)

           0                      IntervaloTiempo Resolucion
0        Pos  2020-07-12T00:00Z/2020-07-12T01:00Z      PT60M
1  PrecioBaj  2020-07-12T00:00Z/2020-07-12T01:00Z      PT60M
  Pos PrecioSub PrecioBaj                      IntervaloTiempo Resolucion
0   1     27.36       NaN  2020-07-12T03:00Z/2020-07-12T11:00Z      PT60M
1   2       NaN     23.50  2020-07-12T03:00Z/2020-07-12T11:00Z      PT60M
2   8       NaN     16.90  2020-07-12T03:00Z/2020-07-12T11:00Z      PT60M
  Pos PrecioSub PrecioBaj                      IntervaloTiempo Resolucion
0   1     29.90       NaN  2020-07-12T12:00Z/2020-07-12T16:00Z      PT60M
1   4       NaN     15.75  2020-07-12T12:00Z/2020-07-12T16:00Z      PT60M

A better way to do it is using flatten_json

Thank you so much!! That's exactly what I was looking for. I will look into flatten_json too. — Rorepio, Jul 14 '20 at 13:36
Ok cool. You can mark that as the answer, and consider upvoting too. — NYC Coder, Jul 14 '20 at 13:46
Hey @nyc-coder, isn't this missing the first item where `PrecioBaj` is `25.20 ` — Partha Mandal, Jul 14 '20 at 13:46

pandas json normalize not working as expected

2 Answers2