I'm trying to write a dictionary with randomly generated strings and numbers to an excel file. I've almost succeeded but with one minor problem. The structure of my dictionary is as follows:
Age: 11, Names Count: 3, Names: nizbh,xyovj,clier
This dictionary was generated from data obtained through a text file. It aggregates all the contents based on their age and if two people have the same age, it groups them into one list. I'm trying to write this data on to an excel file. I've written this piece of code so far.
import xlsxwriter
lines = []
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()
with open ("input2.txt") as input_fd:
lines = input_fd.readlines()
age_and_names = {}
for line in lines:
name,age = line.strip().split(",")
if not age in age_and_names:
age_and_names[age]=[]
age_and_names[age].append(name)
print age_and_names
for key in sorted(age_and_names):
print "Age: {}, Names Count: {}, Names: {}".format(key, len(age_and_names[key]), ",".join(age_and_names[key]))
row=0
col=0
for key in sorted(age_and_names):#.keys():
row += 1
worksheet.write(row, col, key)
for item in age_and_names[key]:
worksheet.write(row, col+1, len(age_and_names[key]))
worksheet.write(row, col+1, item)
row+=1
workbook.close()
But what this is actually doing is this (in the excel file):
11 nizbh
xyovj
clier
What should I do to make it appear like this instead?
Age Name Count Names
11 3 nizbh, xyovj, clier
The problem was indeed in the two for loops there. I meddled and played around with them until I arrived at the answer. They're working fine. Thank you guys!
Replace the for loops in the end with this:
for key in sorted(age_and_names):#.keys():
row+=1
worksheet.write(row, col, key)
worksheet.write(row, col+1, len(age_and_names[key]))
worksheet.write(row, col+2, ",".join(age_and_names[key]))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With