Add new column year and extract given file name (pandas , glob, os)

user_12192476 0
Viewed 569

I will like to read multiple file but when I added a new column year, error message given: list object has no attribute 'Split'. I want to be the same this. Reference: I would like to have the same as this one

File Name: Shirt_2016, Shirt_2017, Shoe_2018, Shoe_2019,

All file names are standardized with this format, item_year.

import os
import glob
import pandas
import numpy

path='/Item/'
files = glob.glob(os.path.join(path,'*.xlsx'))
data = []
for filename in files:
   df = pd.read_excel(filename,header=None)
   data.append(df)
df=pd.concat(data)

df['Year']= os.path.basename(files.split('.')[0],split['_'][1]))
pandas glob operating-system python
2 Answers

As the AttributeError suggests, the variable files that you're calling split() on is a list, not a string.

Instead, try setting the year on each dataframe when you instantiate it:

for filename in files:
    df = pd.read_excel(filename, header=None)
    # Extract the year from the filename & save it
    # on your dataframe before you concatenate the data.
    year = filename.split('.')[0],split['_'][1]
    df['Year'] = year
    data.append(df)
user_65137360

Carolyn solution will work, but here is an alternative using pandas function str.extract: df['year']=df['file'].str.extract('_(\d{4})',expand=True)

Here is the rest of the code to show how it works: 

files = [f"/Item/{f}.xlsx" for f in "Shirt_2016, Shirt_2017, Shoe_2018, Shoe_2019".split(', ')]
df = pd.DataFrame(files,columns=['file']) 

df['year']=df['file'].str.extract('_(\d{4})',expand=True)
df

enter image description here

By the way, when reading multiple files it is faster to create a list and then concat.

df = pd.concat([pd.read_excel(f).assign(file=f) for f in files])

The assign is wasteful, but reads clean. Even faster is to wrap this read into a function and then call parallel read: joblib.Parallel(n_jobs=-1)(joblib.delayed(read)(f) for f in files).

where def read(f): df = pd.read_excel(f); df['file']=f; return df

user_57523140
Related