How to Merge Multiple Data frames with same columns In Pandas | Python
Tags: pandas, python, dataframe, merge
Published: 2023-01-11 14:19:06

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

```python
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

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

```python
    ['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

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

```plaintext

    [                       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.

```python
all_dfs[0]
```

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

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

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