How to remove the last part of each entry in a column based on a pattern?

Question

I have a set of oil wells compiled in the panda's data frame. It looks like this: wells = pd.DataFrame({'date':['2019-01-01', '2019-01-01','2019-01-01', '2019-01-01','2019-01-01', '2019-01-01'], 'well': ['FIELDX-1D', 'FIELDX-2D', 'FIELDY-1C', 'FIELDY-1B', 'FIELDZ-1A', 'FIELDZ-1J'], 'oil_mmbd':[3.1, 5.6, 9.8, 2.5, 4.7, 6.1]}) date well oil_mmbd 0 2019-01-01 FIELDX-1D 3.1 1 2019-01-01 FIELDX-2D 5.6 2 2019-01-01 FIELDY-1C 9.8 3 2019-01-01 FIELDY-1B 2.5 4 2019-01-01 FIELDZ-1A 4.7 5 2019-01-01 FIELDZ-1J 6.1 What I am trying to achieve is to create a new variable (ie 'Field') that compiles the aggregate output of each field. For this, I need to get rid of the last part of each well's name; but I am not able to find the solution for this using Python and Pandas. I want to get to the table that looks like this: wells_agg = pd.DataFrame({'date':['2019-01-01', '2019-01-01','2019-01-01'], 'field': ['FIELDX', 'FIELDY', 'FIELDZ'], 'oil_mmbd':[8.7, 12.3, 10.8]}) date field oil_mmbd 0 2019-01-01 FIELDX 8.7 1 2019-01-01 FIELDY 12.3 2 2019-01-01 FIELDZ 10.8 Select the correct answer from above options

Answer 1

You can strip the suffix of a well column using wells.well.str.split('-').str[0]). Therefore instead of wells.groupby('well'), use wells.groupby(wells.well.str.split('-').str[0])). See solution below with this in mind. wells.groupby(['date',wells.well.str.split('-')\ .str[0]]).oil_mmbd.sum().reset_index() date well oil_mmbd 0 2019-01-01 FIELDX 8.7 1 2019-01-01 FIELDY 12.3 2 2019-01-01 FIELDZ 10.8 If you want to know more about the Data Science then do check out the following Data Science which will help you in understanding Data Science from scratch