JSON to CSV (python)

Question

i have a JSON file as follow:

{
  "temperature": [
    {
      "ts": 1672753924545,
      "value": "100"
    }
  ],
  "temperature c1": [
    {
      "ts": 1672753924545,
      "value": "30.99036523512186"
    }
  ],
  "conductivite_c1": [
    {
      "ts": 1672753924545,
      "value": "18.195760116755046"
    }
  ],
  "pression_c1": [
    {
      "ts": 1672753924545,
      "value": "10.557751448931295"
    }
  ],
  "ph_c1": [
    {
      "ts": 1672753924545,
      "value": "10.443975738053357"
    }
  ],
  "http": [
    {
      "ts": 1672753924545,
      "value": "400"
    }
  ]
}

this is my code :

import csv
import json


data = json.loads('{"temperature": [{"ts": 1672753924545, "value": "100"}], "temperature c1": [{"ts": 1672753924545, "value": "30.99036523512186"}], "conductivite_c1": [{"ts": 1672753924545, "value": "18.195760116755046"}], "pression_c1": [{"ts": 1672753924545, "value": "10.557751448931295"}], "ph_c1": [{"ts": 1672753924545, "value": "10.443975738053357"}], "http": [{"ts": 1672753924545, "value": "400"}]}')


with open('data.csv', 'w', newline='') as csvfile:
    writer = csv.DictWriter(csvfile, fieldnames=data.keys())
    writer.writeheader()

    
    for key in data:
        for row in data[key]:
            writer.writerow({key: row['value']})

i want to convert it to CSV with ts in rows ( not the same row ) and the keys are columns but it gives me a weired format where all the keys in the same column and no ts

Marcel Suleiman · Accepted Answer · 2023-01-06 12:14:33Z

1

If you want each time new data.csv, just replace "a" in open('data2.csv', 'a') to "w" and if you also need all csv files, you must generate new name for csv file

import json

input_data = json.loads('{"temperature": [{"ts": 1672753924545, "value": "100"}], "temperature c1": [{"ts": 1672753924545, "value": "30.99036523512186"}], "conductivite_c1": [{"ts": 1672753924545, "value": "18.195760116755046"}], "pression_c1": [{"ts": 1672753924545, "value": "10.557751448931295"}], "ph_c1": [{"ts": 1672753924545, "value": "10.443975738053357"}], "http": [{"ts": 1672753924545, "value": "400"}]}')


with open('data.csv', 'a') as csvfile:

    header_row = '"ts";'
    data_row = str()

    ts_flag = True

    for header, data in input_data.items():
        header_row += f'"{header}";'

        for key, value in data[0].items():

            print(key)
            print(value)

            if key == "ts":
                if ts_flag:
                    data_row += f'"{value}";'
                    ts_flag = False

            else:
                data_row += f'"{value}";'


    csvfile.write(header_row)
    csvfile.write("\n")
    csvfile.write(data_row)
    csvfile.write("\n")

edited Jan 6, 2023 at 12:14

answered Jan 6, 2023 at 10:58

Marcel Suleiman

1346 bronze badges

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

3 Comments

Brell Over a year ago

thank you for your answer , but the problem i can only see the values , my main point is to put keys as columns header and ts as rows and then value in cell value

Marcel Suleiman Over a year ago

You need separate data.csv file for every json input?

Brell Over a year ago

actually for each JSON i create a CSV the JSON file are created every hour ,what i gave you is just an example but the real JSON contain for each key, for exemple temperature 10 ts and 10 value , ts is same for all keys because we fetsh the data at same time but value is different for sure . i want to write keys : temperature ,temperature c1 ..etc in columns seperatly not all of them in the same column and for row 10 rows for exemple and we put all the ts in same column timestamp,i hope i am clear and sorry for bothering

HarryKane · Accepted Answer · 2023-01-06 11:24:04Z

1

I would reformat your data

import collections
import csv
import json
import typing

