title: Memory efficient solution to replace invalid values in a large DataFrame?
body: <p>This question is a continuation of the following:
  <a href="https://stackoverflow.com/questions/53625099/how-to-replace-certain-rows-by-shared-column-values-in-pandas-dataframe">How to replace certain rows by shared column values in pandas DataFrame?</a></p>
  
  <p>Let's say I have the following pandas DataFrame:</p>
  
  <pre><code>import pandas as pd
  
  data = [['Alex',10],['Bob',12],['Clarke',13], ['Bob', '#'], ['Bob', '#'], ['Bob', '#'], ['Clarke', '#']]
  
  df = pd.DataFrame(data,columns=['Name','Age'], dtype=float)
  
       Name Age
  0    Alex  10
  1     Bob  12
  2  Clarke  13
  3     Bob   #
  4     Bob   #
  5     Bob   #
  6  Clarke   #
  </code></pre>
  
  <p>Rows 3-6 have invalid values, the string <code>#</code>. These should be replaced by valid values, outputting:</p>
  
  <pre><code>     Name Age
  0    Alex  10
  1     Bob  12
  2  Clarke  13
  3     Bob  12
  4     Bob  12
  5     Bob  12
  6  Clarke  13
  </code></pre>
  
  <p>The pandas solutions discussed to replace these values discussed were using <code>coerce</code>, or replacing with a subset data frame:</p>
  
  <pre><code>v = df.assign(Age=pd.to_numeric(df['Age'], errors='coerce')).dropna()
  df['Age'] = df['Name'].map(v.set_index('Name').Age) 
  </code></pre>
  
  <p>or </p>
  
  <pre><code>d= df[df['Age']!='#'].set_index('Name')['Age']
  df['Age']=df['Name'].replace(d)
  </code></pre>
  
  <p>The problem is for a pandas DataFrame with millions of rows, these pandas-based solutions become very memory intensive. </p>
  
  <p>In situations like these with pandas, what would be the most practical solution? </p>
  
  <p>I could try to create a massive dictionary using <code>df[df['Age']!='#']</code>, with <code>Name: Age</code> as the key-value pairs. Then, iterate through the original pandas DataFrame row by row; if there is a row with Age==<code>#</code>, then replace it based on the key-value pair in the dictionary. The downside to this is, a for-loop will take forever. </p>
  
  <p>Are there other solutions which would have better performance? </p>