Create CSV output with an uneven dictionary of single values and lists-CodePudding

I am using python and running my example on google colab. I am new to csv writing. I want to csv write out like the following. desired output example The issue I am having is with my lists in 'add' and 'reg'. When I run my example code I get the following. my output What should I do to get my desired output? Below is my attempt. If there is another post that has a similar situation as mine could you link it as I have already tried searching and could not find someone with the the issue as mine.

d = [{'local_date': '12/16/2022', 'local_time': '12:00', 'add':[2, 8, 22, 17], 'reg':[1001, 1002, 1003, 1004] }]
fields = ['local_date', 'local_time', 'add', 'reg']

with open("tester.csv", "w") as outfile:
  writer = csv.DictWriter(outfile, fieldnames = fields)
  writer.writeheader()
  writer.writerows(d)

CodePudding user response：

using csv

import csv

d = [{'local_date': '12/16/2022', 'local_time': '12:00', 'add':[2, 8, 22, 17], 'reg':[1001, 1002, 1003, 1004]}]

fields = ['local_date', 'local_time', 'add', 'reg']

# we parse the d list to get the create new row with each add, reg value
data = [{**dat, 'add':add, 'reg':reg} for dat in d for add, reg in zip(dat['add'], dat['reg'])]

with open("tester.csv", "w") as outfile:
  writer = csv.DictWriter(outfile, fieldnames = fields)
  writer.writeheader()
  writer.writerows(data)

using pandas

import pandas as pd

d = [{'local_date': '12/16/2022', 'local_time': '12:00', 'add':[2, 8, 22, 17], 'reg':[1001, 1002, 1003, 1004]}]

df = pd.DataFrame(d).explode(['add', 'reg'])

df.to_csv('tester.csv', index=False)

CodePudding user response：

One of the problems is that you're combining lists with other types of objects.

You could just put the other objects as lists, also if keys are in the dictionary, variable 'fields' becomes unnecessary.

Here is my try:

d = {'local_date': ['12/16/2022','12/16/2022','12/16/2022','12/16/2022'], 'local_time': ['12:00','12:00','12:00','12:00'], 'add':[2, 8, 22, 17], 'reg':[1001, 1002, 1003, 1004] }

with open("tester.csv", "w") as outfile:
  writer = csv.writer(outfile)
  writer.writerow(d.keys())
  writer.writerows(zip(*d.values()))

Also, if you don't have a limit on libraries, try pandas (it will make your life easier).

More info about lists in CSV: Write dictionary of lists to a CSV file