How do I output a list of dictionaries to an Excel sheet?

Question

I have a list called 'players' that consists of dictionaries. It looks like this:

players = [{'dailyWinners': 3, 'dailyFreePlayed': 2, 'user': 'Player1', 'bank': 0.06},
{'dailyWinners': 3, 'dailyFreePlayed': 2, 'user': 'Player2', 'bank': 4.0},
{'dailyWinners': 1, 'dailyFree': 2, 'user': 'Player3', 'bank': 3.1},
{'dailyWinners': 3, 'dailyFree': 2, 'user': 'Player4', 'bank': 0.32}]

It's much longer, but this is an excerpt. How do I output this list of dictionaries to an Excel file so it's neatly organized by key/value?

What is exactly the problem? Travesing the data structures or generating Excel files? — C2H5OH, Feb 01 '13 at 00:46
Excel can import csv files. Lookup `csv` module. Esp. `DictWriter`. — zch, Feb 01 '13 at 00:49
Excel can import data in `csv` format and Python just happens to have a [`csv` package](http://docs.python.org/2/library/csv.html) with a `DictWriter` that writes dictionaries to `csv`. — , Feb 01 '13 at 00:53
@zch Just beat me to it. I'm leaving my comment for the link. — , Feb 01 '13 at 00:53
Previous similar Q: http://stackoverflow.com/questions/14391768/excel-vba-macro-using-itunes-search-api-fastest-way-to-query-parse-json-resu/14393532#comment20343383_14393532 — Tim Williams, Feb 01 '13 at 01:04

score 21 · Answer 1 · edited Oct 01 '21 at 07:32

Solution using Pandas

import pandas as pd

players = [{'dailyWinners': 3, 'dailyFreePlayed': 2, 'user': 'Player1', 'bank': 0.06},
{'dailyWinners': 3, 'dailyFreePlayed': 2, 'user': 'Player2', 'bank': 4.0},
{'dailyWinners': 1, 'dailyFreePlayed': 2, 'user': 'Player3', 'bank': 3.1},
{'dailyWinners': 3, 'dailyFreePlayed': 2, 'user': 'Player4', 'bank': 0.32}]

df = pd.DataFrame.from_dict(players)

print (df)

df.to_excel('players.xlsx')

score 18 · Answer 2 · edited Sep 08 '21 at 11:03

There is a way to write a list of dictionary to an Excel worksheet. First of all, be sure you have XlsxWriter package.

from xlsxwriter import Workbook
players = [{'dailyWinners': 3, 'dailyFree': 2, 'user': 'Player1', 'bank': 0.06},
{'dailyWinners': 3, 'dailyFree': 2, 'user': 'Player2', 'bank': 4.0},
{'dailyWinners': 1, 'dailyFree': 2, 'user': 'Player3', 'bank': 3.1},
{'dailyWinners': 3, 'dailyFree': 2, 'user': 'Player4', 'bank': 0.32}]

ordered_list=["user", "dailyWinners", "dailyFree", "bank"] # List object calls by index, but the dict object calls items randomly

wb=Workbook("New File.xlsx")
ws=wb.add_worksheet("New Sheet") # Or leave it blank. The default name is "Sheet 1"

first_row=0
for header in ordered_list:
    col=ordered_list.index(header) # We are keeping order.
    ws.write(first_row,col,header) # We have written first row which is the header of worksheet also.

row=1
for player in players:
    for _key,_value in player.items():
        col=ordered_list.index(_key)
        ws.write(row,col,_value)
    row+=1 #enter the next row
wb.close()

I tried the code, and it worked successfully.

score 4 · Answer 3 · answered Apr 23 '20 at 17:35

The xlsxwriter library is great for creating .xlsx files (originally mentioned by Fatih1923).

The following snippet generates an .xlsx file from a list of dicts while stating the order and the displayed names:

import xlsxwriter

# ...

def create_xlsx_file(file_path: str, headers: dict, items: list):
    with xlsxwriter.Workbook(file_path) as workbook:
        worksheet = workbook.add_worksheet()
        worksheet.write_row(row=0, col=0, data=headers.values())
        header_keys = list(headers.keys())
        for index, item in enumerate(items):
            row = map(lambda field_id: item.get(field_id, ''), header_keys)
            worksheet.write_row(row=index + 1, col=0, data=row)

Usage

headers = {
    'bank': 'Money in Bank',
    'dailyWinners': 'Daily Winners',
    'dailyFree': 'Daily Free',
    'user': 'User',
}

players = [
    {'dailyWinners': 3, 'dailyFreePlayed': 2, 'user': 'Player1', 'bank': 0.06},
    {'dailyWinners': 3, 'dailyFreePlayed': 2, 'user': 'Player2', 'bank': 4.0},
    {'dailyWinners': 1, 'dailyFree': 2, 'user': 'Player3', 'bank': 3.1},
    {'dailyWinners': 3, 'dailyFree': 2, 'user': 'Player4', 'bank': 0.32}
]

create_xlsx_file("my xslx file.xlsx", headers, players)

Note - The headers dict represent both the order and the displayed name. If you're not using Python3.6+, use OrderedDict in headers, since the order in dict is not preserved

score 2 · Accepted Answer · 2013-02-01T01:12:33.937

2

test.py

from csv import DictWriter

players = [{'dailyWinners': 3, 'dailyFreePlayed': 2, 'user': 'Player1', 'bank': 0.06},
{'dailyWinners': 3, 'dailyFreePlayed': 2, 'user': 'Player2', 'bank': 4.0},
{'dailyWinners': 1, 'dailyFree': 2, 'user': 'Player3', 'bank': 3.1},            
{'dailyWinners': 3, 'dailyFree': 2, 'user': 'Player4', 'bank': 0.32}]

with open('spreadsheet.csv','w') as outfile:
    writer = DictWriter(outfile, ('dailyWinners','dailyFreePlayed','dailyFree','user','bank'))
    writer.writeheader()
    writer.writerows(players)

Run python test.py

Then open the resulting spreadsheet.csv file in Excel.

NOTE: I'm running Linux so I wasn't able to test this using Microsoft Excel. This works in LibreOffice Calc and gives a spreadsheet where the keys are the column names and the values are under their appropriate columns.

edited Feb 01 '13 at 01:12

answered Feb 01 '13 at 01:06

hey, thanks so much. I ran this though and a csv. file didn't open. Is it saved somewhere? – Alex Klinghoffer Feb 01 '13 at 01:10
@AlexKlinghoffer There was a typo in `writeheader`. I'd try copying the fixed version over and running it again... the `spreadsheet.csv` should be generated wherever you ran the script from. – Feb 01 '13 at 01:14
Hey Mike, I really appreciate your help. I tried copying again, but when I run it, it just says: Exit status: 0 logout [Process completed] Any other suggestions? – Alex Klinghoffer Feb 01 '13 at 01:23
I'm assuming you're doing this in Windows? Exit status 0 should mean it completed successfully. Is there a spreadsheet.csv file in the directory you just ran test.py in? – Feb 01 '13 at 01:25
I'm running it in the terminal on my mac. – Alex Klinghoffer Feb 01 '13 at 01:27
@AlexKlinghoffer The file will be generated in whatever directory your terminal is open to. To find out if the file was generated do `ls` after you run python and look for a file with the name `spreadsheet.csv`. To find out which directory it's in run the command `pwd`. – Feb 01 '13 at 02:26
@AlexKlinghoffer I'm glad it helped you. If this answer satisfies your question please select it as the correct answer (click the check mark) so that it can be closed out. – Feb 02 '13 at 14:36

How do I output a list of dictionaries to an Excel sheet?

4 Answers4

Linked