Learn a move! How to use pandas to batch merge excel?

Hello everyone , I'm a rookie ！

Share a use today Pandas Tips for data analysis , There were fans asking questions backstage before , That is, how to pandas.dataframe Save to the same Excel in .

In fact, it only needs to be used flexibly pandas Medium pd.ExcelWriter() The method can !

Suppose now we have df1 df2 df3 Three dataframe, You need to save them to the same Excel Different sheet in , Just create a ExcelWriter object , Then keep writing

df1 = pd.read_csv(' Medal data of Tokyo Olympic Games .csv')
df2 = pd.read_excel("TOP250.xlsx")
df3 = pd.read_excel("2020 The ranking of Chinese universities in .xlsx")
writer = pd.ExcelWriter('test.xlsx')
df1.to_excel(writer,sheet_name="df1",index=False)
df2.to_excel(writer,sheet_name="df2",index=False)
df3.to_excel(writer,sheet_name="df3",index=False)
writer.save()

Is it related to common file reading and writing with The method is similar to , We can use the same method

with pd.ExcelWriter("test1.xlsx") as xlsxwriter:
df1.to_excel(xlsxwriter,sheet_name="df1",index=False)
df2.to_excel(xlsxwriter,sheet_name="df2",index=False)
df3.to_excel(xlsxwriter,sheet_name="df3",index=False)

The result is the same , Multiple df Save to a Excel in

This method is simple and easy to use , But if you want to save  df  That's too much , It is very troublesome to read and save manually one by one , In addition, we hope sheet File name , If you manually copy and paste , More trouble .

At this time , Office automation A series of articles came into play , Let's simply take a small script 「 Get all the files in the specified directory Excel file name 」

import os
def getfile(dirpath):
filelist = []
for root,dirs,files in os.walk(dirpath):
for file in files:
if file.endswith("xlsx") or file.endswith("csv"):
filelist.append(os.path.join(root,file))
return filelist

Execute it. , You can see all under the specified directory Excel file name

What to do next , I don't think I need to say more 「 Cyclic reading , Save automatically 」

filelist = getfile('/Users/liuzaoqi/Desktop/zaoqi/2022 Official account / How to save multiple df')
writer = pd.ExcelWriter('test.xlsx')
for file in filelist:
if file.endswith("xlsx"):
df = pd.read_excel(file)
else:
df = pd.read_csv(file)
df.to_excel(writer,sheet_name=file.split('/')[-1].split('.')[0],index=False)
writer.save()

Now? , All in the current directory Excel Automatically merge into one Excel Different in sheet in , also sheet Name is the corresponding file name

If you are interested in the content of this article , Take the code and try it , If you still have pandas Related issues , Feel free to leave a comment in the comments section .

 Recommended reading :
introduction :  The most complete zero Foundation Python The problem of   |  Zero Basics 8 Months Python  |  Actual project  | learn Python That's the shortcut
dried food : A short comment on crawling Douban , The movie 《 The rest of us 》 | 38 year NBA Best player analysis  |    From people's expectation to public praise ！ Tang Dynasty detective 3 disappointing   |  Laugh at the story of the new Yitian dragon slaying  |  Riddle answer King  ｜ use Python Make a massive sketch of my little sister  ｜ Mission impossible is so hot , I use machine learning to make a mini recommendation system movie
Interest : Pinball game   |  squared paper for practicing calligraphy   |  Beautiful flowers  |  Two hundred lines Python《 Cool run every day 》 game !
AI:  A robot that can write poetry  |  Color the picture  |  Forecast revenue  |  Mission impossible is so hot , I use machine learning to make a mini recommendation system movie
Gadget : Pdf turn Word, Easily handle forms and watermarks ！ |  One touch html Save the page as pdf！|   bye PDF Withdrawal charges ！ |  use 90 Lines of code create the strongest PDF converter ,word、PPT、excel、markdown、html One click conversion  |  Make a nail low-cost ticket reminder ！ ｜60 Line of code to do a voice wallpaper switcher, look at my little sister every day ！｜

Annual hot money copy

• 1). Oh my god ！Pdf turn Word use Python Easy to handle ！

• 2). learn Python It's delicious ！ I use 100 Line of code to make a website , Help people PS Travel pictures , Earn a chicken leg to eat

• 3). Premiere billions , Hot all over the net , I analyzed 《 My sister 》, Discovered the secrets  

• 4).80 Line code ！ use Python Make a dorai A Dream separation  

• 5). What you have to master 20 individual python Code , short , Useful  

• 6).30 individual Python Strange sexual skills Collection  

• 7). I summed up 80 page 《 Rookie Science Python Select dry goods .pdf》, Is dry  

• 8). bye Python！ I have to learn Go 了 ！2500 Word depth analysis ！

• 9). Found a licking dog welfare ！ This Python Reptile artifact is great , Automatically download sister pictures

Click to read the original , see B My station 20 A video ！