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

CodePudding user response：

If you are 100% sure, every "json input" will have the same structure, you can skip csv module, create 1 csv file with 1th row as header and use this method:

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('data2.csv', 'a') as csvfile:
    row = str()
    for key in data.keys():
        cell = data[key][0]["value"]

        # uncomment this line for visual control
        # print(f'"{cell}";', end="")

        row  = f'"{cell}";'


    csvfile.write(row)
    csvfile.write("\n")

 

CodePudding user response：

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)

CodePudding user response：

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.