data: dict[str, list[dict[str, typing.Any]]] = json.loads(
    '{"temperature": [{"ts": 1672753924545, "value": "100"}], "temperature c1": [{"ts": 1672753924545, "value": "30.99036523512186"}], "conductivite_c1": [{"ts": 1672753924545, "value": "18.195760116755046"}], "pression_c1": [{"ts": 1672753924545, "value": "10.557751448931295"}], "ph_c1": [{"ts": 1672753924545, "value": "10.443975738053357"}], "http": [{"ts": 1672753924545, "value": "400"}]}'
)

table_dict = collections.defaultdict(lambda: collections.defaultdict(dict))

for key in data:
    for obj in data[key]:
        table_dict[obj["ts"]][key] = obj["value"]

with open('data.csv', 'w', newline='') as csvfile:
    writer = csv.DictWriter(csvfile, fieldnames=data.keys())
    writer.writeheader()
    for _, row in table_dict.items():
        writer.writerow(row)

answered Jan 6, 2023 at 11:24

HarryKane

1781 gold badge4 silver badges16 bronze badges

4 Comments

Brell Over a year ago

i got this error : TypeError: 'type' object is not subscriptable

HarryKane Over a year ago

@Brell which python version do you use? EDIT: If your python version does not support type annotations yet (line 6) you can remove this part. It its not mandatory ``` data = json.loads( ```

Brell Over a year ago

i use python 3.7.8

HarryKane Over a year ago

@Brell Sorry ... I overlooked your comment. The problem seems to be the type annotations then. Annotations for type have been typing.Type, for dict it was typing.Dict and so on, ... This has changed in Python 3.9 (docs.python.org/3.9/whatsnew/3.9.html)

Drejc · Accepted Answer · 2023-01-06 11:44:33Z

1

Here is a simple implementation:

import csv
import json

data = json.loads('{"temperature": [{"ts": 1672753924545, "value": "100"}], "temperature c1": [{"ts": 1672753924545, "value": "30.99036523512186"}], "conductivite_c1": [{"ts": 1672753924545, "value": "18.195760116755046"}], "pression_c1": [{"ts": 1672753924545, "value": "10.557751448931295"}], "ph_c1": [{"ts": 1672753924545, "value": "10.443975738053357"}], "http": [{"ts": 1672753924545, "value": "400"}]}')

def find(lst, key, value):
    for i, dic in enumerate(lst):
        if dic[key] == value:
            return i
    return -1

with open('data.csv', 'w', newline='') as csvfile:
    fieldnames = ['ts'] + list(data.keys())
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
    writer.writeheader()
    rows = []

    for key in data:
        for row in data[key]:
            found = find(rows, 'ts', row['ts'])
            if found != -1:
                tmp = rows[found]
                tmp[key] = row['value']
                rows[found] = tmp
            else:
                newRow = {}
                newRow['ts'] = row['ts']
                newRow[key] = row['value']
                rows.append(newRow)

    for row in rows:
        writer.writerow(row)

Note that it will also add a new row if there is more than one timestamp for each field.

edited Jan 6, 2023 at 11:44

answered Jan 6, 2023 at 11:38

Drejc

5418 silver badges25 bronze badges

4 Comments

Brell Over a year ago

yeah thank you so much but the same problem that i have they put everything in same row

Drejc Over a year ago

Code puts every column value to a row with timestamp. So if timestamp for each column is the same it will put its value in the same row. Since you do not have different timestamps (each column has the same timestamp in your example) it will put it to the same row.

Brell Over a year ago

yeap i see that's perfect ,but the problem it behave like he fill rows only , i mean there is no separation in keys and values with columns

Drejc Over a year ago

Sure there is! Keys are columns as you say they are, rows are different for each new timestamp. Rows are filled according to the values for each column. There is separation - look again. One thing different is that the code limits values to 2 decimal places. You can change this yourself.

Collectives™ on Stack Overflow

JSON to CSV (python)

3 Answers 3

3 Comments

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related