Conditional editing of strings in a Pandas DataFrame
<p>I am learning Pandas and have a DataFrame of strings which looks a little like this:</p>

<pre><code>df = pd.DataFrame([['Apple', 'Med6g7867'], ['Orange', 'Med7g8976'], ['Banana', 'Signal'], ['Peach', 'Med8g8989'], ['Mango', 'Possible result %gggyy']], columns=['A', 'B'])
df
    A       B
0   Apple   Med6g7867
1   Orange  Med7g8976
2   Banana  Signal
3   Peach   Med8g8989
4   Mango   Possible result %gggyy
</code></pre>

<p>Note column B has two types of value, either a unique identifier of the form MedXgXXXX or a descriptive string. I would like to do two related things.</p>

<ol>
<li>Substitute all the values of B with the unique identifier to NaN</li>
<li>Retain the descriptive string but truncate any that have a % sign so that I only retain the string prior to the % sign.</li>
</ol>

<p>I would like a table like this:</p>

<pre><code>    A       B
0   Apple   NaN
1   Orange  NaN
2   Banana  Signal
3   Peach   NaN
4   Mango   Possible result
</code></pre>

<p>Currently I can subset the table like so:</p>

<pre><code>df[df['B'].str.contains("Med")]
df[df['B'].str.contains("%")]
</code></pre>

<p>but no implementation of <code>replace()</code> I try allows me to do this.</p>

<p>Any help appreciated.</p>

<pre><code>import pandas as pd
df = pd.DataFrame([['Apple', 'Med6g7867'],
                   ['Orange', 'Med7g8976'],
                   ['Banana', 'Signal'],
                   ['Peach', 'Med8g8989'],
                   ['Mango', 'Possible result %gggyy']],
                  columns=['A', 'B'])

df['B'] = df['B'].str.extract(r'(?:^Med.g.{4})|([^%]+)', expand=False)
print(df)
</code></pre>

<p>yields</p>

<pre><code>        A                 B
0   Apple               NaN
1  Orange               NaN
2  Banana            Signal
3   Peach               NaN
4   Mango  Possible result 
</code></pre>

<hr>

<p>The regex pattern has the following meaning:</p>

<pre><code>(?:            # start a non-capturing group
  ^            # match the start of the string
  Med          # match the literal string Med
  .            # followed by any character
  g            # a literal g
  .{4}         # followed by any 4 characters
)              # end the non-capturing group
|              # OR
(              # start a capturing group
  [^%]+        # 1-or-more of any characters except %
)              # end capturing group
</code></pre>

<p>If the value in the <code>B</code> column starts with a unique indentifier of the form
<code>MedXgXXXX</code> then the non-capturing group will be matched. Since <code>str.extract</code>
only returns the value from capturing groups, the <code>Series</code> returned by
<code>str.extract</code> will have a <code>NaN</code> at this location.</p>

<p>If instead the capturing group is matched, then <code>str.extract</code> will return the
matched value.</p>

python regex pandas