Filtering DataFrames:

  • Pandas has a string-based syntax through the DataFrame .query() method that allows for clear and concise filtering of DataFrames.
  • Strings passed to the .query() method look more like plain English than native Python code.
    • For instance, consider we wanted to identify the days in 2018 when Ithaca, NY experienced a snowfall of exactly 1.0 inch.
      • After reading the data into a DataFrame, we can first identify the data types of the columns, then use .unique() to identify the unique values in the Snowfall column.
python
1import pandas as pd
2
3df = pd.read_csv('./data/IthacaDailyClimate2018.csv')
4
5print(df.dtypes)
6print("----------")  # Separator for better readability
7print(df['Snowfall'].unique())

Output:

1Date                    object
2Maximum Temperature      int64
3Minimum Temperature      int64
4Average Temperature    float64
5Precipitation          float64
6Snowfall               float64
7Snow Depth             float64
8dtype: object
9----------
10[1.  0.6 0.  1.2 6.  ... 8.6 1.9 2.1 3.9 2. ]
  • Next, the .query() method is used to filter the DataFrame to only include the rows where the Snowfall column is equal to 1.0.
  • Since Snowfall is a float, 1.0 is not enclosed in additional quotes.
python
9df_snowfall_1inch = df.query('Snowfall == 1.0')
10print(df_snowfall_1inch)

Output (some columns hidden here for readability — the real output has 7):

1           Date  Maximum Temperature  ...  Snowfall  Snow Depth
20    2018-01-01                    5  ...       1.0         3.0
332   2018-02-02                   45  ...       1.0         1.0
435   2018-02-05                   31  ...       1.0         2.0
540   2018-02-10                   35  ...       1.0         5.0
667   2018-03-09                   32  ...       1.0         2.0
7357  2018-12-24                   33  ...       1.0         2.0
8
9[6 rows x 7 columns]

Filtering on Multiple Values:

  • The .query() method can also be used to filter on multiple values.
  • For example, imagine we want to identify the days in 2018 when Ithaca, NY experienced a snowfall of either 1.0, 2.0, or 6.0 inches.
    • To do so, we need to define a variable that hold the list of values we want to filter by.
    • Then, we can use the @ symbol in the .query() method for filtering with the in option.
python
9snowfall_values = [1.0, 2.0, 6.0]
10
11df_snowfall_specific = df.query('Snowfall in @snowfall_values')
12print(df_snowfall_specific)
  • Note that because Snowfall is a float, the values in the list are not enclosed in quotes.

Output (some columns hidden here for readability — the real output has 7):

1           Date  Maximum Temperature  ...  Snowfall  Snow Depth
20    2018-01-01                    5  ...       1.0         3.0
312   2018-01-13                   62  ...       6.0         6.0
432   2018-02-02                   45  ...       1.0         1.0
535   2018-02-05                   31  ...       1.0         2.0
640   2018-02-10                   35  ...       1.0         5.0
767   2018-03-09                   32  ...       1.0         2.0
8355  2018-12-22                   61  ...       2.0         2.0
9357  2018-12-24                   33  ...       1.0         2.0
10
11[8 rows x 7 columns]