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>

<pre>
cat csv csv1 | awk '{if (!status[$2] || status[$2]!=$3) {print $0; status[$2]=$3} }'
</pre>

<p><strong>explanation</strong></p>

<p>print these files in sequence and iterete line by line</p>

<p><code>cat csv csv1 | awk '{</code> </p>

<p>Keep second column (<code>unique id</code>) in an array key and the third one column as the value. Then check, if the array element doesn't exists (it means this is first occurence of that row) OR value is not equal to the third one (it means that value has changed)</p>

<p><code>if (!status[$2] || status[$2]!=$3) {</code></p>

<p>then simply print the row and set the array value</p>

<p><code>print $0;</code>
<code>status[$2]=$3</code></p>

<p>if ends</p>

<p><code>}</code></p>

<p>awk ends
<code>}'</code></p>

python csv pandas dataframe