Pandas Read All Excel Files in Folder

                          
          
Excelython — Part four: Read Excel Files in Python
Project DescriptionYou are working on a purchasing team and you want to check your sales and inventory, the pending orders you might accept, where you need to place new orders and what are the products that need your attention as they are boring movers.
Later on y'all have done all the downloading, the processing and evaluation, you lot need to send to another colleague a file by email, that has all the products, divide by supplier and volume to exist ordered per different sheet and you need a dissever excel file with the products that you accept stock greater than 4 weeks, ordered past the top 20 cost value, in order to create a plan of push their sales or render them back to supplier.
Projection Step 1In society to first, I assume that you lot accept several files, with different information in each of them, that you need to download, open, process and save.
For our projection I am going to utilize the below mock data files:
            Sales File            with units sold Last iv Weeks
                      
File Shape = five columns 10 10.000 rows
2. Current Inventory in warehouse per SKU
                      
File Shape = 2 columns x 10.000 rows
three. Current Inventory in stores and min stock levels per SKU
                      
File Shape = 4 columns 10 100.000 rows
iv. Pending Orders Per SKU
                      
File Shape = 2 columns ten 10.000 rows
5. Cost Per SKU
                      
File Shape = 2 columns x x.000 rows
6. Supplier Per SKU
                      
File Shape = two columns x 10000 rows
Open up them all.
Y'all tin download the .xlsx file with all the sample data            here          
Excel Step 1Download from your BI organisation and open all the above files.
Python Footstep aneAs nosotros said in the          Excelython — Part 2: Install Python Libraries, we need in the offset of each script to import the libraries that we are going to use.
Now is the time to import your first library. The libraries that we are going to import are          Pandas                    and                    Numpy          .        
          import pandas equally pd
import numpy as np        Every bit you notice in the code above, nosotros import the library and we define that from at present and on we volition call it with the shorter names          pd          and          np. This is happening in order to save typing time. You tin can call them equally you want but it is meliorate practice to utilise these global curt names.
The adjacent stride is to import the files. The excel files that we are importing in Python Environment, are imported as dataframes. What are dataframes? The spreadsheet in the excel is a dataframe. Information technology is a data table with columns and rows.
The structure of the code is the following:
Define the          proper noun          of the dataframe =          pd(pandas curt name).read_excel(r"full path to file.xlsx").
          File_1 = pd.read_excel(r"C:\path to folder of the file\file1_name.xlsx")          File_2 = pd.read_excel(r"C:\path to binder of the file\file2_name.xlsx")          File_3 = pd.read_excel(r"C:\path to binder of the file\file3_name.xlsx")          File_4 = pd.read_excel(r"C:\path to binder of the file\file4_name.xlsx")          File_5 = pd.read_excel(r"C:\path to folder of the file\file5_name.xlsx")          File_6 = pd.read_excel(r"C:\path to folder of the file\file6_name.xlsx")        The question you might have at this point, is how in hell practice I detect the file path?
If yous navigate to your file location, right click on the excel file you want to open up, click backdrop, select the tab: Security, select the path adjacent to Object Name, from the beginning until the finish where the file'south extension is, copy all and paste it to your editor.
                      
                      
Permit'due south write the to a higher place code in Spyder, run it and encounter what volition happen.
                      
Select all the lines past highlighting them.
                      
Click the "Run selection or current line" button in the navigation panel.
                      
And voila!!!!!!!
In the right Panels, under the Variable explorer tab, you lot volition see the DataFrame produced from importing File_1.
                      
If you double click in the DataFrame line, you tin can and so see a pop up window with the actual data, that python read.
                      
As you see, nosotros imported a table with Alphabetize, the cavalcade names we had in our excel, and the data values we had in it.
The deviation is that in our excel, SKU is an viii-digit number as text. Python converted it to number.
It is actually easy to catechumen it back to 8-digit, but we volition do it in another part.
As presently equally you place the full code for all the file imports as it is on the code box higher up, you will accept successfully imported the 6 files we demand.
pd.read_excel() is a really powerful function. There are cases were you have an excel that:
Has multiple sheets and you lot demand to import a specific one. There is the parameter sheet_names = "NameofSheet", that can handle this asking.
You need to get specific columns of the excel. The parameter usecols volition help you.
There are also a lot of other special cases you need to work and definitely in that location is a solution out at that place. Until now out of 100 things that I have wondered if there is a style that Python can assist me, might be i–ii things that I couldn't observe a pythonian solution.
In the next footstep nosotros volition come across how we tin can do, excel's Holy Grail.
          VLOOKUP          in Python
Regards
          Go to Part five        