Selecting data with .query()

Joining Data with pandas

Aaren Stubberfield

Instructor

The .query() method

.query('SOME SELECTION STATEMENT')
  • Accepts an input string
    • Input string used to determine what rows are returned
    • Input string similar to statement after WHERE clause in SQL statement
      • Prior knowledge of SQL is not necessary
Joining Data with pandas

Querying on a single condition

This table is stocks

  date        disney      nike      
0 2019-07-01  143.009995  86.029999 
1 2019-08-01  137.259995  84.5      
2 2019-09-01  130.320007  93.919998 
3 2019-10-01  129.919998  89.550003 
4 2019-11-01  151.580002  93.489998 
5 2019-12-01  144.630005  101.309998
6 2020-01-01  138.309998  96.300003 
7 2020-02-01  117.650002  89.379997 
8 2020-03-01  96.599998   82.739998 
9 2020-04-01  99.580002   84.629997 
stocks.query('nike >= 90')
  date        disney      nike      
2 2019-09-01  130.320007  93.919998 
4 2019-11-01  151.580002  93.489998 
5 2019-12-01  144.630005  101.309998
6 2020-01-01  138.309998  96.300003
Joining Data with pandas

Querying on a multiple conditions, "and", "or"

This table is stocks

  date        disney      nike      
0 2019-07-01  143.009995  86.029999 
1 2019-08-01  137.259995  84.5      
2 2019-09-01  130.320007  93.919998 
3 2019-10-01  129.919998  89.550003 
4 2019-11-01  151.580002  93.489998 
5 2019-12-01  144.630005  101.309998
6 2020-01-01  138.309998  96.300003 
7 2020-02-01  117.650002  89.379997 
8 2020-03-01  96.599998   82.739998 
9 2020-04-01  99.580002   84.629997 
stocks.query('nike > 90 and disney < 140')
  date        disney      nike     
2 2019-09-01  130.320007  93.919998
6 2020-01-01  138.309998  96.300003
stocks.query('nike > 96 or disney < 98')
   date        disney      nike      
5  2019-12-01  144.630005  101.309998
6  2020-01-01  138.309998  96.300003 
28 020-03-01  96.599998   82.739998
Joining Data with pandas

Updated dataset

This table is stocks_long

  date        stock   close     
0 2019-07-01  disney  143.009995
1 2019-08-01  disney  137.259995
2 2019-09-01  disney  130.320007
3 2019-10-01  disney  129.919998
4 2019-11-01  disney  151.580002
5 2019-07-01  nike    86.029999 
6 2019-08-01  nike    84.5      
7 2019-09-01  nike    93.919998 
8 2019-10-01  nike    89.550003 
9 2019-11-01  nike    93.489998 
Joining Data with pandas

Using .query() to select text

stocks_long.query('stock=="disney" or (stock=="nike" and close < 90)')
  date        stock   close     
0 2019-07-01  disney  143.009995
1 2019-08-01  disney  137.259995
2 2019-09-01  disney  130.320007
3 2019-10-01  disney  129.919998
4 2019-11-01  disney  151.580002
5 2019-07-01  nike    86.029999 
6 2019-08-01  nike    84.5      
8 2019-10-01  nike    89.550003
Joining Data with pandas

Let's practice!

Joining Data with pandas

Preparing Video For Download...