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

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

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

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

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

Let's practice!

Joining Data with pandas