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)