Pandas Lesson 7 Search and Filter a DataFrame by Rows

  1. Create a sample DataFrame for this section
  2. Filter DataFrame by column values (Returns a DataFrame)
  3. Filter DataFrame rows by column values:equals a value
  4. Filter DataFrame rows by column values:doesn’t equal a value
  5. Filter DataFramerows by column valuesis in a list of values
  6. Filter DataFrame rows by column valuesis not in a list of values
  7. Filter DataFramerows by column valuesis null
  8. Filter DataFrame rows by column valuesis not null
  9. Filter DataFrame rows using query (Returns a DataFrame)
  10. Filter DataFrame rows using iloc
  11. Filter DataFramerows using loc

1) Create the Sample DataFrame:

import pandas as pd

weather = [{"City":"Houston","Monday":99,"Tuesday":90,"Wednesday":100},
           {"City":"Dallas","Monday":110,"Tuesday":90,"Wednesday":99},
           {"City":"El Paso","Monday":99,"Tuesday":100,"Wednesday":90}]
data = pd.DataFrame(weather)
data

2) Filter DataFrame rows by column values (equals a value)

d1 = data[data["City"] == "Houston"]
d1
Notebook Output

3) Filter DataFrame rows by column values (doesn’t equal a value)

d2 = data[data["City"] != "Houston"]
d2
Notebook Output

4) Filter DataFrame rows by column values (is in a list of values)

d3 = data[data["City"].isin(["Houston","El Paso"])]
d3
Notebook Output

5) Filter DataFrame rows by column values (is not in a list of values)

d4 = data[~data["City"].isin(["Houston","El Paso"])]
d4
Notebook Output

6) Filter a DataFrame row with some null values for the next 2 methods:

The next 2 methods test if a row has null values.

import numpy as np
newrow = [{"City":"Galveston","Monday":99,"Tuesday":np.nan,"Wednesday":90}]
datanew = pd.DataFrame.from_dict(newrow)
data = data.append(datanew,ignore_index=True)
data
Notebook Output

7) Filter DataFrame rows by column values (is not null)

d5 = data[data["Tuesday"].notnull()]
d5
Notebook Output

8) Filter DataFrame rows by column values (is null)

d6 = data[data["Tuesday"].isnull()]
d6
Notebook Output

9) Filter DataFrame using the Query method to filter

db = data.query('City == "Houston"')
print(db)
dc = data.query('Tuesday == 100')
print(dc)
Notebook Output

10) Filter DataFrame rows using iloc (filter by index)

#single value returns a series
d7 = data.iloc[1]
d7
Notebook Output
#multiple values return a dataframe (supports slice notation)
d8 = data.iloc[1:3]
d8
Notebook Output

11) Filter DataFrame rows using loc

To use .loc in this Dataframe set the “City ” column as the index.

data = data.set_index("City")
data
Notebook Output
#Returns a Series (for a single row result)
d9 = data.loc["Houston"]
d9
Notebook Output
#Returns a DataFrame(for a multiple row results)
d10 = data.loc[["Houston","Dallas"]]
d10
Notebook Output