Read A File Name And Create A Column With It

i have a filename called shoes_2016, how do I create a dataframe column called year and extract out the year according to the filename.

Solution 1:

Extract year with split, convert to integer and assign to DataFrame:

filename = 'shoes_2016.csv'

df = pd.read_csv(filename)
df['year'] = int(filename.split('.')[0].split('_')[1])

print (df)

Or:

filename = 'shoes_2016.csv'

df = pd.read_csv(filename).assign(year = int(filename.split('.')[0].split('_')[1]))
print (df)

EDIT:

import os

filename = 'c:/users/a/desktop/items/shoes_2016.xlsx'
y = int(os.path.basename(filename).split('.')[0].split('_')[1])
print (y)
2016

Or:

y = int(filename.split('/')[-1].split('.')[0].split('_')[1])
print (y)
2016

df = pd.read_csv(filename).assign(year = y)