Pandas - Combine info from multiple text files to single dataframe

Question

I have multiple text (.txt) files saved in a folder. I'm trying to combine them all into a single dataframe. So far I have been able to combine them, but not in the manner I'd like.

The text files (named yob####.txt where #### is a year) have information that looks like this:

Jennifer,F,58376
Amanda,F,35818
Jessica,F,33923
Melissa,F,31634
Sarah,F,25755
Heather,F,19975
Nicole,F,19917
Amy,F,19834
Elizabeth,F,19529
Michelle,F,19122
Kimberly,F,18499
Angela,F,17970

I'm trying to open each file, add the year to the end of the row, and move on.

def main():
    files = file_paths(FILE_FOLDER) # returns a list of file paths, i.e. ["C:\Images\file.txt","C:\Images\file2.txt", ...]

    df = []
    for file in files:
        year = file.split("\\")[-1][3:7] 
        df.append(pd.read_table(file)+","+year)
    big_df = pd.concat(df, ignore_index=True, axis=1)
    big_df.to_csv("Combined.csv", header=False, index=False)

This almost works...except it takes each file and puts the data in a column, the next file in a second column, next file in a third, etc.

Current output:

The expected output is the same, except when it opens the 1881 file, it adds the info to the end of 1880. Then 1882 goes after the 1881 data, etc. etc.

Answer 1

1. With read_table, the default separator is assumed to be whitespace (sep='\t'). Change read_table to read_csv, which infers your separator. Alternatively, specify sep=',' for the same effect.
2. You're trying to add a new column year, but you're not doing that correctly. You can use assign to add it in
3. Concatenate vertically (axis=0, the default), not horizontally.

df_list = []
for file in files:
    year = ...
    df_list.append(pd.read_csv(file, header=None).assign(year=year))

big_df = pd.concat(df_list, ignore_index=True)
big_df.to_csv("Combined.csv", header=False, index=False)

Answer 2

You can use pd.DataFrame.assign to add a column seamlessly while you iterate.

Note also that it is good practice to use os.path.basename instead of splitting by specific characters: this will ensure your code will work on multiple platforms.

Updated: Add header=None and use pd.read_csv, as discussed on other answer.

dfs = []
for file in files:
    year = os.path.basename(fn)[3:7]
    dfs.append(pd.read_csv(file, header=None).assign(Year=year))

df = pd.concat(dfs, ignore_index=True, axis=1)

A more efficient way is to use a list comprehension:

dfs = [pd.read_csv(file, header=None).assign(Year=os.path.basename(fn)[3:7]) \
       for file in files]

df = pd.concat(dfs, ignore_index=True, axis=1)