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!!

SeaBean · Accepted Answer · 2021-05-18 18:48:22Z

3

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

edited May 18, 2021 at 18:48

answered May 18, 2021 at 18:36

SeaBean

23.4k3 gold badges16 silver badges28 bronze badges

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

2 Comments

Mr.B Over a year ago

Thanks for your answer, Just to ensure im on a right way to explore we can do like this also ryt? df['my_list'] = my_list df = pd.DataFrame(my_dict.items(), columns=['Summary','Count'])

SeaBean Over a year ago

You have to put df['my_list'] = my_list after the call to pd.DataFrame(). That's the most trivial way but in 2 statements. As I seen you said at the same time, therefore I devised solution for you in one statement.

Oxford_orange · Accepted Answer · 2021-05-18 18:41:55Z

1

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

answered May 18, 2021 at 18:41

Oxford_orange

1571 silver badge10 bronze badges

Collectives™ on Stack Overflow

How to write the data to an excel using python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related