Import Multiple Excel Files, Create A Column And Get Values From Excel File's Name

I need to upload multiple excel files - each one has a name of starting date. Eg. '20190114'. Then I need to append them in one DataFrame. For this, I use the following code: all_d

Solution 1:

IIUC, you can filter necessary rows ,then concat, for file name you can use os.path.split() and access the filename with string slicing:

l=[]
for f in glob.glob('C:\\path\\*.xlsx'):
    df=pd.read_excel(f)
    df['from']=os.path.split(f)[1][:-5]
    l.append(df[(df['code'].eq('r')&df['price'].ne(0))])
pd.concat(l,ignore_index=True)

---

     id  price code      from
0  id_2   12.5    r  20190101
1  id_3   17.5    r  20190101
2  id_5    7.5    r  20190101
3  id_1    7.5    r  20190115
4  id_2   24.5    r  20190115
5  id_5    7.5    r  20190115