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$
  • 1
    $\begingroup$ The function you are looking for is drop $\endgroup$ – Juan Esteban de la Calle Apr 17 '19 at 15:39
  • $\begingroup$ Drop what? How? Can you be more specific? $\endgroup$ – BlueIvy Apr 17 '19 at 15:40
  • $\begingroup$ This link can help you: pandas.pydata.org/pandas-docs/stable/reference/api/… $\endgroup$ – Juan Esteban de la Calle Apr 17 '19 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 '19 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 '19 at 13:59
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)
| improve this answer | |
$\endgroup$
  • $\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 '19 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$ – ukemi Apr 23 '19 at 10:20
  • $\begingroup$ That worked! Thank you! $\endgroup$ – BlueIvy Apr 23 '19 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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