2

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.

enter image description here

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:

enter image description here

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

Mr.B
  • 51
  • 6

2 Answers2

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
SeaBean
  • 22,547
  • 3
  • 13
  • 25
  • 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']) – Mr.B May 18 '21 at 18:41
  • 1
    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. – SeaBean May 18 '21 at 18:42
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')
Oxford_orange
  • 157
  • 1
  • 10