Df.to_excel('fsl16days')

Python
#1

Hi ,
Am a beginner in python , you could consider this as my 1st project.

So at work we consolidate excel sheets a lot , and as a temp i would be tasked to it always!
Copying and pasting over and over again got to me think if it can be automated with programming . Looks like there is , but i wished to do it with python since i am learning it.

so i tried and got till here searching the web.

#Program to combine excel sheets from same excel workbook.
import pandas pas pd
import numpy as np
excel_file_1 = ‘workbook1.xlsx’
df_Day_one = pd.read_excel(excel_file_1, sheet_name=‘Sheet1’)
df_Day_2 = pd.read_excel(excel_file_1, sheet_name=‘Sheet2’)
df_all = pd.concat([df_Day_one,df_Day_2])
df_all.to_excel(“two_days_output.xlsx”)

This is working and am happy and proud about it even tho it took me 3 days to get this code working… had problems with the location… of the excel sheet .

But sadly i need to do it for 30 days so yes 30 sheets typing ""sheet_name = “” for every sheet, it just beats the whole objective of “less work”.

So searched a bit more and found that “” pd.read_excel(excel_file,sheet_name=None) “” would read all the data ? so i tried it like this and…

import pandas as pd
import numpy as np
excel_file=‘FSL Dump for August.xlsx’
df_alldays = pd.read_excel(excel_file,sheet_name=None)
df_alldays.to_excel(‘16_days_output.xlsx’)

This is the error am getting in shell

File “<pyshell#50>”, line 1, in
df_alldays.to_excel(“16_days_output.xlsx”)
AttributeError: ‘dict’ object has no attribute ‘to_excel’

And this is the part where am stuck at , tried searching the web and still havent got to anything . Need help with this .

Have a good day :sunny:

#2

Did you get answer for the same…I am facing similar issue

#3

Hi,

I strongly recommend using print() function to understand what the code is actually doing.

If you look like that;

#Program to combine excel sheets from same excel workbook.
import pandas pas pd
import numpy as np
excel_file_1 = ‘workbook1.xlsx’
df_Day_one = pd.read_excel(excel_file_1, sheet_name=‘Sheet1’)
print(df_Day_one)
print(type(df_Day_one))
df_Day_2 = pd.read_excel(excel_file_1, sheet_name=‘Sheet2’)
df_all = pd.concat([df_Day_one,df_Day_2])
df_all.to_excel(“two_days_output.xlsx”)

you will see that df_Day_one is a pandas DataFrame via print() functions. If you want to get a Sheet from Excel workbook, it comes as a DataFrame. You can use to_excel method with DataFrames. Code works without problem.

However, at the second code block;

import pandas as pd
import numpy as np
excel_file=‘FSL Dump for August.xlsx’
df_alldays = pd.read_excel(excel_file,sheet_name=None)
print(df_Day_one)
print(type(df_Day_one))
df_alldays.to_excel(‘16_days_output.xlsx’)

you will see that df_alldays is not a DataFrame, it is a dictionary. Their keys are Sheet names as String and values are DataFrame which contains data at the corresponded Sheet. You had tried to use to_excel method with a dictionary but you cannot.

You can write a for loop to concatenate them all Sheets;

import pandas as pd
import numpy as np

excel_file="data.xlsx"

df_alldays = pd.read_excel(excel_file, sheet_name=None)

print(type(df_alldays)) # you can see it is a dictionary
print(df_alldays) 

sheet_number = 0
for sheet in list(df_alldays.keys()):
    if sheet_number == 0:
        df_all = df_alldays[sheet]
    else:
        df_all = pd.concat([df_all, df_alldays[sheet]])
    sheet_number += 1

df_all.to_excel("output.xlsx", index = False) # use index = False to get rid of unnecessary indexes

It is just a way. You can find another ways to do it too, it will be similar though.