Write dictionary to csv or xlsx python

Question

I have a dictionary called master that has values in the format of.

{
  'Company': {
             'App': 4,  
             'App2': 5,  
             'App3': 82,  
          etc}   
  'Company2': {
             'App': 3,  
             'App2': 1,  
             'App3': 48,  
          etc}   
  'Company3' etc

I tried a few different answers:
with open('test.csv', 'wb') as f: writer = csv.writer(f) for row in myDict.iteritems(): writer.writerow(row)
other questions related to this but none gave the output I was looking for. Need the output in either a csv or xslx to be formatted as
Companys as the column headers starting in Column B going across and the Apps Going down the rows in Column A starting in Row 2 with the count for each app filling in the rows under the columns for each associated Company.
Only seem to get outputs that are not separating into columns correctly or do not get the count included correctly.

master = {} business_list = ['Company1','Company2','Company3'etc] for business in business_list: selected_business_apps = {} curr_business_apps = business_map.get(business) freqs = Counter (curr_business_apps) for key, val in freqs.items(): if key in app_map.keys(): selected_business_apps[key] = val master[business] = selected_business_apps

Possible duplicate of [Write dictionary values in an excel file](https://stackoverflow.com/questions/23113231/write-dictionary-values-in-an-excel-file) — Invisible Hippo, Feb 14 '18 at 16:18
Edit the question to include the Python code you have written so far. — Martin Evans, Feb 14 '18 at 16:30
@J.Yonathan I tried something like that as well but it did not work, this is what I tried. Also I have around 3 thousand entires in my dictionary so I didnt do one of the lines in the page you linked `workbook = xlsxwriter.Workbook('file.xlsx') worksheet = workbook.add_worksheet() row = 0 col = 0 for key in master.keys(): row += 1 worksheet.write(row, col, key) for item in master[key]: worksheet.write(row, col + 1, item) row += 1 workbook.close()` — DancinDeku, Feb 14 '18 at 17:04

score 0 · Accepted Answer · answered Feb 14 '18 at 16:55

0

This would be easiest to make use of Python built in csv and defaultdict features. It works by first creating a new dictionary of dictionaries in terms of the apps, rather than the companies. It then uses a csv.DictWriter() which takes a dictionary and converts it into a CSV formatted output row:

from collections import defaultdict
import csv

master = {
    'Company': {'App': 4, 'App2': 5, 'App3': 82},
    'Company2': {'App': 3, 'App2': 1, 'App3': 48}}

all_apps = defaultdict(dict)

# Create a dictionary in terms of apps e.g. {'App' : {'Company' : 4, 'Company2' : 3}}
for company, apps in master.items():
    for app, count in apps.items():
        all_apps[app][company] = count

with open('output.csv', 'wb') as f_output:
    csv_output = csv.DictWriter(f_output, fieldnames=['App'] + sorted(master.keys()))
    csv_output.writeheader()

    for app, row in sorted(all_apps.items()):
        row.update({'App': app})
        csv_output.writerow(row)

This would give you an output.csv file containing:

App,Company,Company2
App,4,3
App2,5,1
App3,82,48

answered Feb 14 '18 at 16:55

Martin Evans

45,791
17
81
97

Does it matter that I have around 3000 apps for each company and around 100 companies so typing them all out as above is not a desired step? – DancinDeku Feb 14 '18 at 17:08
You said you already had `master` ? Apart from that, no extra typing is required. It should work regardless how many apps/companies you have. If you like you could post a link to your test file using something like [pastebin](https://pastebin.com/) – Martin Evans Feb 14 '18 at 17:14
Yeah I do sorry new to all of this but I will put the last bit of code that gives me my master dict in my OP as I cannot share my test file. – DancinDeku Feb 14 '18 at 17:43
Do you get an error when you remove my `master` example and replace it with yours? – Martin Evans Feb 14 '18 at 17:47
You could try `business_list = business_map.keys()` to create that list automatically? – Martin Evans Feb 14 '18 at 17:51
That may be what I need to do as when I did try it I did get a type error: int object not iterable but I am having to rerun my workflow as something I did emptied my dictionary and it takes around an hour to run through as it is iterating through 38 million rows of data to create it. – DancinDeku Feb 14 '18 at 17:54
I think so yes. – Martin Evans Feb 14 '18 at 17:55
Yes that was it; thank you and sorry it too so long for me to understand everything completely – DancinDeku Feb 14 '18 at 19:10

Write dictionary to csv or xlsx python

1 Answers1