CODEWITHSUNDEEP
pythonpandas
euri10
euri10

Reputation: 2626

pandas filtering consecutive rows

I got a Dataframe with a Matrix colum like this

11034-A
11034-B
1120-A
1121-A
112570-A
113-A
113.558
113.787-A
113.787-B
114-A
11691-A
11691-B
117-A RRS
12 X R
12-476-AT-A
12-476-AT-B

I'd like to filter only matrix that ends with A or B only when they are consecutive, so in the example above 11034-A and 11034-B, 113.787-A and 113.787-B, 11691-A and 11691-B, 12-476-AT-A and 12-476-AT-B

I wrote the function that will compare those 2 strings and return True or False, the problem is I fail to see how to apply / applymap to the consecutive rows:

def isAB(stringA, stringB):
    if stringA.endswith('A') and stringB.endswith('B') and stringA[:-1] == stringB[:-1]:
        return True
    else:
        return False

I tried df['result'] = isAB(df['Matrix'].str, df['Matrix'].shift().str) to no-avail I seem to lack something in the way I designed this

edit : I think this works, looks like I overcomplicated at 1st :

df['t'] = (df['Matrix'].str.endswith('A') & df['Matrix'].shift(-1).str.endswith('B')) | (df['Matrix'].str.endswith('B') & df['Matrix'].shift(1).str.endswith('A'))
df['p'] = (df['Matrix'].str[:-1] == df['Matrix'].shift(-1).str[:-1]) | (df['Matrix'].str[:-1] == df['Matrix'].shift(1).str[:-1])
df['e'] = df['p'] & df['t']

final = df[df['e']]

Upvotes: 3

Views: 1703

Answers (3)

rurp
rurp

Reputation: 1446

Here is how I would do it.

df['ShiftUp'] = df['matrix'].shift(-1)
df['ShiftDown'] = df['matrix'].shift()

def check_matrix(x):
    if pd.isnull(x.ShiftUp) == False and x.matrix[:-1] == x.ShiftUp[:-1]:
        return True
    elif pd.isnull(x.ShiftDown) == False and x.matrix[:-1] == x.ShiftDown[:-1]:
        return True
    else:
        return False

df['new'] = df.apply(check_matrix, axis=1)
df = df.drop(['ShiftUp', 'ShiftDown'], axis=1)
print df

prints

         matrix    new
0       11034-A   True
1       11034-B   True
2        1120-A  False
3        1121-A  False
4      112570-A  False
5         113-A  False
6       113.558  False
7     113.787-A   True
8     113.787-B   True
9         114-A  False
10      11691-A   True
11      11691-B   True
12    117-A RRS  False
13       12 X R  False
14  12-476-AT-A   True
15  12-476-AT-B   True

Upvotes: 2

elelias
elelias

Reputation: 4781

Here's my solution, it requires a bit of work.

The strategy is the following: obtain a new column that has the same values as the current column but shifted one position.

Then, it's just a matter to check whether one column is A or B and the other one B or A.

Say your matrix colum is called "column_name".

Then:

myl = ['11034-A',
'11034-B',
'1120-A',
'1121-A',
'112570-A',
'113-A',
'113.558',
'113.787-A',
'113.787-B',
'114-A',
'11691-A',
'11691-B',
'117-A RRS',
'12 X R',
'12-476-AT-A',
'12-476-AT-B']

    #toy data frame
    mydf = pd.DataFrame.from_dict({'column_name':myl})

    #get a new series which is the same one as the original
    #but the first entry contains "nothing"
    new_series = pd.Series(  ['nothing'] + 
    mydf['column_name'][:-1].values.tolist() )

    #add it to the original dataframe
    mydf['new_col'] = new_series

You then define a simple function:

def do_i_want_this_row(x,y):

    left_char = x[-1]
    right_char = y[-1]
    return ((left_char == 'A') & (right_char == 'B')) or ((left_char == 'B') & (right_char=='A'))

and voila:

print mydf[mydf.apply(lambda x: do_i_want_this_row( x.column_name, x.new_col), axis=1)]

 column_name      new_col
1       11034-B      11034-A
2        1120-A      11034-B
8     113.787-B    113.787-A
9         114-A    113.787-B
11      11691-B      11691-A
15  12-476-AT-B  12-476-AT-A

There is still the question of the last element, but I'm sure you can think of what to do with it if you decide to follow this strategy ;)

Upvotes: 1

Nat Knight
Nat Knight

Reputation: 374

You can delete rows from a DataFrame using DataFrame.drop(labels, axis). To get a list of labels to delete, I would first get a list of pairs that match your criterion. With the labels from above in a list labels and your isAB function,

pairs = zip(labels[:-1], labels[1:])
delete_pairs = filter(isAB, pairs)

delete_labels = []
for a,b in delete_pairs:
    delete_labels.append(a)
    delete_labels.append(b)

Examinedelete_labels to make sure you've put it together correctly,

print(delete_labels)

And finally, delete the rows. With the DataFrame in question as x,

x.drop(delete_labels) # or x.drop(delete_labels, axis) if appropriate

Upvotes: 0

Related Questions