0

I wanted to convert my json file into CSV file using python, I went through number of sites and they all are limited to simple json format. anybody know how we can do it for complex json file which has nested format ?

{ "tttt":"546"tttt, "reportId":"", "erete":"umereteort", "adpPayrollResponse":{ "asOfDate":"2020-11-25", "governmentID":{ "id":"xxx-xx-gfdgfg", "fffffffCode":{ "fffffff":"tttt", "code":"gdfgf" } }, "personalData":{ "personfffffff":{ "givenfffffff":"tttt", "middlefffffff":"S", "familyfffffff":"tttt" }, "communication":{ "telephones":[ {

           }
        ],
        "emails":[
           {
              "emailUri":"jtttt"
           }
        ],
        "internetAddresses":[
           
        ]
     },
     "address":{
        "lineOne":"tttt",
        "cityfffffff":"Brgfgfgfdistow",
        "subdivisionCode1":{
           "fffffff":"VA"
        },
        "erete":"US",
        "erete":"fgfdgfdgfg"
     }
  },
  "main":[
     {
        "employerfffffff":"tttt",
        "legalEntityID":{
           "legalEntityID":"fgfdgfgfdg",
           "erete":null
        },
        "gfdgfgfg":{
           "lineOne":"9500 fgfdgfdgfdg DR",
           "cityfffffff":"MANASSAS",
           "gfdgfgf":{
              "fffffff":"VA"
           },
           "fgdfgf":"gfdgdfg",
           "erete":"20110"
        },
        "ffgfg":"2003-06-16",
        "erete":{
           "fffffff":"Active",
           "code":"A"
        },
        "fgfdggdf":{
           "fffffff":"Regular Full-Time",
           "code":"FT"
        },
        "erete":"Business Analyst",
        "fdgfdgfg":"gfgfgfg-06fgfgg16",
        "fgfdgfdgfg":"dfgfgfdg",
        "remunerationSummary":[
           
           {
              "payrollYear":2019,
              "baseRemunerationeeeeeee":{
                 "eeeeeee":114329.73,
                 "cccccc":"tttt"
              },
              "additionalRemunerations":[
                 {
                    "tttt":"tttt",
                    "remunerationeeeeeee":{
                       "eeeeeee":22000.0,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "tttt":"tttt",
                    "remunerationeeeeeee":{
                       "eeeeeee":0.0,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "tttt":"YTD Others",
                    "remunerationeeeeeee":{
                       "eeeeeee":40558.47,
                       "cccccc":"tttt"
                    }
                 }
              ],
              "totalAnnualRemunerationeeeeeee":{
                 "eeeeeee":146688.0,
                 "cccccc":"tttt"
              },
              "netPayYTDeeeeeee":{
                 "eeeeeee":85395.04,
                 "cccccc":"tttt"
              }
           },
           {
              "payrollYear":2018,
              "baseRemunerationeeeeeee":{
                 "eeeeeee":31539.28,
                 "cccccc":"tttt"
              },
              "additionalRemunerations":[
                 {
                    "tttt":"tttt",
                    "remunerationeeeeeee":{
                       "eeeeeee":10500.0,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "tttt":"tttt",
                    "remunerationeeeeeee":{
                       "eeeeeee":0.0,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "tttt":"YTD Others",
                    "remunerationeeeeeee":{
                       "eeeeeee":11646.84,
                       "cccccc":"tttt"
                    }
                 }
              ],
              "totalAnnualRemunerationeeeeeee":{
                 "eeeeeee":35301.3,
                 "cccccc":"tttt"
              },
              "netPayYTDeeeeeee":{
                 "eeeeeee":26036.31,
                 "cccccc":"tttt"
              }
           }
        ],
        "paymentHistory":[
           {
              "tttt":true,
              "tttt":null,
              "dfsd":"2020-11-25",
              "payPeriod":{
                 "tttt":"2020-10-31",
                 "tttt":"2020-11-13"
              },
              "payeeeeeee":{
                 "dfsd":{
                    "eeeeeee":4640.36,
                    "cccccc":"tttt"
                 },
                 "basePayeeeeeee":{
                    "eeeeeee":4305.11,
                    "cccccc":"tttt"
                 },
                 "dfsd":{
                    "eeeeeee":2654.71,
                    "cccccc":"tttt"
                 },
                 "bonusPayeeeeeee":{
                    "eeeeeee":0.0,
                    "cccccc":"tttt"
                 },
                 "overtimePayeeeeeee":{
                    "eeeeeee":0.0,
                    "cccccc":"tttt"
                 },
                 "otherPayeeeeeee":{
                    "eeeeeee":335.25,
                    "cccccc":"tttt"
                 }
              },
              "tttt":"80.0",
              "tttt":{
                 "fffffff":"tttt",
                 "code":null
              },
              "tttt":{
                 "eeeeeee":4305.11,
                 "cccccc":"tttt"
              },
              "123":{
                 "fffffff":"Bi-Weekly",
                 "code":"B"
              },
             
              "de":[
                 {
                    "erete":{
                       "fffffff":"tttt 123",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":704.17,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"State 123",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":219.27,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"Local 123",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":0.0,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"Social security 123",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":273.61,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"Retirement de",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":215.26,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"Garnishment de",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":0.0,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"tttt",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":0.0,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"Benefit de",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":234.22,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"SUI SDI VPDI 123",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":0.0,
                       "cccccc":"tttt"
                    }
                 }
              ]
           },
           {
              "tttt":false,
              "tttt":null,
              "dfsd":"2020-11-10",
              "payPeriod":{
                 "tttt":"2020-10-17",
                 "tttt":"2020-10-30"
              },
              "payeeeeeee":{
                 "dfsd":{
                    "eeeeeee":4325.81,
                    "cccccc":"tttt"
                 },
                 "basePayeeeeeee":{
                    "eeeeeee":4305.11,
                    "cccccc":"tttt"
                 },
                 "dfsd":{
                    "eeeeeee":2457.8,
                    "cccccc":"tttt"
                 },
                 "bonusPayeeeeeee":{
                    "eeeeeee":0.0,
                    "cccccc":"tttt"
                 },
                 "overtimePayeeeeeee":{
                    "eeeeeee":0.0,
                    "cccccc":"tttt"
                 },
                 "otherPayeeeeeee":{
                    "eeeeeee":20.7,
                    "cccccc":"tttt"
                 }
              },
              "tttt":"80.0",
              "tttt":{
                 "fffffff":"tttt",
                 "code":null
              },
              "tttt":{
                 "eeeeeee":4305.11,
                 "cccccc":"tttt"
              },
              "123":{
                 "fffffff":"Bi-Weekly",
                 "code":"B"
              },
              "de":[
                 {
                    "depositeeeeeee":{
                       "eeeeeee":2457.8,
                       "cccccc":"tttt"
                    },
                    "dfsd":{
                       "tttt":"05100001",
                       "name":"tttt",
                       "tttt":"tttt"
                    }
                 }
              ],
              "de":[
                 {
                    "erete":{
                       "fffffff":"tttt 123",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":628.68,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"State 123",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":201.18,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"Local 123",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":0.0,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"Social security 123",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":254.1,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"Retirement de",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":215.26,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"Garnishment de",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":0.0,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"tttt",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":0.0,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"Benefit de",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":234.22,
                       "cccccc":"tttt"
                    }
                 },
                 {
                    "erete":{
                       "fffffff":"SUI SDI VPDI 123",
                       "code":null
                    },
                    "deductioneeeeeee":{
                       "eeeeeee":0.0,
                       "cccccc":"tttt"
                    }
                 }
              ]
           },
           {
           {
              "tttt":false,
              "tttt":null,
              "dfsd":"2020-10-14",
              "payPeriod":{
                 "tttt":"2020-09-19",
                 "tttt":"2020-10-02"
              },
              "payeeeeeee":{
                 "dfsd":{
                    "eeeeeee":4460.34,
                    "cccccc":"tttt"
                 },
                 "basePayeeeeeee":{
                    "eeeeeee":4305.11,
                    "cccccc":"tttt"
                 },
                 "dfsd":{
                    "eeeeeee":2537.29,
                    "cccccc":"tttt"
                 },
                 "bonusPayeeeeeee":{
                    "eeeeeee":0.0,
                    "cccccc":"tttt"
                 },
                 "overtimePayeeeeeee":{
                    "eeeeeee":0.0,
                    "cccccc":"tttt"
                 },
                 "otherPayeeeeeee":{
                    "eeeeeee":155.23,
                    "cccccc":"tttt"
                 }
              },
              "tttt":"82.5",
              "tttt":{
                 "fffffff":"tttt",
                 "code":null
              },
              "tttt":{
                 "eeeeeee":4305.11,
                 "cccccc":"tttt"
              },
              "123":{
                 "fffffff":"Bi-Weekly",
                 "code":"B"
              },
              "de":[
                 {
                    "depositeeeeeee":{
                       "eeeeeee":2537.29,
                       "cccccc":"tttt"
                    },
                    "dfsd":{
                       "tttt":"05100001",
                       "name":"tttt",
                       "tttt":"tttt"
                    }
                 }
              ],
           
           }

        ]
     }
  ],
  "asaadaff":{
     "availableHistorySourceCount":1,
     "includedHistorySourceCount":1,
     "availablePaymentHistoryMonths":{
        "payrollDataMonthCount":26,
        "quarterlyDataMonthCount":0
     }
  },
  "adpCorrelationId":"3941321f-844d-46f3-8b4a-7671c80e6e58",
  "payrollProvider":"adp"

} }

Mallikarjun
  • 13
  • 1
  • 5
  • It would be good if you provided a better formed json. – frankr6591 Dec 08 '20 at 05:31
  • assuming you have complex nested json... you will need to flatten out the data into a table form... you are basically going from N dimensions into 2... thus each of the N prefixes need to be represented as columns from what I can see. – frankr6591 Dec 08 '20 at 05:34
  • recommend using json pretty print to indent nicely. https://stackoverflow.com/questions/12943819/how-to-prettyprint-a-json-file – frankr6591 Dec 08 '20 at 05:36
  • try to use pandas to convert json to csv. https://datatofish.com/json-string-to-csv-python/ – harshil suthar Dec 08 '20 at 05:58

1 Answers1

0

I tried to clean up a bit of your sample json (1st part) to see the data & test conversion to csv.

The following generic function to flattens the namespace of the CSV columns. You will need to customize this for the specific name space of the csv. Unclear from desciption now.

flatDict 
def flatDict(newDict, oldDict, key=None):
    for k,v in oldDict.items():
        useKey = '_'+key if key is not None else ''
        newKey = k+useKey
        if type(v) is dict:
            flatDict(newDict, v, key=newKey)
            continue
        newDict[newKey] = v
nDict = {}
flatDict(nDict, jDict, key=None)
print(jso

Flatten JSON for CSV

{
    "tttt": "546",
    "reportId": "",
    "erete": "umereteort",
    "asOfDate_adpPayrollResponse": "2020-11-25",
    "id_governmentID_adpPayrollResponse": "xxx-xx-gfdgfg",
    "fffffff_fffffffCode_governmentID_adpPayrollResponse": "tttt",
    "code_fffffffCode_governmentID_adpPayrollResponse": "gdfgf",
    "givenfffffff_personfffffff_personalData_adpPayrollResponse": "tttt",
    "middlefffffff_personfffffff_personalData_adpPayrollResponse": "S",
    "familyfffffff_personfffffff_personalData_adpPayrollResponse": "tttt",
    "telephones_communication_personalData_adpPayrollResponse": [
        {}
    ],
    "emails_communication_personalData_adpPayrollResponse": [
        {
            "emailUri": "jtttt"
        }
    ],
    "internetAddresses_communication_personalData_adpPayrollResponse": [],
    "lineOne_address_personalData_adpPayrollResponse": "tttt",
    "cityfffffff_address_personalData_adpPayrollResponse": "Brgfgfgfdistow",
    "fffffff_subdivisionCode1_address_personalData_adpPayrollResponse": "VA",
    "erete_address_personalData_adpPayrollResponse": "fgfdgfdgfg"
}

JSON SAMPLE FROM description above

The following was copy of some of json above as input to flatDict()

j1 = '''{ original json here } '''

jDict = json.loads(j1)
print(json.dumps(jDict, indent=4))

PRETTY PRINT

{
    "tttt": "546",
    "reportId": "",
    "erete": "umereteort",
    "adpPayrollResponse": {
        "asOfDate": "2020-11-25",
        "governmentID": {
            "id": "xxx-xx-gfdgfg",
            "fffffffCode": {
                "fffffff": "tttt",
                "code": "gdfgf"
            }
        },
        "personalData": {
            "personfffffff": {
                "givenfffffff": "tttt",
                "middlefffffff": "S",
                "familyfffffff": "tttt"
            },
            "communication": {
                "telephones": [
                    {}
                ],
                "emails": [
                    {
                        "emailUri": "jtttt"
                    }
                ],
                "internetAddresses": []
            },
            "address": {
                "lineOne": "tttt",
                "cityfffffff": "Brgfgfgfdistow",
                "subdivisionCode1": {
                    "fffffff": "VA"
                },
                "erete": "fgfdgfdgfg"
            }
        }
    }
}
frankr6591
  • 1,211
  • 1
  • 8
  • 14