formatting data into a csv table in python

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)

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