JSON data into csv by using python maybe pandas specific format

user_12560892 0
Viewed 56

the goal is to convert the given JSON into the CSV data Frame according to the below sample output. this is a part of the whole project. anyone would like to exercise.

response.json

[
    {
      
      "fiscalPeriodYearMonth": "2012-09",
      
      "revenuePer": {
        "yearOverYear": 19.57,
        "threeYearAvg": 28.24,
        "fiveYearAvg": 21.240000000000002,
        "tenYearAvg": 28.96
      },
      "operatingIncome": {
        "yearOverYear": 21.57,
        "threeYearAvg": 50.019999999999996,
        "fiveYearAvg": 30.3,
        "tenYearAvg": null
      },
      "netIncomePer": {
        "yearOverYear": 14.000000000000002,
        "threeYearAvg": 44.330000000000005,
        "fiveYearAvg": 29.01,
        "tenYearAvg": null
      },
      "epsPer": {
        "yearOverYear": 16.55,
        "threeYearAvg": 44.65,
        "fiveYearAvg": 30.830000000000002,
        "tenYearAvg": null
        }
    },

    {
     
      "fiscalPeriodYearMonth": "2013-09",
      "revenuePer": {
        "yearOverYear": 7.5600000000000005,
        "threeYearAvg": 18.87,
        "fiveYearAvg": 17.9,
        "tenYearAvg": 29.020000000000003
      },
      "operatingIncome": {
        "yearOverYear": 1.06,
        "threeYearAvg": 23.27,
        "fiveYearAvg": 34.11,
        "tenYearAvg": 58.93000000000001
      },
      "netIncomePer": {
        "yearOverYear": 0.77,
        "threeYearAvg": 22.42,
        "fiveYearAvg": 30.12,
        "tenYearAvg": 52.459999999999994
      },
      "epsPer": {
        "yearOverYear": 1.4500000000000002,
        "threeYearAvg": 23.46,
        "fiveYearAvg": 31.5,
        "tenYearAvg": 47.88
      }
    }
  
  ]

My Code:

import pandas as pd
df = pd.read_json(r'PATH TO JSON FILE', orient ='values')

print(df.T.to_csv("final_output.csv"))

But it does not giving me the desired following format.

Output I need

2012-09 2013-09
Revenue
Year Over Year 19.57 7.56
3-Year Average 28.24 18.87
5-Year Average 21.24 17.90
10-Year Average 28.96 29.02
operatingIncome
Year Over Year 21.57 1.06
3-Year Average 50.02 23.27
5-Year Average 30.30 34.11
10-Year Average - 58.93

and so on.

I know its a bit logical but very interesting, and will be so easy for someone good in python pandas.

data-science pandas dataframe python json
1 Answers

I haven't used python pandas before, so I wrote it without it (the only import is a built-in module):

import json

with open("test.json") as file:
    data = json.loads(file.read())


keys = []

isFirst = True

i = 0

for entry in data:
    j = 0
    arrayIndex = 0
    for key in list(entry.keys()):
        
        if isFirst:
            keys.append([key])
        arrayIndex += 1
        if type(data[i][key]) == dict:
            for innerKey in data[i][key]:
                if isFirst:
                    keys.append([innerKey])

                this_data = data[i][key][innerKey]
                if type(this_data) == float:
                    this_data = round(this_data, 2)
                elif type(this_data) == None:
                    this_data = "-"
                keys[arrayIndex].append(this_data)
                arrayIndex += 1
                
        else:
            keys[j].append(entry[key])
        j += 1

    isFirst = False
    i += 1

output = ""
for i in keys:
    for j in i:
        output += str(j) + ","
    output = output[:-1]
    output += "\n"
output = output[:-1]

f = open("output.csv", "w")
f.write(output)
f.close()

It's a lot longer, but it does work

user_181224980
Related