Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I write a python dictionary to an excel file?

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
like image 280
DeA Avatar asked Oct 18 '22 03:10

DeA


1 Answers

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]))
like image 93
DeA Avatar answered Oct 31 '22 19:10

DeA