replacing a column in DataFrame using regex
<p>I have a data frame with 4 columns, col4 is a string including texts and digits:</p>

<pre><code> Col1           Col2              Col3           Col4
Syslog        2016,09,17           1    PD380_003 %LINK-3-UPDOWN
Syslog        2016,09,17           1    NM380_005 %BGP-5-NBR_RESET
Syslog        2016,09,14           1    NM380_005 %BGP-5-NBR_RESET
Syslog        2016,09,08           1    DO NOT TICKET LO380_004 %SYS-5-CONFIG_I Config
</code></pre>

<p>i need to keep a substring of that column and delete anything else, so I used regex and i made a pattern but when i run the following query the result is not what i want, it replace everything with the pattern itself:</p>

<pre><code>data.replace({'Col4':{'.*':'([A-Z]{2}[0-9]{3}_[0-9]{3})'}},regex=True)
</code></pre>

<p>desired result is:</p>

<pre><code> Col1           Col2              Col3           Col4
Syslog        2016,09,17           1           PD380_003
Syslog        2016,09,17           1           NM380_005
Syslog        2016,09,14           1           LO380_004
Syslog        2016,09,08           1           LO380_004
</code></pre>

<p>but the result i get is like:</p>

<pre><code> Col1           Col2              Col3           Col4
Syslog        2016,09,17           1    ([A-Z]{2}[0-9]{3}_[0-9]{3})
Syslog        2016,09,17           1    ([A-Z]{2}[0-9]{3}_[0-9]{3})
Syslog        2016,09,14           1    ([A-Z]{2}[0-9]{3}_[0-9]{3})
Syslog        2016,09,08           1    ([A-Z]{2}[0-9]{3}_[0-9]{3})
</code></pre>

<p>what am i doing wrong?</p>

<p>First, you have the wrong regex's in the wrong positions. The <code>to_replace</code> argument to <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.replace.html?highlight=replace#pandas-dataframe-replace" rel="nofollow">.replace</a> needs to match what to replace and what to delete. So you need a <code>^.*</code> in front of and a <code>.*$</code> behind your regex in this case since you want to trim the string outside the match:</p>

<pre><code>^.*([A-Z]{2}[0-9]{3}_[0-9]{3}).*$
</code></pre>

<p><a href="https://www.regex101.com/r/X6cypX/3" rel="nofollow">Demo</a></p>

<p>Second, the <code>replace</code> argument, if a regex, needs to be a capturing group or fixed string. In this case <code>\1</code> will do. </p>

<p>Last, the Series form of <code>.replace</code> has a littler simpler syntax (at least for me) to understand. </p>

<p>So given:</p>

<pre><code>&gt;&gt;&gt; df
     Col1        Col2  Col3                                            Col4
0  SysLog  2016,09,17     1                        PD380_003 %LINK-3-UPDOWN
1  SysLog  2016,09,17     1                      NM380_005 %BGP-5-NBR_RESET
2  SysLog  2016,09,17     1                      NM380_005 %BGP-5-NBR_RESET
3  SysLog  2016,09,17     1  DO NOT TICKET LO380_004 %SYS-5-CONFIG_I Config
</code></pre>

<p>You can do:</p>

<pre><code>&gt;&gt;&gt; df['Col4'].replace(to_replace='^.*([A-Z]{2}[0-9]{3}_[0-9]{3}).*$', value=r'\1', regex=True) 
0    PD380_003
1    NM380_005
2    NM380_005
3    LO380_004
Name: Col4, dtype: object
</code></pre>

<p>You can also use a positional argument version if easier: </p>

<pre><code>df['Col4'].replace('^.*([A-Z]{2}[0-9]{3}_[0-9]{3}).*$', r'\1', regex=True)
</code></pre>

<p>but you need to have <code>regex=True</code> since the replacement string is to be interpreted as a regex -- not just a static string.</p>

<p>Finally, assign directly into the original:</p>

<pre><code>&gt;&gt;&gt; df['Col4']=df['Col4'].replace('^.*([A-Z]{2}[0-9]{3}_[0-9]{3}).*$', r'\1', regex=True)
&gt;&gt;&gt; df
     Col1        Col2  Col3       Col4
0  SysLog  2016,09,17     1  PD380_003
1  SysLog  2016,09,17     1  NM380_005
2  SysLog  2016,09,17     1  NM380_005
3  SysLog  2016,09,17     1  LO380_004
</code></pre>

python pandas