Changing nested json files into a data frame using python

Question

I have a json file that looks like below and I want to convert it into a data frame. It is an example of output from API LinkedIn and I want to convert it into a data frame so that i can save it in csv format. The problem is that the json file is nested and I couldn't find a straight forward way to achieve this.

{
  "paging": {
    "count": 10,
    "start": 0
  },
  "elements": [
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1451606400000,
        "end": 1451692800000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "pageViews": 0
          }
        }
      }
    },
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1451692800000,
        "end": 1451779200000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "pageViews": 0
          }
        }
      }
    },
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1451779200000,
        "end": 1451865600000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "pageViews": 0
          }
        }
      }
    },
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1451865600000,
        "end": 1451952000000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "pageViews": 0
          }
        }
      }
    },
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1451952000000,
        "end": 1452038400000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "pageViews": 0
          }
        }
      }
    },
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1452038400000,
        "end": 1452124800000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          }
        }
      }
    },
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1452124800000,
        "end": 1452211200000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          }
        }
      }
    }
  ]
}

I am really new to list comprehension in python and I want someone to help me on converting it into a data frame.

Does this answer your question? [How can I convert JSON to CSV?](https://stackoverflow.com/questions/1871524/how-can-i-convert-json-to-csv) — jizhihaoSAMA, Aug 24 '20 at 06:07
why you need list comprehension for converting json to dataframe? can you post the expected output — deadshot, Aug 24 '20 at 06:08

score 1 · Accepted Answer · answered Aug 24 '20 at 06:47

You can use json_normalize from pandas with max_level for nested json

from pandas import json_normalize
d_ = [
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1451606400000,
        "end": 1451692800000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "pageViews": 0
          }
        }
      }
    },
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1451692800000,
        "end": 1451779200000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "pageViews": 0
          }
        }
      }
    },
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1451779200000,
        "end": 1451865600000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "pageViews": 0
          }
        }
      }
    },
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1451865600000,
        "end": 1451952000000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "pageViews": 0
          }
        }
      }
    },
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1451952000000,
        "end": 1452038400000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "pageViews": 0
          }
        }
      }
    },
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1452038400000,
        "end": 1452124800000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          }
        }
      }
    },
    {
      "organization": "urn:li:organization:1000",
      "timeRange": {
        "start": 1452124800000,
        "end": 1452211200000
      },
      "totalPageStatistics": {
        "clicks": {
          "careersPageClicks": {
            "careersPageBannerPromoClicks": 0,
            "careersPagePromoLinksClicks": 0,
            "careersPageEmployeesClicks": 0,
            "careersPageJobsClicks": 0
          }
        },
        "views": {
          "careersPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "overviewPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          },
          "allPageViews": {
            "uniquePageViews": 0,
            "pageViews": 0
          }
        }
      }
    }
  ]
# }

df = json_normalize(d_, max_level=2)
print(df)

but one problem I am encountering while doing this is that I have to convert the json into list by including it into []. Is there anyways to convert the json file with out actually using [] instead by using something like .tolist(). This is not working in my case, but I cam just wondering if i can directely apply json_normalize function to the json variable? — user86907, Aug 24 '20 at 06:58

Changing nested json files into a data frame using python

1 Answers1