Python - Concatenate CSV files in a specific directory

I am trying to concatenate CSV files from a folder in my desktop:

C:\\Users\\Vincentc\\Desktop\\W1 

and output the final CSV to:

C:\\Users\\Vincentc\\Desktop\\W2\\conca.csv

The CSV files don't have header. However, nothing come out when I run my script, and no error message. I'm a beginner, can someone have a look at my code below, Thanks a lot!

import os
import glob
import pandas

def concatenate(indir="C:\\Users\\Vincentc\\Desktop\\W1",outfile="C:\\Users\\Vincentc\\Desktop\\W2\\conca.csv"):
    os.chdir(indir)
    fileList=glob.glob("indir")
    dfList=[]
    for filename in fileList:
        print(filename)
        df=pandas.read_csv(filename,header=None)
        dfList.append(df)
    concaDf=pandas.concat(dfList,axis=0)
    concaDf.to_csv(outfile,index=None)

2 answers

  • answered 2018-05-16 11:47 Martin Evans

    glob.glob() needs a wildcard to match all the files in the folder you have given. Without it, you might just get the folder name returned, and none of the files inside it. Try the following:

    import os
    import glob
    import pandas
    
    def concatenate(indir=r"C:\Users\Vincentc\Desktop\W1\*", outfile=r"C:\Users\Vincentc\Desktop\W2\conca.csv"):
        os.chdir(indir)
        fileList = glob.glob(indir)
        dfList = []
    
        for filename in fileList:
            print(filename)
            df = pandas.read_csv(filename, header=None)
            dfList.append(df)
    
        concaDf = pandas.concat(dfList, axis=0)
        concaDf.to_csv(outfile, index=None)
    

    Also you can avoid the need for adding \\ by either using / or by prefixing the strings with r. This has the effect of disabling the backslash escaping on the string.

  • answered 2018-05-16 13:52 jpp

    Loading csv files into pandas only for concatenation purposes is inefficient. See this answer for a more direct alternative.

    If you insist on using pandas, the 3rd party library dask provides an intuitive interface:

    import dask.dataframe as dd
    
    df = dd.read_csv('*.csv')  # read all csv files in directory lazily
    df.compute().to_csv('out.csv', index=False)  # convert to pandas and save as csv