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>For your inner loop you can do the following:</p>

<pre><code>for row in reader:
    new_record = dict(row)
    records = data.setdefault(row['Student_Number'], [])
    for record in records:
        new_record.update(record) # preserve old values
        record = copy.copy(new_record) 
    new_record.update(dict(row)) # to preserve original values
    records.append(new_record)
</code></pre>

<p>When writing you have to go one step deeper now:</p>

<pre><code>for rows in data.values():
    for row in rows:
        writer.writerow(row)
</code></pre>

<p>A the beginning add "import copy". I wrote it from my head, may need some tweaking.</p>

<p>Basically what this does is adds another layer in form of list where you update your old values with new ones and add updated new record to the end of list with (all collected data are put into the new record). When writing it has to go one level deeper.</p>

python python-3.x csv merge