Extract part of string when has "_" and "_V" and without it in pandas datframe python

user_10404281 0
Viewed 36

I have the below pandas dataframe

d = {'col1': [1, 2,3,4,5,60,0,0],'col3': [1, 22,33,44,55,60,1,5],
     'Name': ['greenCha|place|Palace_place_greenCha_1st_Rubber_plant_past_pu_24_mos_Aug_2021_V8|1P|ABC-6712a32|JKL-5234276'
              , 'JKL-879887_Mirror place greenCha_1st_Giant bird pu_24mos_V1',
              'greenCha|place|Palace_place_greenCha_1st_Rubber_plant_past_pu_1_m_V1|1P|ABC-7823932|JKL-90aw6084 199,291',
              'JKL-346076_sound.co corn plant Detail Page Medium Rectangle _V3',
              'JKL-576051_sound.co Madagascar dragon tree Page Block 1_V3',
              'JKL-981290_Mirror place greenCha_1st_Giant bird pu_24mos  _V8',
              'JKL-2196182_sound.co greenCha Run of Search_V8',
              np.nan],'Date': ['2021-06-13', '2021-06-13','2021-06-13','2021-06-14','2021-06-15','2021-06-15','2021-06-13','2021-06-16'],
     }
dff = pd.DataFrame(data=d)
dff
    col1    col3    Name                                            Date      
0   1       1   greenCha|place|Palace_place_greenCha_1st_Rubbe...   2021-06-13
1   2       22  JKL-879887_Mirror place greenCha_1st_Giant bir...   2021-06-13
2   3       33  greenCha|place|Palace_place_greenCha_1st_Rubbe...   2021-06-13
3   4       44  JKL-346076_sound.co corn plant Detail Page Med...   2021-06-14
4   5       55  JKL-576051_sound.co Madagascar dragon tree Pag...   2021-06-15
5   60      60  JKL-981290_Mirror place greenCha_1st_Giant bir...   2021-06-15
6   0       1   JKL-2196182_sound.co greenCha Run of Search_V8      2021-06-13
7   0       5   NaN                                                 2021-06-16

I want to create a new column named "New Name". Most of the name has JKL number in the beginning and only the line starting with "Palace" hasn't that number. When the JKL number has it in the line I want to extract a new name starting with "_" and ending before with "_V". When it doesn't have a JKL number new name should extract should starting with "Palace" and ending before with "_V". Everyline has _V. So it should below look like below.

    col1    col3    Name                                            Date        New Name
0   1       1   greenCha|place|Palace_place_greenCha_1st_Rubbe...   2021-06-13  Palace_place_greenCha_1st_Rubber_plant_past_pu_24_mos_Aug_2021
1   2       22  JKL-879887_Mirror place greenCha_1st_Giant bir...   2021-06-13  Mirror place greenCha_1st_Giant bird pu_24mos
2   3       33  greenCha|place|Palace_place_greenCha_1st_Rubbe...   2021-06-13  Palace_place_greenCha_1st_Rubber_plant_past_pu_1_m
3   4       44  JKL-346076_sound.co corn plant Detail Page Med...   2021-06-14  sound.co corn plant Detail Page Medium Rectangle
4   5       55  JKL-576051_sound.co Madagascar dragon tree Pag...   2021-06-15  sound.co Madagascar dragon tree Page Block 1
5   60      60  JKL-981290_Mirror place greenCha_1st_Giant bir...   2021-06-15  Mirror place greenCha_1st_Giant bird pu_24mos
6   0       1   JKL-2196182_sound.co greenCha Run of Search_V8      2021-06-13  sound.co greenCha Run of Search
7   0       5   NaN                                                 2021-06-16  nan

I was trying to start with the below code and stuck here

detail['new_name']    = detail.apply(lambda row: str(row['Name']________) if row['Name'].str.contains('Palace') else(), axis=1)

Is it possible to extract in python? Thanks in advance! Any help is appreciated!

pandas python
1 Answers

You can use:

pattern = r'(?:JKL-\d+_|(?=Palace))(.*)_V\d+'
dff['New Name'] = dff['Name'].str.extract(pattern, expand=False).str.strip()

Output:

>>> df['New Name']
0    Palace_place_greenCha_1st_Rubber_plant_past_pu_24_mos_Aug_2021
1                     Mirror place greenCha_1st_Giant bird pu_24mos
2                Palace_place_greenCha_1st_Rubber_plant_past_pu_1_m
3                  sound.co corn plant Detail Page Medium Rectangle
4                      sound.co Madagascar dragon tree Page Block 1
5                     Mirror place greenCha_1st_Giant bird pu_24mos
6                                   sound.co greenCha Run of Search
7                                                               NaN
Name: New Name, dtype: object
user_152399510
Related