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]
| 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
| 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)