Python Forum

Dears

I am trying to merge multiple excel files into a single file with Python, but I get an error and I can't fix it.


My Code is

import os
import pandas as pd
cwd = os.path.abspath('') 
files = os.listdir(cwd)  
folder = r"C:\Users\Sameer\Downloads\Sales"

## Method 1 gets the first sheet of a given file
df = pd.DataFrame()
for file in files:
    if file.endswith('.xlsx'):
        df = df.append(pd.read_excel(file), ignore_index=True) 
df.head() 
df.to_excel('total_sales.xlsx')



## Method 2 gets all sheets of a given file
df_total = pd.DataFrame()
for file in files:                         # loop through Excel files
    if file.endswith('.xlsx'):
        excel_file = pd.ExcelFile(file)
        sheets = excel_file.sheet_names
        for sheet in sheets:               # loop through sheets inside an Excel file
            df = excel_file.parse(sheet_name = sheet)
            df_total = df_total.append(df)
df_total.to_excel('combined_file.xlsx')

Error:
C:\Users\Sameer\PycharmProjects\merage\venv\Scripts\python.exe C:\Users\Sameer\PycharmProjects\merage\main.py 
C:\Users\Sameer\PycharmProjects\merage\main.py:11: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.
  df = df.append(pd.read_excel(file), ignore_index=True)
C:\Users\Sameer\PycharmProjects\merage\main.py:11: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.
  df = df.append(pd.read_excel(file), ignore_index=True)
Traceback (most recent call last):
  File "C:\Users\Sameer\PycharmProjects\merage\main.py", line 24, in <module>
    df_total = df_total.concat(df)
               ^^^^^^^^^^^^^^^
  File "C:\Users\Sameer\PycharmProjects\merage\venv\Lib\site-packages\pandas\core\generic.py", line 5902, in __getattr__
    return object.__getattribute__(self, name)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'DataFrame' object has no attribute 'concat'

The error line 24

df_total = df_total.concat(df)

does not match your posted code line 24

df = excel_file.parse(sheet_name = sheet)

neither does the next line

df_total = df_total.append(df)

DataFrame does not have a concat method
Pandas has a concat function

https://pandas.pydata.org/docs/reference...oncat.html Wrote:pandas.concat(objs, *, axis=0, join='outer', ignore_index=False, keys=None, levels=None, names=None, verify_integrity=False, sort=False, copy=True)

Concatenate pandas objects along a particular axis.

Allows optional set logic along the other axes.

Can also add a layer of hierarchical indexing on the concatenation axis, which may be useful if the labels are the same (or overlapping) on the passed axis number.

In your next post you will compose a meaningful topic that describes your question, not "I need help!!!!". I usually ignore posts with meaningless topics like "I need help", "What is wrong", "Python problem". "Error concatenating dataframes" would be a good topic for this thread.

concat is a function in pandas, just like read_excel(). concat() is not a method of DataFrame. You would concat df and df_total like this.

df_total = pd.concat((df_total, df))

Pandas append is depreciated. Do not use it. Instead of this:

cwd = os.path.abspath('') 
files = os.listdir(cwd)

df = pd.DataFrame()
for file in files:
    if file.endswith('.xlsx'):
        df = df.append(pd.read_excel(file), ignore_index=True) 

Use something like this:

import pandas as pd
from pathlib import Path

files = Path('.').glob("*.xlsx")
df = pd.concat((pd.read_excel(file) for file in files))
print(df)

I think pathlib is far superior to using os tools.

(Feb-17-2023, 03:35 PM)Yoriz Wrote: [ -> ]pandas.concat(objs, *, axis=0, join='outer', ignore_index=False, keys=None, levels=None, names=None, verify_integrity=False, sort=False, copy=True)

Thanks to you

Can you, however, write the entire correct code?

I'm still learning the Python programming language.

You won't learn by having others write code for you. I am confident that this is within your abilities. If you cannot make it work after having put in some good effort, come back and post what you have.

Thank you all very much for your assistance.