pandas json type columns converter

json2df.py

import pandas as pd

def json2df(json_df):
  def normalize_column(dataframe, column):
    df = pd.json_normalize(dataframe[column]) # Normalize column  
    df.columns = [f"{column}.{col}" for col in df.columns]
    return pd.concat([dataframe, df], axis=1).drop(columns=column)

  # id
  json_df['_id'] = json_df['_id'].apply(lambda _id: _id.get('$oid'))
  
  # city
  json_df = normalize_column(json_df, 'city') # Normalize 'city' Column
  
  # list
  json_df = json_df.explode('list').reset_index(drop=True) # List type column to rows.
  json_df = normalize_column(json_df, 'list') # Normalize 'list' Column

  json_df = json_df.explode('list.weather').reset_index(drop=True) # List type column to rows.
  json_df = normalize_column(json_df, 'list.weather') # Normalize 'list.weather' Column
  
  return json_df

df = pd.read_json('/content/forecast_ist.json', orient='records')
processed_df = json2df(df)
display(processed_df.head())