Delete all rows between two values in a dataframe that repeat multiple times in a column

Question

I have dataframe and let's say inside of it is a column_A. This column_A has 3 strings as values, call them 'new_records', 'deletions', 'changes' that repeat across the dataframe multiple times in that order always with multiple rows in between. I want to delete all rows from the beginning of deletions to the end of changes, i.e. I want to leave only new_records in the dataframe. The dataframe looks like this:

column_A         column_B     column_C ....
NEW_RECORDS        val1         val2
string1_new        val3         val4 
string2_new        val5         val6 
  NaN              val9         val10
  NaN              val11        val12 
string3_new
 ...
DELETIONS          val7         val8
string1_del         ...           ...
   NaN              ...           ...
string2_del         ...           ...
  ...    
CHANGES             ...           ...
 str1_ch            ... 
 str2_ch
  ... 
NEW_RECORDS
 str200_new        ...
 str300_new           ...
  NaN
  NaN
  ...
DELETIONS
 NaN
 str100_del
 NaN
 str290_del        ...
  ...
CHANGES
 str1000
 str20000
  NaN
   ...           ...

I want to have at the end only chunks of rows between new_records and deletions values, without rows that belong to the deletions group and changes group. How can I do that?

UPDATE:

There are many rows after the 'new_records' and before the start of 'deletions' group and there are many rows after the start of deletions group and beginning of the 'changes' group. I need to extract only rows that belong to the new_records group. So all rows after the value 'new_records' and before the value of 'deletions' across all dataframe.

Answer 1

You can achieve this by forward filling the blank values, and then selecting only those with new_records:

df.fillna(method='ffill')

df = df[df['column_A'] == 'new_records']

Depending on the actual values in your data frame, you may need to first replace what appear to be empty/space strings with NaN's:

df['column_A'] = df['column_A'].replace(r'^\s*$', np.nan, regex=True)