Diese Frage wurde hier wahrscheinlich mehrmals gestellt. Ich habe versucht, meine verschachtelte JSON-Datei flach zu machen und in CSV zu konvertieren. Am ehesten konnte ich jedoch die Feldnamen auflisten: MyCount, from, Mysize, Allhits, aggs, aber ohne Werte:

Output.csv:

""
Mycount
from
Mysize
Allhits
aggs

Ich habe diesen Code versucht, um JSON in CSV zu konvertieren:

import json
import csv

def get_leaves(item, key=None):
    if isinstance(item, dict):
        leaves = {}
        for i in item.keys():
            leaves.update(get_leaves(item[i], i))
        return leaves
    elif isinstance(item, list):
        leaves = {}
        for i in item:
            leaves.update(get_leaves(i, key))
        return leaves
    else:
        return {key : item}


with open('path/to/my/file.json') as f_input:
    json_data = json.load(f_input)

# Paresing all entries to get the complete fieldname list
fieldnames = set()

for entry in json_data:
    fieldnames.update(get_leaves(entry).keys())

with open('/path/to/myoutput.csv', 'w', newline='') as f_output:
    csv_output = csv.DictWriter(f_output, fieldnames=sorted(fieldnames))
    csv_output.writeheader()
    csv_output.writerows(get_leaves(entry) for entry in json_data)

Die JSON-Struktur sieht folgendermaßen aus:

