deduplicate records in multiple CSV files with varying columns
<p>I have multiple CSV files in a directory. Some contain more columns (which would be OK to drop).</p>

<p>Is there an elegant way to deduplicate records between these CSV files and reduce columns to a common set of columns?</p>

<p>Currently, I will use python / pandas to accomplish this. I will load all the files into a single data frame, note in an additional column where the records originated from (filename), drop the additional columns and finally have pandas deduplicate via <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.duplicated.html" rel="nofollow">http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.duplicated.html</a>
In the last step, I write the deduplicated files back to disk based on the filename-identifier column.</p>

<pre><code># ASSUMPTION: files are in order, first file defines minimum common columns
path = '.'
files_in_dir = [f for f in os.listdir(path)if f.endswith('csv')] 
isFirst = True

for filenames in fs.find('*.csv', path):
    df = pd.read_csv(filenames, error_bad_lines=False)
    df['origin'] = fs.add_suffix(filenames, '_deduplicated')
    if (isFirst):
        isFirst = False
        bigDf = df
    else:
        bigDf = pd.concat(bigDf, df, axis=0, join='inner')
cols_for_dup = [col for col in bigDf.columns if col not in ['origin']]
bigDf.duplicated(subset=cols_for_dup).sum()
bigDf.duplicated().sum()
bigDf_withoutNA = bigDf.drop_duplicates(keep='first', subset= cols_for_dup)

grouped = bigDf_withoutNA.groupby('origin')
for name, group in grouped:
    #filename = 'path' + name
    group.to_csv(path_or_buf= name, sep=';', decimal=',')
</code></pre>

<p>Is there a simpler approach to this?</p>

<p>I do not known how to make it simpler. I have an equal script done for some data of mine. It just runs twice, first to determine the min / max cols in all documents and finally to rewrite the csv files in an new folder, to keep the original data.</p>

<p>I am just using the csv lib from python.
<a href="https://docs.python.org/2/library/csv.html" rel="nofollow">https://docs.python.org/2/library/csv.html</a></p>

<p>There are no checks in this script, as it's just a quick and dirty script.</p>

<p>The deduplication is not done. It just cuts all data to the same length, but you can replace the last line with your deduplication code.</p>

<pre><code>import os
import csv

mincols = 0xffffffff
maxcols = 0

srcdir = '/tmp/csv/'
dstdir = '/tmp/csv2/'

for dirName, subdirList, fileList in os.walk(srcdir):
    for fname in fileList:
        if fname[-4:].lower() == '.csv':
            with open(os.path.join(dirName, fname)) as csvfile:
                reader = csv.reader(csvfile, delimiter=',', quotechar='"')
                for row in reader:
                    if mincols &gt; len(row):
                        mincols = len(row)
                    if maxcols &lt; len(row):
                        maxcols = len(row)

print(mincols, maxcols)

for dirName, subdirList, fileList in os.walk(srcdir):
    for fname in fileList:
        if fname[-4:].lower() == '.csv':
            fullpath = os.path.join(dirName, fname)    
            newfile = os.path.join(dstdir, fullpath[len(srcdir):])

            if not os.path.exists(os.path.dirname(newfile)):
                os.makedirs(os.path.dirname(newfile))
            with open(fullpath) as csvfile:
                reader = csv.reader(csvfile, delimiter=',', quotechar='"')
                with open(newfile, 'w') as dstfile:
                    writer = csv.writer(dstfile, delimiter=',', quotechar='"',
                        quoting=csv.QUOTE_MINIMAL)
                    for row in reader:
                        #You can deduplicate here 
                        writer.writerow(row[:mincols])                  
</code></pre>

python csv pandas duplicates