How to Merge Multiple Data frames with same columns In Pandas | Python

Hello Everybody, So Today I just faced this issue of merging multiple excels in one and there size was too big for merging like more 100 MB for Size and In Some Cases the size spans more and if we tried to search any online tool they mostly have size limit for merging the excel files with same columns.

I Know there is pd.merge function but I like simple.

So I Just Decided to merge my file as given below.

So My Code Is in Jupyter Notebook and I am Pasting it's Markdown version as given below.

Firstly we will need to Import pandas and glob package

pandas for dataframe and glob for getting the list of all files in whatever folder that we want

import pandas as pd
from glob import glob

Get List file all file names in a variable named list_of_all_files like given below

list_of_all_files = glob(r'B:\dev\test_folder\*.xlsx')
list_of_all_files

    ['B:\\dev\\test_folder\\test.xlsx',
     'B:\\dev\\test_folder\\test2.xlsx',
     'B:\\dev\\test_folder\\test3.xlsx',
     'B:\\dev\\test_folder\\test4.xlsx',
     'B:\\dev\\test_folder\\test5.xlsx']

Read Each and Every file and get list of Pandas Dataframes

all_dfs = [pd.read_excel(file, 'Sheet1') for file in list_of_all_files]
all_dfs

    [                       Name of the Establishment  \
     0                M/S, RASHAONTHA AIRCONDITIONERS   
     1                 Micro Plastics Private Limited   
     2                           RBL FINSERVE LIMITED   
     3                                RAM ENTERPRISES   
     4   S3 GROUP INDIA LIMITED LIABILITY PARTNERSHIP   
     5                                        ADGROWW   
     6         LOKADITYA PROPERTIES AND CONSTRUCTIONS   
     7                           JAI AMBE ENTERPRISES   
     8                                AARTHA THAI SPA   
     9                   D N NANDI EXCELLENT SERVICES   
     10                    YULU BIKES PRIVATE LIMITED   
     11                                      MIG CAFE   
     12                     VNF IDEAS PRIVATE LIMITED   
     13                     VNF IDEAS PRIVATE LIMITED   
     14                        Prasanna N Rajapurohit   
     15                            SILVERLINE CLINICS   
     16                KALYANI AURA WORKSPACE PVT LTD   

                          Name of the Employer.  \
     0                 Mr.LOKESH G, Mr.LOKESH G   
     1                          VIJENDRA BABU N   
     2                     VIKAS UPENDRA MUTTOO   
     3                                  S MEERA   
     4                          KRISHNAMURTHY M   
     5                               DEEPAK P R   
     6                                HEMANTH K   
     7                   MEHERNOSH NOSHIR PATEL   
     8                              ROSE MARY G   
     9                        Dileep Kumara K S   
     10                          Naveen Dachuri   
     11                  Malleshwar Rao Dhavili   
     12                              Atul Kumar   
     13                              Atul Kumar   
     14                  Prasanna N Rajapurohit   
     15  KANAJENAHALLI NARAYANAPPA SATHISH BABU   
     16                            A MOHAN RAJU   

     ...  

                                        Nature of Business  
     0   Coaching Centre-Student counselling for overse...  
     1                                        SUPER MARKET  
     2   Technical and Professional Skill Development a...  
     3                               INTERIOR AND WOODWORK  
     4       Learning and Activity Centre For Special Kids  
     5           Family Office and Trust Planning Services  
     6   Handloom Cloth, Mill Goods, Hosieries, Dress M...  
     7   Web Development Web Designing Services And Pro...  
     8          Security/House keeping and Allied Services  
     9                                Software Development  
     10                 Mangoes and Mango products Trading  
     11                                    RESTAURANT,CAFE  
     12      Selling Fruits, Vegetables and Dairy Products  
     13      Selling Fruits,Vegetables and Dairy products.  
     14                 Works Contract for Government work  
     15                                   Medical Hospital  
     16                 Commercial & Office Space Provider  ,
                              Name of the Establishment  \
     0                                KLAYWORKZ BARISTA   
     1                      DWIJA FOODS PRIVATE LIMITED   
     2   M/s INNATERA NANOSYSTEMS INDIA PRIVATE LIMITED   
     3                                  PARISHIKA GROUP   
     4          FOURDEGREEWATER CAPITAL PRIVATE LIMITED   
     5                                       RAYSUN LED   
     6             FORETHOUGHT VENTURES PRIVATE LIMITED   
     7        REVENUESTACK TECHNOLOGIES PRIVATE LIMITED   
     8            CODELINEAR SOFTWARE SOLUTIONS PVT LTD   
     9                             M/S VAISHNAVI INCORP   
     10                                 DINERO VENTURES   
     11                              GENESIS PHOTOBOOKS   
     12               M/s KNOWSPRING FINANCIAL ADVISORS   
     13                         M/S ICL FINCORP LIMITED   
     14  VATSALA CONSTRUCTIONS AND CONSULTANTS PVT.LTD.   

                                     Name of the Employer.  \
     0                                              SAMPAT   
     1   JAYATI DIBAKAR CHATTERJEE, DIBAKAR SATYABRATA ...   
     2                                    Sri S Uma Mahesh   
     3                           Chandan Nagaraj, Jeevitha   
     4   Anshul Gupta, Ajinkya Mukund Kulkarni, Abhik J...   
     5                                           PRAKASH M   
     6    DOMMASANDRA MALLIKARJUNA GOWRI SHANKAR, NACHAPPA   
     7                          YESHU SINGH, SANDEEP SINGH   
     8                       Syed Muheeb, Muheeb Syed Saif   
     9                               Mr M K Harshith Kumar   
     10                        GRISHMA RAMANA PRASAD REDDY   
     11                                          A C AKASH   
     12                                 Monika Prabhakaran   
     13                                 SRI. KG ANIL KUMAR   
     14                                     H S Vishwanath   

                                        Nature of Business  
     0                                   Hotel/ Restaurant  
     1                           Beverage Vending Services  
     2                           IT Design and Development  
     3                                  Bar and Restaurant  
     4   Commercial Establishment engaged in business o...  
     5           LED LIGHTS MANUFACTURING TRADING SERVICES  
     6                               HOTEL AND RESTAURANTS  
     7   all related services and consultancy services ...  
     8   Designing, Developing, Maintaining Website and...  
     9   Manpower Consultancy,Staffing, Security, House...  
     10                               Consultancy Services  
     11          Printing and Printing Systems, and Others  
     12                              PROFESSIONAL SERVICES  
     13                                          GOLD LOAN  
     14                       Construction and Civil works  ]

They are not visible like table because they are not merged given below is visualization of first data frame.

all_dfs[0]

Now we are gonna merge all data frames in one, like given below

Be Aware that at given below line of code the ignore_index=True is mandatory because the index column can be duplicate for each file and may overwrite instead of merging all df into one.

single_merged_df = pd.concat(all_dfs, ignore_index=True)
single_merged_df

82 rows × 3 columns

As you can see like above code you can merge multiple data frame into one. and now just export your data frame in whatever format you want like csv, xlsx or anything else.