Concating CSV files but Filtering Duplicates by 2 Columns
<p>I have a csv I want to update based on certain criteria. Example: </p>

<pre><code>csv: 
Name     UniqueID    Status
Apple    1121        Full
Orange   1122        Eaten
Apple    1123        Rotten
</code></pre>

<p>New values (also in a csv):</p>

<pre><code>csv1:
Apple    1121        Eaten
orange   1122        Eaten
Pear     1233        Wiggly 
</code></pre>

<p>the updated csv would look like this:</p>

<pre><code>Name     UniqueID    Status
Apple    1121        Full
Orange   1122        Eaten
Apple    1123        Rotten
Pear     1233        Wiggly
Apple    1121        Eaten
</code></pre>

<p>So basically skip the entries that have the same <code>UniqueID</code> and <code>Status</code>. If it's a new <code>UniqueID</code> or an existing <code>UniqueID</code> and a different <code>Status</code> I want it included as a separate row. So from the above example <code>orange   1122  Eaten</code>, was excluded. </p>

<p>I tried converting the csv to a DataFrame and using the <code>drop_duplicates</code>. </p>

<p><code>data = pd.concat([pd.DataFrame.from_csv(csv, csv1)].drop_duplicates(subset=['Status', 'UniqueID'])</code></p>

<p>But it predictably dropped all the duplicates. Which resulted in:</p>

<pre><code>    Name     UniqueID    Status
    Apple    1121        Full
    Orange   1122        Eaten
    Apple    1123        Rotten
    Pear     1233        Wiggly
   # Apple    1121        Eaten  &lt;-- this result was excluded
</code></pre>

<p><strong><em>setup</em></strong>  </p>

<pre><code>import pandas as pd
from StringIO import StringIO

csv = """Name     UniqueID    Status
Apple    1121        Full
Orange   1122        Eaten
Apple    1123        Rotten"""

csv1 = """Name     UniqueID    Status
Apple    1121        Eaten
Orange   1122        Eaten
Pear     1233        Wiggly """
</code></pre>

<p><strong><em>option 1</em></strong><br>
<code>set_index</code> + <code>combine_first</code> + <code>reduce</code></p>

<pre><code>def fruit_status1(f):
    return pd.read_csv(StringIO(f), delim_whitespace=True,
                       index_col=['UniqueID', 'Status'])

def update1(d1, d2):
    return d2.combine_first(d1)

reduce(update1, [fruit_status1(f) for f in [csv, csv1]])
</code></pre>

<p><a href="https://i.stack.imgur.com/TGIDG.png" rel="nofollow"><img src="https://i.stack.imgur.com/TGIDG.png" alt="enter image description here"></a></p>

<p><strong><em>option 2</em></strong><br>
<code>pd.concat</code> + <code>drop_duplicates</code></p>

<pre><code>def fruit_status2(f):
    return pd.read_csv(StringIO(f), delim_whitespace=True)

pd.concat([fruit_status2(f) for f in [csv, csv1]]) \
    .drop_duplicates(subset=['UniqueID', 'Status'])
</code></pre>

<p><a href="https://i.stack.imgur.com/NoI8w.png" rel="nofollow"><img src="https://i.stack.imgur.com/NoI8w.png" alt="enter image description here"></a></p>

python csv pandas dataframe