title: Parsing Data using Pandas Numpy in Python
body: <p>I have a dataframe with 2 columns level1 and level2. Each account number in level1 is linked to a ParentID found in column level2. For accounts ending in &quot;8409&quot; in column &quot;level1&quot; some of them are mapped to the wrong ParentID in level2. To find its correct ParentID, you need to search in level1 where you replace all accounts that end in&quot;8409&quot; with &quot;8400&quot;. This will then find its equivalent account in the same column. Where a match is found, copy what is in column &quot;level2&quot; and replace it under the column for the accounts ending in &quot;8409&quot;.</p>
  <pre><code>import pandas as pd
  import numpy as np
  df = pd.DataFrame([[7854568400,489],
                      [9632588400,126],
                      [3699633691,189],
                      [9876543697,987],
                      [1111118409,987],
                      [7854568409,396],
                      [7854567893,897],      
                      [9632588409,147]],
  columns = ['level1','level2'])
  df
  </code></pre>
  <p>The solution below allowed to create a new column &quot;new_level2&quot; to solve the above problem.</p>
  <pre><code>maps = df.set_index('level1')['level2']
  s = df['level1'].astype(str).str.replace('8409$', '8400', regex=True).astype('int64')
  df['new_level2'] = s.map(maps).combine_first(df['level2']).convert_dtypes()
  </code></pre>
  <p>In the output below, account &quot;7854568409&quot; had its level2 changed from 396 to 489 (taken from row 0), and account &quot;9632588409&quot; had its level2 changed from 147 to 126 (taken from row 1).</p>
  <pre><code>level1  level2  new_level2
  0   7854568400  489 489
  1   9632588400  126 126
  2   3699633691  189 189
  3   9876543697  987 987
  4   1111118409  987 987
  5   7854568409  396 489
  6   7854567893  897 897
  7   9632588409  147 126
  </code></pre>
  <p>However, when I apply the above solution with additional variables <strong>this is where I run into problems</strong> mainly when I add the currency to the dataframe. The replacing of level2 values ONLY applies to USd only, all other currencies need to retain its current value in column level2.</p>
  <p><strong>New DF</strong></p>
  <pre><code>df = pd.DataFrame([['USD',7854568400,489],
                      ['USD',9632588400,126],
                      ['USD',3699633691,189],
                      ['USD',9876543697,987],
                      ['EUR',1111118400,120],
                      ['EUR',1111118409,987],
                      ['USD',1111118409,987],
                      ['USD',7854568409,396],
                      ['USD',7854567893,897],      
                      ['USD',9632588409,147]],
  columns = ['cur','level1','level2'])
  </code></pre>
  <p>I get the following error when i attempt to apply the above solution to the new dataframe that contains the currency.
  <code>InvalidIndexError: Reindexing only valid with uniquely valued Index objects</code></p>
  <p>Its worth noting that you can have the same account number across different currencies.</p>
  <p>Desired output is below. Only 2 accounts (Index 7 &amp; 9) should have their level2 changed. Index 4 and 5 should retain its original level2 value because they are euro and not in scope, and Index 6 because there was no corresponding match found for this account, therefore it retains it original value.</p>
  <pre><code>   cur  level1     level2   new_level2
  0   USD 7854568400  489     489
  1   USD 9632588400  126     126
  2   USD 3699633691  189     189
  3   USD 9876543697  987     987
  4   EUR 1111118400  120     120
  5   EUR 1111118409  987     987
  6   USD 1111118409  987     987
  7   USD 7854568409  396     489
  8   USD 7854567893  897     897
  9   USD 9632588409  147     126
  </code></pre>
  <p>Any help is greatly appreciated.</p>