How to re-oder a dataframe in pandas using a string in cell?

user_9586779 0
Viewed 31

I have been trying to re-order a dataframe in pandas by locating the cell which contains the string negative, delete this row and instead add a new column into the dataframe which distinguishes the rows before the cell which contains the string negative and call them 'POSITIVE' whereas the rows which used to be after the string, call them 'NEGATIVE'.

This is a minimal example of the dataframe:

d = {'col1': [1, 2, 'negative', 4,5], 
'col2': [6,7,None, 8,9],
'col3':[10,11,None,12,13] }

data = pd.DataFrame(data=d)

This is what I have been trying to acheive:

arget = {'col1': [1, 2, 4, 5], 
'col2': [6,7, 8,9],
'col3':[10,11, 12,13],
'col4':['POSTIVE','POSTIVE','NEGATIVE', 'NEGATIVE' ] }
target = pd.DataFrame(data= target)

I have tried to split the dataframe, then remove the row and then add the new column and finally join them again. I was wondering if there is a better way in pandas to achieve this?

pandas dataframe python
1 Answers

First compare by negative for first mask, then add cumulative sum and compare for previous values to mask1, pass to numpy.where and last remove negative rows by invert mask by ~ in boolean indexing:

mask = data['col1'].eq('negative')
mask1 = mask.cumsum().eq(0)

data['col4'] = np.where(mask1, 'POSTIVE','NEGATIVE')
data = data[~mask].copy()
print (data)
  col1  col2  col3      col4
0    1   6.0  10.0   POSTIVE
1    2   7.0  11.0   POSTIVE
3    4   8.0  12.0  NEGATIVE
4    5   9.0  13.0  NEGATIVE
user_29010020
Related