Extract data from a database if a specific value in is followed by a another specific value

48
February 12, 2019, at 12:10 PM

I have a database in MySQL where one of the columns consists of different types of events and I want to extract specific data to python. For example:

Database:

ID  Event  A
 1    1   jdf
 1    2   kfl
 1    3   sdf
 2    2   asd
 2    1   sdf
 2    4   asf
 2    2   asas
 2    3   aas

What I am looking for is to take the events equal to 1 if the next event is 2 and create a new table.

Output:

 ID  Event  A
 1    1   jdf
 1    2   kfl
 2    1   sdf
 2    2   asas
Answer 1

shift

ones = df.Event.eq(1)
twos = df.Event.eq(2)
ones_then_twos = ones & twos.shift(-1)
mask = ones_then_twos | ones_then_twos.shift()
df[mask]
   ID  Event     A
0   1      1   jdf
1   1      2   kfl
4   2      1   sdf
5   2      2  asas

ones_then_twos shifts the twos boolean series backwards by one to ensure that we have a two that follows a one.

ones_then_twos
0     True
1    False
2    False
3    False
4     True
5    False
6    False
Name: Event, dtype: bool

But this only gets the ones that are followed by twos. We also want the twos that are preceded by ones. But these just come right after so we shift forward the same mask and use "or" to combine them:

ones_then_twos | ones_then_twos.shift()
0     True
1     True
2    False
3    False
4     True
5     True
6    False
Name: Event, dtype: bool

This is the mask we use and you can see that it is True for both the ones and twos where twos come on the heels of ones.

READ ALSO
SQL, Group by one column with one distinct column

SQL, Group by one column with one distinct column

I have a table with following structure

31
Get sum using DATE_FORMAT() and IN

Get sum using DATE_FORMAT() and IN

I've got a query that looks a little bit like this :

39
In select query include placeholder results where no direct result was found

In select query include placeholder results where no direct result was found

I have a table of race resultsNot all competitors enter every single race, but I would like to include a table which shows those races they didn't enter with blanks and a hyphen "-" value in the result column

41
mysql - current_timestamp and null

mysql - current_timestamp and null

I have question about mysql configurationI have the same database on AWS and local apache server on ubuntu

29