Merge two CSV files Keeping duplicates
<p>I am trying to merge two csv files and keep duplicate records.  Each file may have a matching record, one file may have duplicate records (same student ID with different test scores), and one file may not have a matching student record in the other file.  The code below works as expected however if a duplicate record exists only the second record is being written to the merged file.  I've looked through many threads and all address removing duplicates but I need to keep duplicate records.</p>

<pre><code>import csv
from collections import OrderedDict
import os

cd = os.path.dirname(os.path.abspath(__file__))

fafile = os.path.join(cd, 'MJB_FAScores.csv')
testscores = os.path.join(cd, 'MJB_TestScores.csv')

filenames = fafile, testscores
data = OrderedDict()
fieldnames = []
for filename in filenames:
    with open(filename, 'r') as fp:
        reader = csv.DictReader(fp)
        fieldnames.extend(reader.fieldnames)
        for row in reader:
            data.setdefault(row['Student_Number'], {}).update(row)
fieldnames = list(OrderedDict.fromkeys(fieldnames))
with open('merged.csv', 'w', newline='') as fp:
    writer = csv.writer(fp)
    writer.writerow(fieldnames)
    for row in data.values():
        writer.writerow([row.get(fields, '') for fields in fieldnames])
</code></pre>

<hr>

<p>fafile:</p>

<pre class="lang-none prettyprint-override"><code>Student_Number  Name    Grade   Teacher FA1 FA2 FA3
65731   Ball, Peggy 4   Bauman, Edyn    56  45  98
65731   Ball, Peggy 4   Bauman, Edyn    32  323 232
85250   Ball, Jonathan  3   Clarke, Mary    65  77  45
981235  Ball, David 5   Longo, Noel 56  89  23
91851   Ball, Jeff  0   Delaney, Mary   83  45  42
543 MAX 2   Phil    77  77  77
543 MAX 2   Annie   88  888 88
9844    Lisa    1   Smith, Jennifer 43  44  55
</code></pre>

<p>testscores:</p>

<pre class="lang-none prettyprint-override"><code>Student_Number  Name    Grade   Teacher MAP Reading MAP Math    FP Level    DSA LN  DSA WW  DSA SJ  DSA DC
65731   Ball, Peggy 4   Bauman, Edyn    175 221 A   54  23  78  99
72941   Ball, Amanda    4   Bauman, Edyn    201 235 J   65  34  65
85250   Ball, Jonathan  3   Clarke, Mary    189 201 L   34  54
981235  Ball, David 5   Longo, Noel 225 231 D   23  55
91851   Ball, Jeff  0   Delaney, Mary   198 175 C
65731   Ball, Peggy 4   Bauman, Edyn    200 76  Y   54  23  78  99
543 MAX 2   Phil    111 111 Z   33  44  55  66
543 MAX 2   Annie   222 222 A   44  55  66  77
</code></pre>

<p>Current output:</p>

<pre class="lang-none prettyprint-override"><code>Student_Number  Name    Grade   Teacher FA1 FA2 FA3 MAP Reading MAP Math    FP Level    DSA LN  DSA WW  DSA SJ  DSA DC
65731   Ball, Peggy 4   Bauman, Edyn    32  323 232 200 76  Y   54  23  78  99
85250   Ball, Jonathan  3   Clarke, Mary    65  77  45  189 201 L   34  54
981235  Ball, David 5   Longo, Noel 56  89  23  225 231 D   23  55
91851   Ball, Jeff  0   Delaney, Mary   83  45  42  198 175 C
543 MAX 2   Annie   88  888 88  222 222 A   44  55  66  77
72941   Ball, Amanda    4   Bauman, Edyn                201 235 J   65  34  65
9844    Lisa    1   Smith, Jennifer 43  44  55
</code></pre>

<p>Desired output:</p>

<pre class="lang-none prettyprint-override"><code>Student_Number  Name    Grade   Teacher FA1 FA2 FA3 MAP Reading MAP Math    FP Level    DSA LN  DSA WW  DSA SJ  DSA DC
65731   Ball, Peggy 4   Bauman, Edyn    32  323 232 200 76  Y   54  23  78  99
65731   Ball, Peggy 4   Bauman, Edyn    56  45  98  175 221 A   54  23  78  99
85250   Ball, Jonathan  3   Clarke, Mary    65  77  45  189 201 L   34  54
981235  Ball, David 5   Longo, Noel 56  89  23  225 231 D   23  55
91851   Ball, Jeff  0   Delaney, Mary   83  45  42  198 175 C
543 MAX 2   Annie   88  888 88  222 222 A   44  55  66  77
543 MAX 2   Phil    77  77  77  111 111 Z   33  44  55  66
72941   Ball, Amanda    4   Bauman, Edyn                201 235 J   65  34  65
9844    Lisa    1   Smith, Jennifer 43  44  55
</code></pre>

<p>You're using an <code>OrderedDict</code> to store student IDs (keys) and their record (values) from both files. </p>

<p>This has a subtle implication: new <em>student ID</em> entries will replace the old duplicate <em>student ID</em>  dict entries from the previous file, because dict keys are unique.</p>

<p>You may consider writing the entries to the <em>merge</em> file immediately after they are read from one file. After which the <code>dict</code> is cleared and the next file is used to <em>repopulate</em> <code>dict</code>. This will help avoid <em>collision</em> and <em>replacement</em> of studentIDs.</p>

python python-3.x csv merge