CODEWITHSUNDEEP
pythoncsvdictionary
user6106624
user6106624

Reputation:

Write Nested Dictionary to CSV, Convert Long to Wide Format

Trying to get a nested dictionary (see example below) into a CSV file in a wide data format.

Let's call the dictionary "boats", where to top-level key is the boat ID key.

Example

"123abc": {
    "length": 50,
    "color": "Orange",
    "Weight": 75
},
"456xyz": {
    "length": 35,
    "color": "Green",
    "Weight": 55
}

Current Code

    with open('insertIntoFile.csv', 'w') as fileCSV:
         csvWriter = csv.writer(fileCSV, delimiter=',')
         for all_keys in boats:
              for sub_key in boats[ID]:
                   csvWriter.writerow([ID, sub_key, boats[ID][sub_key]])

The output is

ID
123abc, length, 50
123abc, color, "Orange"
123abc, weight, 75
456xyz, length, 35
456xyz, color, "Green"
456xyz, weight, 55

I'm trying to figure out a way to add another loop in the CSV writing process to get the following, which is my ideal output.

Aspiration

ID, length, color, weight
123abc, 75, "Orange", 50
456xyz, 35, "Green", 35

Thanks.

Upvotes: 1

Views: 236

Answers (2)

RoadRunner
RoadRunner

Reputation: 26325

You can simply write the headers first, then write the concatenated ID key with the dictionary values to the file:

from csv import writer

d = {
    "123abc": {"length": 50, "color": "Orange", "Weight": 75},
    "456xyz": {"length": 35, "color": "Green", "Weight": 55},
}

headers = ["ID", "length", "color", "weight"]

with open("insertIntoFile.csv", mode="w", newline="") as csvfile:
    writer = writer(csvfile)
    writer.writerow(headers)

    for row, data in d.items():
        writer.writerow([row] + list(data.values()))

You can also use csv.DictWriter:

from csv import DictWriter

d = {
    "123abc": {"length": 50, "color": "Orange", "Weight": 75},
    "456xyz": {"length": 35, "color": "Green", "Weight": 55},
}

fieldnames = ["ID", "length", "color", "weight"]

with open("insertIntoFile.csv", mode="w", newline="") as csvfile:
    writer = DictWriter(csvfile, fieldnames=fieldnames)
    writer.writeheader()

    for row, data in d.items():
        writer.writerow(
            {
                "ID": row,
                "length": data["length"],
                "color": data["color"],
                "weight": data["Weight"],
            }
        )

insertIntoFile.csv:

ID,length,color,weight
123abc,50,Orange,75
456xyz,35,Green,55

Upvotes: 0

DYZ
DYZ

Reputation: 57085

You can significantly simplify the task with Pandas. Create a dataframe from the dictionary and save it to the file:

import pandas as pd
df = pd.DataFrame(boats['ID']).T
df.index.name = 'ID' # The default name is "index"
df.reset_index().to_csv('insertIntoFile.csv', index=False)

Upvotes: 1

Related Questions