Question 110408 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JSON to CSV (python)

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

Tags:

3

Answers


  1. 0

    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")
    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search