How to conditionally copy a substring into a new column of a pandas dataframe?

Question

This is my first post so hopefully I don't botch the question and I'm clear. Basically, this is a two part question. I need to set up code that first checks whether or not Column A = "VALID". If this is true, I need to extract the substring from column B and place it in a new column, here labeled "C". If the conditional is false, I will like to put in "NA". See the second table for my desired outcome.

|     A       |             B                     |
|-------------|-----------------------------------|
|    VALID    |asdfafX'XextractthisY'Yeaaadf      |
|    INVALID  |secondrowX'XsubtextY'Yelakj        |
|    VALID    |secondrowX'XextractthistooY'Yelakj |

---

|     A       |             B                       |      C          |
|-------------|-------------------------------------|-----------------|
|    VALID    |"asdfafX'XextractthisY'Yeaaadf"      | extractthis     |
|    INVALID  |"secondrowX'XsubtextY'Yelakj"        | NA              |
|    VALID    |"secondrowX'XextractthistooY'Yelakj" | extractthistoo  |

A few things to note:

-The substring will always start after the phrase "X'X" and finish right before "Y'Y".

-The substring will be of differing lengths from cell to cell.

I know the following code is wrong, but I wanted to show you at how I have been attempting to solve this problem:

import pandas as pd

if df[A] == "VALID":
   df[C] = df[B]df.str[start:finish]
else:
   df[C].isna()

I apologize for the errors in this basic code, as I am new to python altogether and still rely on an IDE and trial&error to guide me. Any help you can provide is appreciated.

Answer 1

You can use pd.Series.str.extract:

In [737]: df
Out[737]: 
         A                                   B
0    VALID       asdfafX'XextractthisY'Yeaaadf
1  INVALID         secondrowX'XsubtextY'Yelakj
2    VALID  secondrowX'XextractthistooY'Yelakj

In [745]: df['C'] = df[df.A == 'VALID'].B.str.extract("(?<=X'X)(.*?)(?=Y'Y)", expand=False)

In [746]: df
Out[746]: 
         A                                   B               C
0    VALID       asdfafX'XextractthisY'Yeaaadf     extractthis
1  INVALID         secondrowX'XsubtextY'Yelakj             NaN
2    VALID  secondrowX'XextractthistooY'Yelakj  extractthistoo

The regex pattern is:

(?<=X'X)(.*?)(?=Y'Y)

• (?<=X'X) is a lookbehind for X'X

• (.*?) matches everything between the lookbehind and lookahead

• (?=Y'Y) is a lookahead for Y'Y