Parsing json into csv format with multiple columns

Question

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}')

Ipvikukiepki-KQS · Accepted Answer · 2023-01-03 21:30:30Z

1

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?

answered Jan 3, 2023 at 21:30

Ipvikukiepki-KQS

1378 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jantheman Over a year ago

thanks for the help, my data is sadly in the wrong format, all " in the json are ', so when I try the code, I get the code 'TypeError: list indices must be integers or slices, not dict'

Ipvikukiepki-KQS Over a year ago

I used the same json inputs from your question. I could perceive that the structure of your entire json data might differ. Could you have a keen look at the data structure of the dictionaries in the reference that i attached? The problem is arising due to improper type of data within the list. You can make some customizations according to your json data structure. I am glad that it had helped you.

jantheman Over a year ago

is there a simple way to convert my json_data into the inputs used in the question? My current format is in a long python string that is in JSON format eg,

''[{"request":{"command":"series","series_id":"INTL.4008-8-AFG-MMTCD.A"},"series":[{"series_id":"INTL.4008-8-AFG-MMTCD.A","name":"CO2 emissions, Afghanistan, Annual","units":"million metric tonnes carbon dioxide","f":"A","copyright":"None","

etc.

Ipvikukiepki-KQS Over a year ago

I would suggest that then it would be easier to prepare(customize) the data before transforming your data to another format. I would recommend you to look for some articles (tutorials) to get a gist about the data which you are working with. E.g. realpython.com/python-json

Collectives™ on Stack Overflow

Parsing json into csv format with multiple columns

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related