Alternate Between Relative and Absolute Path in Same Loop

Question

I am trying to:

1. Loop through a directory of CSV files
2. Append the file name as a new column to each file
3. Concatenate every file into single master file

But I get stuck at step #3, when converting the absolute path back into a relative path because my output looks like ../../../../Desktop/2018.12.31.csv when I just want it to be 2018.12.31.

For example, say the directory contains two files: 2018.12.31.csv and 2018.11.30.csv.

2018.12.31.csv

A B
1 2

2018.11.30.csv

A B
3 4

After running my program:

import os
import pandas as pd

folder = ('/Users/user/Desktop/copy')
files = os.listdir(folder)
file_list = list()

for file in files:

    file = os.path.join(folder, file)
    if file.endswith('.csv'):
        df = pd.read_csv(file, sep=";")
        df['filename'] = os.path.relpath(file)
        file_list.append(df)

all_days = pd.concat(file_list, axis=0, ignore_index=True, sort=False)
all_days.to_csv("/Users/user/Desktop/copy/all.csv")

I want the output to be:

A B filename
1 2 2018.12.31
3 4 2018.11.30

But instead it's:

A B filename
1 2 ../../../../Desktop/copy/2018.12.31.csv
3 4 ../../../../Desktop/copy/2018.11.30.csv

Answer 1

os.path.relpath returns the file location relative to your current directory. You can get the original filename using os.path.basename(path), or just keep the filename as a separate variable and set df['filename'] = file_orig.