Identify cells containing specific strings and overwrite content with numbers using Python
<p>I have a dataframe which looks like this:</p>

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

<p>My goal is to identify for each cell of every column if the following strings are contained: <code>'KSS'</code>, <code>'ABC'</code>, <code>'DEF'</code>, <code>'ABC / DEF'</code>, <code>'KSS / DEF'</code></p>

<p>Subsequently I would like to substitute the content with the following values: <code>'KSS'</code> -> 100, <code>'ABC'</code> -> 200, <code>'DEF'</code> -> 300, <code>'ABC / DEF'</code> -> 400, <code>'KSS / DEF'</code> -> 500</p>

<p>The output should be like something like this:</p>

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

<p>Notice: the algorithm should be generic and check every column, not only number 3. For sake of completeness the data types are all <code>objects</code>.</p>

<p>So far my line of codes are these but I guess they are incomplete...</p>

<pre><code>import pandas as pd
import numpy as np  
df = pd.DataFrame([
        ['XYZ', 'BALSO', 'PISCO', 'KSS', 'Yes', 660, 'Cop'],
        ['XYZ', 'TONTO', 'LOLLO', '195', 500, 'Yes', 'nan'],
        ['XYZ', 'CALLO', 'WANDA', 'ABC / DEF', 'Yes', 500, 'nan'],
        ['XYZ', 'AZUNGO', 'FINGI', 'KSS / DEF', 'Yes', 500, 'nan']
    ])
df = pd.read_csv('prova.csv', sep=',', skiprows=0, header=None, low_memory=False)
df.str.replace('KSS|ABC|DEF','?')
</code></pre>

<p>If you create a dict with your lookup and replacement values then you can call <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.map.html#pandas.Series.map" rel="nofollow"><code>map</code></a> on this column, additionally you need to pass <code>na_action='ignore'</code> to <code>map</code> otherwise you get a <code>KeyError</code> for the missing values, additionally you will note that as you have missing values the values get converted to <code>float</code> but you can cast again using <code>astype(int)</code> later:</p>

<pre><code>In [182]:
d={'KSS':100, 'ABC' :200, 'DEF' : 300, 'ABC / DEF' : 400, 'KSS / DEF' : 500}
df[3] = df[3].map(d, na_action='ignore')
df

Out[182]:
     0       1      2      3    4    5
0  XYZ   BALSO  PISCO 100.00  660  Cop
1  XYZ   TONTO  LOLLO    nan  500  nan
2  XYZ   CALLO  WANDA 400.00  500  nan
3  XYZ  AZUNGO  FINGI 500.00  500  nan
</code></pre>

<p>here we cast the type using <code>astype</code>:</p>

<pre><code>In [178]:
df[3] = df[3].astype(int)
df

Out[178]:
     0       1      2    3    4    5
0  XYZ   BALSO  PISCO  100  660  Cop
1  XYZ   TONTO  LOLLO  195  500  nan
2  XYZ   CALLO  WANDA  400  500  nan
3  XYZ  AZUNGO  FINGI  500  500  nan
</code></pre>

python pandas replace substitution