1

I am a newbie to Python and I have the below holiday Calendar in nested Json Format 

{  
      'Holiday_Calendar':'Holilday Calendar - Texas',
      'Holiday_Calendar_Group':[  
         {  
            'Holiday_Date_Time':'2019-12-25T08:00:00.000-08:00',
            'Holiday_Name':'Christmas Day'
         },
         {  
            'Holiday_Date_Time':'2019-07-04T08:00:00.000-07:00',
            'Holiday_Name':'Independence Day'
         },
         {  
            'Holiday_Date_Time':'2019-09-02T08:00:00.000-07:00',
            'Holiday_Name':'Labor Day'
         },
         {  
            'Holiday_Date_Time':'2019-05-27T08:00:00.000-07:00',
            'Holiday_Name':'Memorial Day'
         },
         {  
            'Holiday_Date_Time':'2019-01-01T08:00:00.000-08:00',
            'Holiday_Name':"New Year's Day"
         },
         {  
            'Holiday_Date_Time':'2019-11-28T08:00:00.000-08:00',
            'Holiday_Name':'Thanksgiving Day'
         }
      ]
   },
   {  
      'Holiday_Calendar':'Holilday Calendar - Kansas',
      'Holiday_Calendar_Group':[  
         {  
            'Holiday_Date_Time':'2019-12-25T08:00:00.000-08:00',
            'Holiday_Name':'Christmas Day'
         },
         {  
            'Holiday_Date_Time':'2019-11-29T08:00:00.000-08:00',
            'Holiday_Name':'Day After Thanksgiving'
         },
         {  
            'Holiday_Date_Time':'2019-07-04T08:00:00.000-07:00',
            'Holiday_Name':'Independence Day'
         },
         {  
            'Holiday_Date_Time':'2019-09-02T08:00:00.000-07:00',
            'Holiday_Name':'Labor Day'
         },
         {  
            'Holiday_Date_Time':'2019-05-27T08:00:00.000-07:00',
            'Holiday_Name':'Memorial Day'
         },
         {  
            'Holiday_Date_Time':'2019-01-01T08:00:00.000-08:00',
            'Holiday_Name':"New Year's Day"
         },
         {  
            'Holiday_Date_Time':'2019-11-28T08:00:00.000-08:00',
            'Holiday_Name':'Thanksgiving Day'
         }
      ]
   }
}

I need to get the flatten / simple json to create a pd dataframe

I tried using the json_normalize but it seems not working

abc =[]    
for index, row in result.iterrows():
        r1 = json_normalize(row['Holiday_Calendar_Group'],record_path=['Holiday_Calendar','Holiday_Calendar_Group'], meta =['Holiday_Calendar',['Holiday_Date_Time','Holiday_Name']])
        abc.append(r1)

Result expected is Holiday_Calendar, Holiday_Date_Time , Holiday_Name

ForceBru
  • 43,482
  • 10
  • 63
  • 98
Sparkles
  • 61
  • 2
  • 6

1 Answers1

1
result = [{   
    'Holiday_Calendar':'Holilday Calendar - Texas',
      'Holiday_Calendar_Group':[  
         {  
            'Holiday_Date_Time':'2019-12-25T08:00:00.000-08:00',
            'Holiday_Name':'Christmas Day'
         },
         {  
            'Holiday_Date_Time':'2019-07-04T08:00:00.000-07:00',
            'Holiday_Name':'Independence Day'
         },
         {  
            'Holiday_Date_Time':'2019-09-02T08:00:00.000-07:00',
            'Holiday_Name':'Labor Day'
         },
         {  
            'Holiday_Date_Time':'2019-05-27T08:00:00.000-07:00',
            'Holiday_Name':'Memorial Day'
         },
         {  
            'Holiday_Date_Time':'2019-01-01T08:00:00.000-08:00',
            'Holiday_Name':"New Year's Day"
         },
         {  
            'Holiday_Date_Time':'2019-11-28T08:00:00.000-08:00',
            'Holiday_Name':'Thanksgiving Day'
         }
      ]
   },   {  
      'Holiday_Calendar':'Holilday Calendar - Kansas',
      'Holiday_Calendar_Group':[  
         {  
            'Holiday_Date_Time':'2019-12-25T08:00:00.000-08:00',
            'Holiday_Name':'Christmas Day'
         },
         {  
            'Holiday_Date_Time':'2019-11-29T08:00:00.000-08:00',
            'Holiday_Name':'Day After Thanksgiving'
         },
         {  
            'Holiday_Date_Time':'2019-07-04T08:00:00.000-07:00',
            'Holiday_Name':'Independence Day'
         },
         {  
            'Holiday_Date_Time':'2019-09-02T08:00:00.000-07:00',
            'Holiday_Name':'Labor Day'
         },
         {  
            'Holiday_Date_Time':'2019-05-27T08:00:00.000-07:00',
            'Holiday_Name':'Memorial Day'
         },
         {  
            'Holiday_Date_Time':'2019-01-01T08:00:00.000-08:00',
            'Holiday_Name':"New Year's Day"
         },
         {  
            'Holiday_Date_Time':'2019-11-28T08:00:00.000-08:00',
            'Holiday_Name':'Thanksgiving Day'
         }
      ]
   }
]


from pandas.io.json import json_normalize
import pandas as pd

df = pd.DataFrame()    
for index, row in enumerate(result):
    temp_df = json_normalize(row['Holiday_Calendar_Group'])
    temp_df['Holiday_Calendar'] = row['Holiday_Calendar']
    df = df.append(temp_df, sort=True).reset_index(drop=True)

Output:

print (df.to_string())
              Holiday_Calendar              Holiday_Date_Time            Holiday_Name
0    Holilday Calendar - Texas  2019-12-25T08:00:00.000-08:00           Christmas Day
1    Holilday Calendar - Texas  2019-07-04T08:00:00.000-07:00        Independence Day
2    Holilday Calendar - Texas  2019-09-02T08:00:00.000-07:00               Labor Day
3    Holilday Calendar - Texas  2019-05-27T08:00:00.000-07:00            Memorial Day
4    Holilday Calendar - Texas  2019-01-01T08:00:00.000-08:00          New Year's Day
5    Holilday Calendar - Texas  2019-11-28T08:00:00.000-08:00        Thanksgiving Day
6   Holilday Calendar - Kansas  2019-12-25T08:00:00.000-08:00           Christmas Day
7   Holilday Calendar - Kansas  2019-11-29T08:00:00.000-08:00  Day After Thanksgiving
8   Holilday Calendar - Kansas  2019-07-04T08:00:00.000-07:00        Independence Day
9   Holilday Calendar - Kansas  2019-09-02T08:00:00.000-07:00               Labor Day
10  Holilday Calendar - Kansas  2019-05-27T08:00:00.000-07:00            Memorial Day
11  Holilday Calendar - Kansas  2019-01-01T08:00:00.000-08:00          New Year's Day
12  Holilday Calendar - Kansas  2019-11-28T08:00:00.000-08:00        Thanksgiving Day
chitown88
  • 27,527
  • 4
  • 30
  • 59