Not able to add extra column to the list in the python

[shantanu97]

I have two excel files, and I wanted to append those files into one. But I also want to add a work book name of excel file into a column "SourceFile".

df = []
for fn in xlsx_files:
    df.append(pd.read_excel(fn,sheet_name=0))
    new_column ={"SourceFile":Path(fn.name)}            
df = pd.concat([df,new_column], axis=1,ignore_index=True) 
df.to_csv(os.path.join(path_save, f"combined.csv"), index=False)

Error: df = pd.concat([df,new_column], axis=1,ignore_index=True) TypeError: cannot concatenate object of type '<class 'list'>'; only Series and DataFrame objs are valid

File Attached Here

Can anyone please let me know why I am not able to add extra column with workbook name in python???? Or is there any other way we can add that column with workbook name into that???

[paul18fr]

The following code works for me

import pandas as pd
import os

PATH=str(os.path.abspath(''))

FilesList=['VY6_Row1_report_url.xlsx', 'VY6_Row2_report_url.xlsx']
NumberOfFiles=len(FilesList)

# first file
df=pd.read_excel(PATH + '/' + FilesList[0], sheet_name=0)

# other files if more than one
for i in range(1, NumberOfFiles):
    df=pd.concat([df, pd.read_excel(PATH + '/' + FilesList[i], sheet_name=0)], axis = 1)

# export to csv
df.to_csv(PATH + "/combined.csv", index=False)

[snippsat]

Can anyone please let me know why I am not able to add extra column with workbook name in python???? Or is there any other way we can add that column with workbook name into that???

I would combine excel first then add a new column.
Example.

import pandas as pd
pd.set_option('display.expand_frame_repr', False)
from pathlib import Path

dest = r'G:\div_code\answer\exfiles'
all_data = pd.DataFrame()
for path in Path(dest).rglob('*.xlsx'):
    if path.is_file():
        df_comb = pd.read_excel(path)
        df = all_data.append(df_comb, ignore_index=True)

G:\div_code\answer\exfiles
λ ptpython -i two_excel.py
>>> df.head()
   frameNo.                gpsTimestamp  videoTimeInSec   latitude   longitude  metreID   Distance  vineNumber  canesCount  nodeCount  shootCount                                             S3Link


0       250  2021-08-31 02:16:17.833235        4.170833 -37.279718  140.858070        0   0.000000           1           2         10           0  https://greenview-1004.s3.amazonaws.com/Penley...


1       287  2021-08-31 02:16:18.415585        4.788117 -37.279736  140.858067        2   2.014529           2           2         19           1  https://greenview-1004.s3.amazonaws.com/Penley...


2       338  2021-08-31 02:16:19.315000        5.638967 -37.279764  140.858065        5   5.216640           3           1         14           0  https://greenview-1004.s3.amazonaws.com/Penley...


3       382  2021-08-31 02:16:20.046175        6.373033 -37.279789  140.858061        7   7.961367           4           2         19           0  https://greenview-1004.s3.amazonaws.com/Penley...


4       442  2021-08-31 02:16:21.036175        7.374033 -37.279826  140.858056       12  12.077279           5           2         13           0  https://greenview-1004.s3.amazonaws.com/Penley...

# Insert a new column eg after frameNo. 
>>> df.insert(1, "SourceFile", list(range(16)), True)
>>> df.head()
   frameNo.  SourceFile                gpsTimestamp  videoTimeInSec   latitude   longitude  metreID   Distance  vineNumber  canesCount  nodeCount  shootCount                                             S3Link


0       250           0  2021-08-31 02:16:17.833235        4.170833 -37.279718  140.858070        0   0.000000           1           2         10           0  https://greenview-1004.s3.amazonaws.com/Penley...


1       287           1  2021-08-31 02:16:18.415585        4.788117 -37.279736  140.858067        2   2.014529           2           2         19           1  https://greenview-1004.s3.amazonaws.com/Penley...


2       338           2  2021-08-31 02:16:19.315000        5.638967 -37.279764  140.858065        5   5.216640           3           1         14           0  https://greenview-1004.s3.amazonaws.com/Penley...


3       382           3  2021-08-31 02:16:20.046175        6.373033 -37.279789  140.858061        7   7.961367           4           2         19           0  https://greenview-1004.s3.amazonaws.com/Penley...


4       442           4  2021-08-31 02:16:21.036175        7.374033 -37.279826  140.858056       12  12.077279           5           2         13           0  https://greenview-1004.s3.amazonaws.com/Penley...