DEV Community

Cover image for How to Merge Multiple Data frames with same columns In Pandas | Python
Shrikant Dhayje
Shrikant Dhayje

Posted on • Originally published at codewithshriekdj.netlify.app on

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

#pandas #python #dataframe #merge

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
    ['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']
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 

    [                       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  ]
Enter fullscreen mode Exit fullscreen mode

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

all_dfs[0]
Enter fullscreen mode Exit fullscreen mode
Name of the Establishment Name of the Employer. Nature of Business
0 M/S, RASHAONTHA AIRCONDITIONERS Mr.LOKESH G, Mr.LOKESH G Sales and Service of Airconditioners
1 Micro Plastics Private Limited VIJENDRA BABU N Corporate Office
2 RBL FINSERVE LIMITED VIKAS UPENDRA MUTTOO Business Correspondent
3 RAM ENTERPRISES S MEERA PACKAGING METERIAL TRADING SERVICES
4 S3 GROUP INDIA LIMITED LIABILITY PARTNERSHIP KRISHNAMURTHY M IT SOLUTIONS
5 ADGROWW DEEPAK P R B P O SERVICES
6 LOKADITYA PROPERTIES AND CONSTRUCTIONS HEMANTH K CONSTRUCTIONS
7 JAI AMBE ENTERPRISES MEHERNOSH NOSHIR PATEL SECURITY AND HOUSE KEEPING MANPOWER SERVICES
8 AARTHA THAI SPA ROSE MARY G SPA AND SALOON
9 D N NANDI EXCELLENT SERVICES Dileep Kumara K S House Keeping And Sump Cleaning
10 YULU BIKES PRIVATE LIMITED Naveen Dachuri EV Battery Charging & Swapping
11 MIG CAFE Malleshwar Rao Dhavili RESTAURANT,CAFE
12 VNF IDEAS PRIVATE LIMITED Atul Kumar Selling Fruits, Vegetables and Dairy Products
13 VNF IDEAS PRIVATE LIMITED Atul Kumar Selling Fruits,Vegetables and Dairy products.
14 Prasanna N Rajapurohit Prasanna N Rajapurohit Works Contract for Government work
15 SILVERLINE CLINICS KANAJENAHALLI NARAYANAPPA SATHISH BABU Medical Hospital
16 KALYANI AURA WORKSPACE PVT LTD A MOHAN RAJU Commercial & Office Space Provider

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
Enter fullscreen mode Exit fullscreen mode
Name of the Establishment Name of the Employer. Nature of Business
0 M/S, RASHAONTHA AIRCONDITIONERS Mr.LOKESH G, Mr.LOKESH G Sales and Service of Airconditioners
1 Micro Plastics Private Limited VIJENDRA BABU N Corporate Office
2 RBL FINSERVE LIMITED VIKAS UPENDRA MUTTOO Business Correspondent
3 RAM ENTERPRISES S MEERA PACKAGING METERIAL TRADING SERVICES
4 S3 GROUP INDIA LIMITED LIABILITY PARTNERSHIP KRISHNAMURTHY M IT SOLUTIONS
... ... ... ...
77 DINERO VENTURES GRISHMA RAMANA PRASAD REDDY Consultancy Services
78 GENESIS PHOTOBOOKS A C AKASH Printing and Printing Systems, and Others
79 M/s KNOWSPRING FINANCIAL ADVISORS Monika Prabhakaran PROFESSIONAL SERVICES
80 M/S ICL FINCORP LIMITED SRI. KG ANIL KUMAR GOLD LOAN
81 VATSALA CONSTRUCTIONS AND CONSULTANTS PVT.LTD. H S Vishwanath Construction and Civil works

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.

Top comments (0)

Read next

edvichuki profile image

Comprehensive Weather Data Analysis Using Python: Temperature, Rainfall Trends, and Visualizations

Gichuki Edwin -

qianl15 profile image

How to build a serverless AI chatbot in <100 lines of Python

Qian Li -

alexmercedcoder profile image

Getting Started with Data Analytics Using PyArrow in Python

Alex Merced -

abhinowww profile image

Python vs Java: Comparing Job Markets and Career Growth

Abhinav Anand -