2
$\begingroup$

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.

$\endgroup$
5
  • 1
    $\begingroup$ The function you are looking for is drop $\endgroup$ Apr 17, 2019 at 15:39
  • $\begingroup$ Drop what? How? Can you be more specific? $\endgroup$
    – BlueIvy
    Apr 17, 2019 at 15:40
  • $\begingroup$ This link can help you: pandas.pydata.org/pandas-docs/stable/reference/api/… $\endgroup$ Apr 17, 2019 at 15:40
  • $\begingroup$ I am still not sure how to drop rows between values that repeat multiple times across dataframe. $\endgroup$
    – BlueIvy
    Apr 17, 2019 at 15:45
  • $\begingroup$ First fill the values of the empty column_A cells and then drop the "deletions" and "changes" rows $\endgroup$
    – pcko1
    Apr 18, 2019 at 13:59

1 Answer 1

0
$\begingroup$

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)
$\endgroup$
3
  • $\begingroup$ Unfortunately, this won't work because there are other strings that belong to different groups, not only NaN values. I updated how the dataframe looks like. $\endgroup$
    – BlueIvy
    Apr 23, 2019 at 10:13
  • 1
    $\begingroup$ @user68225 idea: replace all 'column_A' values not equal to 'NEW_RECORDS', 'DELETIONS', 'CHANGES' with NaN then forward fill etc $\endgroup$
    – iacob
    Apr 23, 2019 at 10:20
  • $\begingroup$ That worked! Thank you! $\endgroup$
    – BlueIvy
    Apr 23, 2019 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.