[FIXED] formatting data into a csv table in python

Issue

I am trying to turn a list containing dictionaries into a csv table. I am using pandas however I cant find a way to get my desired result. the data is formatted in the following way:

[[{0:"title1"},{1:"title2"}],[{0:"data1"},{1:"data2"}],[{1:"more data2"}]]

• the outer list is supposed to represent the entire table
• each inner list is supposed to represent a column in the table
• and each dictionary is supposed to represent a row in each column, with each key representing the row number
• in addition not every column has the same number of rows, that's why the row numbers are important.

would appreciate it if someone could help! thank you

I tried using pandas data frame but it completely ignored the dictionary keys and turned the lists into rows rather than columns

Solution

I would use a comprehension to reshape your table/data into a list to dictionaries where each dictionary represented a row in the table. At that point, a csv.DictWriter() or a pandas.DataFrame().to_csv() will be able to process it into a CSV file.

import json # only needed for display of reshaped table

table = [
    [{0:"title1"}, {1:"title2"}],
    [{0:"data1"}, {1:"data2"}],
    [{1:"more data2"}]
]

## ------------------
## re-shape the table into a list of dictionaries (rows)
## ------------------
table = [
    {key: value for col in row for key, value in col.items()}
    for row in table
]
## ------------------

## ------------------
## Take a peek at what table looks like now
## ------------------
print(json.dumps(table, indent=4))
## ------------------

That should show you:

[
    {
        "0": "title1",
        "1": "title2"
    },
    {
        "0": "data1",
        "1": "data2"
    },
    {
        "1": "more data2"
    }
]

Now we can more easily write out our CSV.

import csv

with open("out.csv", "w", newline="") as file_out:
    writer = csv.DictWriter(file_out, fieldnames=[0,1], extrasaction="ignore")
    writer.writeheader()
    writer.writerows(table)

or

import pandas

pandas.DataFrame(table).to_csv("out.csv", index=False)

Either should produce a file like:

0,1
title1,title2
data1,data2
,more data2

Answered By - JonSG