Trying to Parse JSON using Pandas in the YoutubeAnalytics Api and Convert it CSV

user_2912702 0
Viewed 113

I am trying to download my youtube data using the Youtube Analytics and Reporting API from here Google Reference. I am using Python code from google to download this data which works fine. The data is retrieved in JSON format As shown below.

rawdata.json -

{
  "kind": "youtubeAnalytics#resultTable",
  "columnHeaders": [
    {
      "name": "day",
      "columnType": "DIMENSION",
      "dataType": "STRING"
    },
    {
      "name": "views",
      "columnType": "METRIC",
      "dataType": "INTEGER"
    },
    {
      "name": "estimatedMinutesWatched",
      "columnType": "METRIC",
      "dataType": "INTEGER"
    },
    {
      "name": "averageViewDuration",
      "columnType": "METRIC",
      "dataType": "INTEGER"
    },
    {
      "name": "averageViewPercentage",
      "columnType": "METRIC",
      "dataType": "FLOAT"
    },
    {
      "name": "subscribersGained",
      "columnType": "METRIC",
      "dataType": "INTEGER"
    }
  ],
  "rows": [
    [
      "2020-10-01",
      445,
      1068,
      144,
      38.78,
      3
    ],
    [
      "2020-10-02",
      406,
      905,
      133,
      34.94,
      2
    ],
    [
      "2020-10-03",
      466,
      1042,
      134,
      34.77,
      6
    ],
    [
      "2020-10-04",
      427,
      1006,
      141,
      36.48,
      3
    ],
    [
      "2020-10-05",
      379,
      964,
      152,
      39.48,
      2
    ]
  ]
}

I am trying to convert this JSON into CSV format but having no luck so far.

Here is the python code I am using to normalize and create a data frame but having no luck.

import json
import pandas as pd
from pandas import json_normalize 

with open('rawdata.json') as json_file:
    data = json.load(json_file)
    #print (data)

df = json_normalize(data['rows'])
print(df)

I am trying to get the final output in this format. enter image description here

Any help or guidance is much appreciated.

EDIT: Answer by Suraj S. This worked out for me.

import json
import pandas as pd
from pandas import json_normalize 

with open('rawdata.json') as json_file:
    data = json.load(json_file)
    #print (data)

df = pd.DataFrame(data['rows'],columns=['day','views','comments','likes','dislikes','estimatedMinutesWatched','averageViewDuration','subscribersGained'])

print(df)
pandas dataframe python json
2 Answers
pd.DataFrame(json_file['rows'])

you can add column names as u req if u want

 pd.DataFrame(json_file['rows'],columns=[a,b,c,...])
user_143917790

Looks like you want to make use of pd.read_json(___, orient='split').

You have to organize your input into a dictionary of three elements with a list as its values: "columns", "index", and "data". You should write a lines to prepare this data:

For "columns", just put the "name"s into its own list. For "data", you don't have to change anything I don't think, just make sure you rename that list of lists "data".

You can use some garbage "row 1", "row 2"... elements for the "index", but the most important ones for you are "columns" and "data".

You can look at it here in more depth.

user_119121610
Related