CODEWITHSUNDEEP
pythonjsoncsv
Brell
Brell

Reputation: 35

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

Upvotes: 1

Views: 439

Answers (3)

Marcel Suleiman
Marcel Suleiman

Reputation: 134

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")

Upvotes: 1

Drejc
Drejc

Reputation: 531

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.

Upvotes: 1

HarryKane
HarryKane

Reputation: 168

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)

Upvotes: 1

Related Questions