{"Mycount":538,
"from":0,
"Mysize":1000,
"Allhits":[{
    "isVerified":true,
    "backgroundColor":"FF720B",
    "name":"yourShop",
    "Id":"12345678",
    "ActionItems":[{
        "subtitle":"Click here to start",
        "body":null,
        "language":"de",
        "title":"Start here",
        "isDefault":true}],
        "tintColor":"FFFFFF",
        "shoppingHours":[{"hours":[{"day":["SUNDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]},{"day":["MONDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]},{"day":["SATURDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]},{"day":["FRIDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]},{"day":["THURSDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]},{"day":["WEDNESDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]},{"day":["TUESDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]}]}],
        "LogoUrl":"https://url/to/my/logo.png",
        "coverage":[{
            "country":"*",
            "language":"*",
            "ratio":1}],
        "shoppingHours2":[{"hours":[{"day":["SUNDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]},{"day":["MONDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]},{"day":["SATURDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]},{"day":["FRIDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]},{"day":["THURSDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]},{"day":["WEDNESDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]},{"day":["TUESDAY"],"timeRange":[{"allDay":false,"from":25200,"to":68400}]}]}],
        "group":"shop_open",
        "timeZone":"CET",
        "phone":"+1234567890",
        "modTime":1234567890,
        "intId":"+123456789",
        "Logo2Url":"https://link/to/my/logo.png"}],
"aggs":{}}

Wäre dies mit dem Pandas-Modul einfach zu erreichen? Ich lerne immer noch Python, daher würde ich mich über jede Anleitung freuen. Das Minimum, was ich aus dieser JSON-Datei benötige, ist das Extrahieren von id, intId, name, ratio und Werten für diese Feldnamen in CSV.

Die gewünschte Ausgabe sollte sein (alternativ könnte sie alle Feldnamen und Werte haben und ich könnte dann die benötigten Felder direkt aus CSV extrahieren):

id          intId         name    ratio
12345678    123456789   yourShop    1

Dies ist nur eine Version für einen Datensatz, aber meine Ausgabedatei muss Zeilen für alle in der JSON-Datei vorhandenen IDs enthalten.

Danke im Vorraus für deine Hilfe!

BEARBEITEN Ich habe auch folgendes versucht:

import json
import csv


x = '/path/to/myrecords.json'

x = json.loads(x)

f.writerow(["name", "id", "intId", "ratio"])

f = csv.writer(open("/path/to/my/output.csv", "w", newline=''))

for x in x:
    f.writerow([x["Allhits"]["name"],
                x["Allhits"]["id"],
                x["Allhits"]["ActionItems"]["intId"],
                x["Allhits"]["ActionItems"]["ratio"]])

Hat aber diesen Fehler für x = json.loads(x) Schritt erhalten:

    Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/myusername/anaconda3/lib/python3.6/json/__init__.py", line 354, in loads
    return _default_decoder.decode(s)
  File "/Users/myusername/anaconda3/lib/python3.6/json/decoder.py", line 339, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/myusername/anaconda3/lib/python3.6/json/decoder.py", line 357, in raw_decode
    raise JSONDecodeError("Expecting value", s, err.value) from None
json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)
1
Baobab1988 23 Feb. 2020 im 15:53

3 Antworten

Beste Antwort

Versuche dies. Dadurch wird die Liste Allhits durchlaufen und der erforderliche Mindestdatensatz abgerufen:

import json
import csv

with open('/path/to/myrecords.json') as f_input:
    json_data = json.load(f_input)

with open('/path/to/my/output.csv', 'w', newline='') as f_output:
    csv_output  = csv.writer(f_output)
    csv_output.writerow(["id", "intId", "name", "ratio"])
    for x in json_data['Allhits']:
        csv_output.writerow([x["Id"], x["intId"], x["name"], x["coverage"][0]["ratio"]])
0
jignatius 23 Feb. 2020 im 13:56
x = json.loads(x)

json.decoder.JSONDecodeError: Erwarteter Wert: Zeile 1 Spalte 1 (Zeichen 0)

Dies liegt daran, dass json.loads eine Zeichenfolge erwartet, die die JSON-Daten enthält, während der Dateiname als x übergeben wird.

As "Allhits":[{...}] ist eine einzelne Elementliste, die ein Wörterbuch enthält Ersetzen Sie x["Allhits"]["name"] durch x["Allhits"][0]["name"]. Ähnliches gilt für den Zugriff auf andere Elemente wie "Id".

0
Manoj Mohan 23 Feb. 2020 im 14:08

Wenn Sie den gesamten JSON, einschließlich Arrays, reduzieren müssen, können Sie Folgendes mit einer Wiederholung tun:

import json
import csv

def flatten(item, prefix=None):
    result = {}
    if isinstance(item, list):
        item = { i : item[i] for i in range(0, len(item) )}
    for key, val in item.items():
        prefixed_key = f"{prefix}{key}" if prefix else str(key)
        if isinstance(val, list) or isinstance(val, dict):
            result = {**result, **flatten(val, f"{prefixed_key}_")}
        else:
            result[prefixed_key] = val

    return result


with open('test.json') as f_input, open('result.csv', 'w', newline='') as f_output:
    writer = csv.writer(f_output)
    hits = json.load(f_input)["Allhits"]
    header_written = False
    for hit in hits:
        flat = flatten(hit)
        if not header_written:
            writer.writerow(flat.keys())
            header_written = True
        writer.writerow(flat.values())

Damit bekommst du diese csv monstrocity:

isVerified,backgroundColor,name,Id,ActionItems_0_subtitle,ActionItems_0_body,ActionItems_0_language,ActionItems_0_title,ActionItems_0_isDefault,tintColor,shoppingHours_0_hours_0_day_0,shoppingHours_0_hours_0_timeRange_0_allDay,shoppingHours_0_hours_0_timeRange_0_from,shoppingHours_0_hours_0_timeRange_0_to,shoppingHours_0_hours_1_day_0,shoppingHours_0_hours_1_timeRange_0_allDay,shoppingHours_0_hours_1_timeRange_0_from,shoppingHours_0_hours_1_timeRange_0_to,shoppingHours_0_hours_2_day_0,shoppingHours_0_hours_2_timeRange_0_allDay,shoppingHours_0_hours_2_timeRange_0_from,shoppingHours_0_hours_2_timeRange_0_to,shoppingHours_0_hours_3_day_0,shoppingHours_0_hours_3_timeRange_0_allDay,shoppingHours_0_hours_3_timeRange_0_from,shoppingHours_0_hours_3_timeRange_0_to,shoppingHours_0_hours_4_day_0,shoppingHours_0_hours_4_timeRange_0_allDay,shoppingHours_0_hours_4_timeRange_0_from,shoppingHours_0_hours_4_timeRange_0_to,shoppingHours_0_hours_5_day_0,shoppingHours_0_hours_5_timeRange_0_allDay,shoppingHours_0_hours_5_timeRange_0_from,shoppingHours_0_hours_5_timeRange_0_to,shoppingHours_0_hours_6_day_0,shoppingHours_0_hours_6_timeRange_0_allDay,shoppingHours_0_hours_6_timeRange_0_from,shoppingHours_0_hours_6_timeRange_0_to,LogoUrl,coverage_0_country,coverage_0_language,coverage_0_ratio,shoppingHours2_0_hours_0_day_0,shoppingHours2_0_hours_0_timeRange_0_allDay,shoppingHours2_0_hours_0_timeRange_0_from,shoppingHours2_0_hours_0_timeRange_0_to,shoppingHours2_0_hours_1_day_0,shoppingHours2_0_hours_1_timeRange_0_allDay,shoppingHours2_0_hours_1_timeRange_0_from,shoppingHours2_0_hours_1_timeRange_0_to,shoppingHours2_0_hours_2_day_0,shoppingHours2_0_hours_2_timeRange_0_allDay,shoppingHours2_0_hours_2_timeRange_0_from,shoppingHours2_0_hours_2_timeRange_0_to,shoppingHours2_0_hours_3_day_0,shoppingHours2_0_hours_3_timeRange_0_allDay,shoppingHours2_0_hours_3_timeRange_0_from,shoppingHours2_0_hours_3_timeRange_0_to,shoppingHours2_0_hours_4_day_0,shoppingHours2_0_hours_4_timeRange_0_allDay,shoppingHours2_0_hours_4_timeRange_0_from,shoppingHours2_0_hours_4_timeRange_0_to,shoppingHours2_0_hours_5_day_0,shoppingHours2_0_hours_5_timeRange_0_allDay,shoppingHours2_0_hours_5_timeRange_0_from,shoppingHours2_0_hours_5_timeRange_0_to,shoppingHours2_0_hours_6_day_0,shoppingHours2_0_hours_6_timeRange_0_allDay,shoppingHours2_0_hours_6_timeRange_0_from,shoppingHours2_0_hours_6_timeRange_0_to,group,timeZone,phone,modTime,intId,Logo2Url
True,FF720B,yourShop,12345678,Click here to start,,de,Start here,True,FFFFFF,SUNDAY,False,25200,68400,MONDAY,False,25200,68400,SATURDAY,False,25200,68400,FRIDAY,False,25200,68400,THURSDAY,False,25200,68400,WEDNESDAY,False,25200,68400,TUESDAY,False,25200,68400,https://url/to/my/logo.png,*,*,1,SUNDAY,False,25200,68400,MONDAY,False,25200,68400,SATURDAY,False,25200,68400,FRIDAY,False,25200,68400,THURSDAY,False,25200,68400,WEDNESDAY,False,25200,68400,TUESDAY,False,25200,68400,shop_open,CET,+1234567890,1234567890,+123456789,https://link/to/my/logo.png

Wenn Sie jedoch nur bestimmte Schlüssel benötigen, können Sie einfach Ihre Allhits durchlaufen und abrufen, was wie folgt benötigt wird:

with open('test.json') as f_input, open('result.csv', 'w', newline='') as f_output:
    writer = csv.writer(f_output)
    hits = json.load(f_input)["Allhits"]
    writer.writerow(["Id", "intId", "name", "ratio"])
    for hit in hits:
        writer.writerow([hit["Id"], hit["intId"], hit["name"], hit["coverage"][0]["ratio"]])
0
Olvin Roght 23 Feb. 2020 im 13:59