How to write the data to an excel using python

Question

Im writing the data inside my dictionary to an excel which looks like below

my_dict = { 'one': 100, 'two': 200, 'three': 300}

df = pd.DataFrame(my_dict.items(), columns=['Summary','Count'])

with pd.ExcelWriter('outfile.xlsx') as writer:
    df.to_excel(writer, sheet_name='sheet1', index=False)

for the above code im getting the desired output like below.

I have one more list which have some values which needs to be pasted in the 3rd column of the excel.

my_list = [10,20,30]

expected output:

Edit: I need to add the data in my_dict and the my_list at the same time.

I have tried finding out a solution unfortunately couldn't able to. Any help is appreciated! Many thanks!!

Answer 1

To add the data in my_dict and the my_list at the same time to define the dataframe df, you can chain the pd.DataFrame() call with .assign() to define the column named my_list using the input list my_list as input:

df = pd.DataFrame(my_dict.items(), columns=['Summary','Count']).assign(my_list=my_list)

Of course, the most trivial way of doing that is to separate them into 2 statements, defining the dataframe by pd.DataFrame first and then add column, as below. But this will be in 2 statement and not sure whether you still count it as "at the same time".

df = pd.DataFrame(my_dict.items(), columns=['Summary','Count'])  # Your existing statement unchanged

df['my_list'] = my_list

Result:

print(df)


  Summary  Count  my_list
0     one    100       10
1     two    200       20
2   three    300       30

Answer 2

This may also solve your problem

import pandas as pd
my_dict = {'summary': ['one', 'two', 'three'],  'count': [100, 200, 300]}
my_list = [10,20,30]
df = pd.DataFrame.from_dict(my_dict)
df['my_list'] = my_list
df.to_excel('df.xlsx')