Parsing json into csv format with multiple columns-CodePudding

When exporting my json into a csv, using df.to_csv('codetocsv.csv', index=False), my csv format groups my 'series' column into one individual column. Im trying to have each string in its own column ie, a column for command, copyright etc, and a column for each year. There are multiple countries within the json.

{
  "request": {
    "command": "series",
  },
  "series": [
    {
      "f": "A",
      "copyright": "None",
      "updated": "2022",
      "data": [
        [
          "2021",
          404.800507146
        ],
        [
          "2020",
          371.59497253
        ],
        [
          "2019",
          392.433272519
        ],
        [
          "2018",
          397.800544326
        ]
      ]
    }
  ]
}

df = pd.concat(dfs, ignore_index=True)


data = df['series'][0]['data']

for row in data:
    year = row[0]
    value = row[1]
    print(f'Year: {year}, Value: {value}')

CodePudding user response：

You could use the pandas library to convert it to csv. Since you would like to create a table with list of dictionaries, it would throw you a valueerror if you directly want to convert your data to csv (https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_csv.html?highlight=to_csv#pandas.DataFrame.to_csv).

As you would like to have each year in 'data' as a separate column, and also a separate column for 'command' and other dictionaries in 'series' list. The sample code is given below as follows:

import pandas as pd
from pandas import DataFrame as df

data = {
  "request": {
    "command": "series",
  },
  "series": [
    {
      "f": "A",
      "copyright": "None",
      "updated": "2022",
      "data": [
        [
          "2021",
          404.800507146
        ],
        [
          "2020",
          371.59497253
        ],
        [
          "2019",
          392.433272519
        ],
        [
          "2018",
          397.800544326
        ]
      ]
    }
  ]
}

result = {}
for values in data:
    for itx in data[values]:        
        if isinstance(itx,dict):
            for itxval in itx:
                if isinstance(itx[itxval], list):
                    for itxval_val in itx[itxval]:
                        result.update({itxval_val[0]: [itxval_val[1]]})
                else:
                    result.update({itxval: [itx[itxval]]})
        else:
            pass
print(result)

data_frame = df(result)

data = df.to_csv(data_frame, index=False)
print(data)

Result:

f,copyright,updated,2021,2020,2019,2018
A,None,2022,404.800507146,371.59497253,392.433272519,397.800544326

Is this your expected output?