使用Python合并/追加多个Excel

z9smfwbn  于 2023-05-01  发布在  Python
关注(0)|答案(1)|浏览(165)

我是新的python,我试图合并/追加多个excel与相同的表结构,我采取了所有的excel工作表标签命名为“数据”,只有特定的列。
但是,我得到“TypeError:append()为参数'ignore_index'获取了多个值“。寻求建议如何解决这个问题与我的代码下面?
预期结果:我只是想合并/追加所有的Excel文件从名为“数据”的选项卡,并与特定的列成一个主Excel。

import pandas as pd
    import sys
    sys.path.insert(0, 'S:\\19 Performance Analytics\ETL Data Extraction\\PyCr')
    
    # MYOS Resort Q1
    df = pd.DataFrame()
    xls = pd.ExcelFile(r"C:\Users\jiacheng.heng\OneDrive - Shangri-La Group\NOVA\Arrival Check Tracker\CT2 Arrival Check Report\2023\2023 - Q1 - MYSLOS RESORT Arrival Checks.xlsx")
    columns = ['Report Pull Out Date (DD-MMM-YY)','Property','Confirmation No.','Guest Name','Arrival','Departure','Rate Code','Source'
               ,'Arr. Transfer Type','Dep. Transfer Type','Status','Agent Initial','Remark','Completion Date','Completion Month','Received Month'
               ,'Arrival Month','Tasks Status','AHT','Pending Aging','SLA']
    df=df.append(xls.parse('Data',usecols= columns))
    
    # MYOS Resort Q2
    combdf = pd.DataFrame()
    xls1 = pd.ExcelFile(r"C:\Users\jiacheng.heng\OneDrive - Shangri-La Group\NOVA\Arrival Check Tracker\CT2 Arrival Check Report\2023\2023 - Q2 - MYSLOS RESORT Arrival Checks.xlsx")
    columns = ['Report Pull Out Date (DD-MMM-YY)','Property','Confirmation No.','Guest Name','Arrival','Departure','Rate Code','Source'
               ,'Arr. Transfer Type','Dep. Transfer Type','Status','Agent Initial','Remark','Completion Date','Completion Month','Received Month'
               ,'Arrival Month','Tasks Status','AHT','Pending Aging','SLA']
    combdf=combdf.append(xls1.parse('Data',usecols= columns))
    
    # MYOS City Q1 & Q2
    combdf2 = pd.DataFrame()
    xls2 = pd.ExcelFile(r"C:\Users\jiacheng.heng\OneDrive - Shangri-La Group\NOVA\Arrival Check Tracker\CT2 Arrival Check Report\2023\2023 - Q1 & Q2 - MYSLOS CITY Arrival Checks.xlsx")
    columns = ['Report Pull Out Date (DD-MMM-YY)','Property','Confirmation No.','Guest Name','Arrival','Departure','Rate Code','Source'
               ,'Arr. Transfer Type','Dep. Transfer Type','Status','Agent Initial','Remark','Completion Date','Completion Month','Received Month'
               ,'Arrival Month','Tasks Status','AHT','Pending Aging','SLA']
    combdf2=combdf2.append(xls2.parse('Data',usecols= columns))
    
    # HKOS Q1 & Q2
    combdf3 = pd.DataFrame()
    xls3 = pd.ExcelFile(r"C:\Users\jiacheng.heng\OneDrive - Shangri-La Group\NOVA\Arrival Check Tracker\CT1 Arrival Check Report\2023\2023 - Q1 & Q2 - HKOS Arrival Checks.xlsx")
    columns = ['Report Pull Out Date (DD-MMM-YY)','Property','Confirmation No.','Guest Name','Arrival','Departure','Rate Code','Source'
               ,'Arr. Transfer Type','Dep. Transfer Type','Status','Agent Initial','Remark','Completion Date','Completion Month','Received Month'
               ,'Arrival Month','Tasks Status','AHT','Pending Aging','SLA']
    combdf3=combdf3.append(xls3.parse('Data',usecols= columns))
    
    # HKOS Q3 & Q4
    combdf4 = pd.DataFrame()
    xls4 = pd.ExcelFile(r"C:\Users\jiacheng.heng\OneDrive - Shangri-La Group\NOVA\Arrival Check Tracker\CT1 Arrival Check Report\2023\2023 - Q3 & Q4 - HKOS Arrival Checks.xlsx")
    columns = ['Report Pull Out Date (DD-MMM-YY)','Property','Confirmation No.','Guest Name','Arrival','Departure','Rate Code','Source'
               ,'Arr. Transfer Type','Dep. Transfer Type','Status','Agent Initial','Remark','Completion Date','Completion Month','Received Month'
               ,'Arrival Month','Tasks Status','AHT','Pending Aging','SLA']
    combdf4=combdf4.append(xls4.parse('Data',usecols= columns))
    
    combdf=combdf.append(df,combdf2,combdf3,combdf4, ignore_index=True)
    combdf = combdf.iloc[: , :-1]
    
    
    # Import the data into excel file
    combdf.to_excel(r"S:\19 Performance Analytics\NOVA\Appended Arrival Checks.xlsx", index=0)
7uzetpgm

7uzetpgm1#

使用read_excel方法,您可以读取excel文件。您将获得单独创建的 Dataframe 。以xls和xls1为例,在使用concat方法之后,每一个都将是一个 Dataframe 。你将能够在阅读后加入 Dataframe ,最后将所有内容转换为一个文件,你使用to_excel,就像你做的那样。这将大大减少你的代码。你会更容易理解。

相关问题