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?

Answer 1

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.

Answer 2

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')