0

I have 20 excels file which name is

'apple1.xlsx' 'apple2.xlsx' 'apple3.xlsx' . . . 'orange1.xlsx' 'orange2.xlsx' 'orange3.xlsx'

I dont know how to loop the data to combine every excel file into 1 file and I also want to have index name in final excel file (which is apple1, apple2, apple3, ...,orange1, orange2, orange3)

Help me please. thank you so much everyone

Paper.J
  • 13
  • 1
  • 5
  • I might be able to come up with an answer, just let me know these things 1. how many sheets are present in each xlsx file ? 2. are all sheets named the same ? 3. what is the nature of the data in each xlsx file? 4. Show me what ever code you have done so far. – PSR Jan 21 '22 at 06:37
  • Also refer to the answer here, https://stackoverflow.com/questions/15793349/how-to-concatenate-three-excels-files-xlsx-using-python – PSR Jan 21 '22 at 06:39
  • 1. only 1 sheet per file – Paper.J Jan 21 '22 at 06:40
  • 2. every sheet and excel files are the same. 3. data in each file , some columns are text and some columns are float – Paper.J Jan 21 '22 at 06:40
  • please refer the link i have shared above – PSR Jan 21 '22 at 06:41
  • i have tried this city = ["Apple", "Banana", "Orange"] dfs = [] for city in cities : for x in range(1,26) : dfs = [pd.read_excel(str(city)+str(x)+'.xlsx' for city in cities] dfs = pd.concat(dfs, keys=stocks, names=['Apple1', 'Apple2',...,'Orange1','Orange2','Orange3']) #to get new index column name – Paper.J Jan 21 '22 at 06:43
  • Does it work?, what output do you get ? – PSR Jan 21 '22 at 06:47
  • Try the other method also, xlrd and xlwt ... which is mentioned in another answer at the same above mentioned link – PSR Jan 21 '22 at 06:48
  • got one problem is i can't add index name. after combine all excel file i still want to have new column as an index name for me . (need each excel file's name to be and index name) – Paper.J Jan 21 '22 at 06:51
  • By index name you mean column name in the combined xl ? – PSR Jan 21 '22 at 06:55

1 Answers1

0

It can be done with a library pandas

step 1: install pandas

pip install pandas

step2: put all your files you want to merge into a folder . and get its path in my case path is "E:\test\\" you should replace with yours

step 3: run the below code to get your merged excel file

import glob
import pandas as pd
path=r"E:\test\\" #replace with your path
files = glob.glob(path+ "/*.xlsx") #getting all the file path
merged = pd.concat(map(pd.read_excel,files),axis=1) #reading and combining them into one file
merged.to_excel("merged.xlsx") #saving the result

step 4 (optional): customization,

There are several way you can merge those file, please refer pandas documentation. for example axis=1 refers append by column, axis=0 refers append by row (while appending by rows you may need to set ignore_index=True)

ah bon
  • 9,293
  • 12
  • 65
  • 148
Vignesh
  • 63
  • 6