Writing an excel from a dictionary of tuples in python

Question

I have a list of tuples like below

mylist = {'file1': {'N': '96', 'P': '70', 'E': '109', 'T': '10'}, 'file2': {'N': '6', 'P': '90', 'E': '1309', 'T': '100'}', 'file3': {'N': '966', 'P': '370', 'E': '1409', 'T': '50'}}

If I extract it as a dataframe

df= pd.DataFrame.from_dict(mylist, orient='index')

it works fine. but when i try to write this df into an excel

df_cpu.to_excel (writer, "MY", index=True)

My excel does not have the file column.

Your dictionary has an error '100'}' though if you're just looking for the column to be named in the excel file as it's the index it won't have a label by default To give it one you can do: df_cpu.to_excel (writer, "MY", index_label='file') — RBowen
– RBowen, Commented May 3, 2020 at 21:35

Jhanvi Patel · Accepted Answer · 2020-05-03 23:54:25Z

1

My Solution

First, install openpyxl

1.open cmd.exe
2.write the following command:

cd C:\Users\username\AppData\Local\Programs\Python\Python37-32\Scripts
pip install openpyxl

Python code

mylist = {'file1': {'N': '96', 'P': '70', 'E': '109', 'T': '10'}, 'file2': {'N': '6', 'P': '90', 'E': '1309', 'T': '100'},'file3': {'N': '966', 'P': '370', 'E': '1409', 'T': '50'}}
df= pd.DataFrame.from_dict(mylist, orient='index')
df.to_excel(r"C:\Users\Any\Desktop\output.xlsx",sheet_name='Sheet_name')

output

answered May 3, 2020 at 23:54

Jhanvi Patel

2391 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rexovas · Accepted Answer · 2020-05-03 21:50:15Z

0

If you have openpyxl installed (can be installed with pip install openpyxl), the following should work.

mylist = {
    "file1": {"N": "96", "P": "70", "E": "109", "T": "10"},
    "file2": {"N": "6", "P": "90", "E": "1309", "T": "100"},
    "file3": {"N": "966", "P": "370", "E": "1409", "T": "50"},
}

df = pd.DataFrame.from_dict(mylist, orient="index")
output = "OUTPUT.xlsx"
sheet_name = "MY"
df.to_excel(output, sheet_name, index=True)

The resulting excel file doesn't have a header for the "file" column. If you wanted to add one, you could instead do

df.to_excel(output, sheet_name, index=True, index_label="file") # Replace "file" with whatever header you prefer)

Alternatively, because the resulting file is quite simple, you could choose to simply use df.to_csv instead

df.to_csv(output)

answered May 3, 2020 at 21:50

Rexovas

4782 silver badges9 bronze badges

1 Comment

kapa3 Over a year ago

Thanks much. Adding the index_label works perfectly. df_cpu.to_excel (writer, "MY", index=True, index_label="filename")

Collectives™ on Stack Overflow

Writing an excel from a dictionary of tuples in python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related