Querying a pandas DataFrame

A pandas DataFrame can be queried using many of its methods. They include the methods at(), iat(), head(), tail(), query(), asof(), items(), iteritems() and the properties loc and iloc.

Dataframe Manipulations Updated 20 Aug 2020 4 min read

Overview

  • The DataFrame class of pandas library provides multiple ways to query data from a DataFrame instance.
  • To get a single value from a DataFrame using the indexes and labels, the properties at and iat are used. To get a group of values by specifying the indexes and labels the properties loc and iloc are used.
  • To pop a column from a DataFrame, the method pop() is used.
  • To get the first "n" rows and the last "n" rows of the DataFrame, the head() and tail() methods are used.
  • To query the data based on Boolean expressions(including specific customizations) methods like query() and asof() are used.
  • To iterate over the DataFrame, the method items(), iteritems() are used.

Example 1 - Querying a single value from a pandas DataFrame using indices and labels

python
# Example Python program to query a value

            # using the index

            import pandas as pds

# Create a DataFrame

            lattice = [(-1, -1, -1),

                        (-1, 0, -1),

                        (-1, -1, -10)];

            df = pds.DataFrame(lattice);

            print("DataFrame:");

            print(df);

# Add index and column labels

            df.index     = ["r1", "r2", "r3"];

            df.columns     = ["c1", "c2", "c3"];

# Query the value from a location using the indices

            row = 1;

            col = 1;

            val = df.iat[row, col];

            print("Value at the indexes %d,%d:"%(row, col));

            print(val);

# Query the value from a location using labels

            row = "r3";

            col = "c3";

            val = df.at[row, col];

            print("Value at the labels %s,%s:"%(row, col));

            print(val);

Output

Output
DataFrame:

               0  1   2

            0 -1 -1  -1

            1 -1  0  -1

            2 -1 -1 -10

            Value at the indexes 1,1:

            0

            Value at the labels r3,c3:

            -10

Example 2 - Querying a group of values from a pandas DataFrame using indices and labels

python
# Example Python program that queries

            # a group of values using the indexes and labels

            import pandas as pds

tokens = [("ab", "bc", "cd"),

                      ("de", "ef", "fg"),

                      ("gh", "hi", "ij")];

            df = pds.DataFrame(data = tokens);

df.index     = ["r1", "r2", "r3"];

            df.columns     = ["x", "y", "z"];

            print("DataFrame:");

            print(df);

# Query different groups of values using indices

            r1 = 0;

            r2 = 0;

            c1 = 1;

            c2 = 1;

            print("Query results using indices [%d, %d], [%d, %d]:"%(r1, r2, c1, c2));

            print(df.iloc[[r1,r2], [c1,c2]]);

r1 = 0;

            r2 = 1;

            c1 = 1;

            c2 = 2;

            print("Query results using indices [%d, %d], [%d, %d]:"%(r1, r2, c1, c2));

            print(df.iloc[[0,1], [1,2]]);

# Query different groups of values using labels

            r1 = "r1";

            r2 = "r2";

            c1 = "x";

            c2 = "z";

            print("Query results using labels [\"%s\", \"%s\"], [\"%s\", \"%s\"]:"%(r1, r2, c1, c2));

            print(df.loc[["r1","r2"], ["x","z"]]);

Output

Output
DataFrame:

                 x   y   z

            r1  ab  bc  cd

            r2  de  ef  fg

            r3  gh  hi  ij

            Query results using indices [0, 0], [1, 1]:

                 y   y

            r1  bc  bc

            r1  bc  bc

            Query results using indices [0, 1], [1, 2]:

                 y   z

            r1  bc  cd

            r2  ef  fg

            Query results using labels ["r1", "r2"], ["x", "z"]:

                 x   z

            r1  ab  cd

            r2  de  fg

Example 3 - Pop a column from a DataFrame

python
# Example Python program that pops a value

            # from a DataFrame

            import pandas as pds

# Create a pandas DataFrame

            vals = [("I","II","III"),

                    ("IV","V","VI"),

                    ("VII","VIII","IX")];

df = pds.DataFrame(data=vals);

            df.columns = ["c1", "c2", "c3"];

            df.index = ["r1", "r2", "r3"];

            print("DataFrame:");

            print(df);

# Remove a column

            popped = df.pop("c2");

            print("Removed column:");

            print(popped);

            print(type(popped))

print("DataFrame after popping a column:");

            print(df);

Output

Output
DataFrame:

                 x   y   z

            r1  ab  bc  cd

            r2  de  ef  fg

            r3  gh  hi  ij

            Query results using indices [0, 0], [1, 1]:

                 y   y

            r1  bc  bc

            r1  bc  bc

            Query results using indices [0, 1], [1, 2]:

                 y   z

            r1  bc  cd

            r2  ef  fg

            Query results using labels ["r1", "r2"], ["x", "z"]:

                 x   z

            r1  ab  cd

            r2  de  fg

Example 4 - Querying the DataFrame using a Boolean expression

  • The query() method accepts a string containing a Boolean expression. Typically a column name is used against a value or a variable containing a value. To refer to a variable inside the Boolean expression, the symbol @ is prepended to the variable name.
python
# Example Python program that queries

            # a pandas DataFrame using a Boolean

            # expression

            import pandas as pds

fractions = [(1.1, 1.2, 1.3),

                           (1.4, 1.5, 1.6),

                           (1.7, 1.8, 1.9)];

# Create a DataFrame

            df = pds.DataFrame(data=fractions,

                               columns=["c1", "c2", "c3"],

                               index = ["r1", "r2", "r3"]);

print("DataFrame:");

            print(df);

# Query the rows where a column value exceeds a threshold

            threshold = 1.1;

            queryResults = df.query("c1 > @threshold");

            print("Query results:");

            print(queryResults);

Output

Output
DataFrame:

                 c1   c2   c3

            r1  1.1  1.2  1.3

            r2  1.4  1.5  1.6

            r3  1.7  1.8  1.9

            Query results:

                 c1   c2   c3

            r2  1.4  1.5  1.6

            r3  1.7  1.8  1.9

Example 5 - Querying DateTime indexed DataFrame for Non-None rows

python
# Example python program that queries

            # non-none rows of a DataFrame using DateTimeIndex

            import pandas as pds

# Prices of commodities

            prices = {"Commodity 1": [110.1, None, 109.6, 105.5, 104.2, 107.0],

                      "Commodity 2": [250.1, 250.0, 245.1, None, 249.5, 252.5],

                      "Commodity 3": [310.1, 320.0, 312.5, None, 315.2, 309.1]

                     }

# Create a pandas DataFrame

            df = pds.DataFrame(data = prices);

            dateTimes = ['2020-08-05 12:31:10',

                         '2020-08-05 12:32:11',

                         '2020-08-05 12:33:12',

                         '2020-08-05 12:34:13',

                         '2020-08-05 12:35:14',

                         '2020-08-05 12:35:15',

                         ];

# Create an index of DateTime instances

            dIndex = pds.DatetimeIndex(dateTimes);

# Attach the index to the DataFrame

            df.index = dIndex;

            print(df);

# Query the rows that has non-none values between

            # t0 and t4

            df1 = df.asof(pds.DatetimeIndex(['2020-08-05 12:31:10',

                                                '2020-08-05 12:35:14']));

            # Print the query results

            print(df1);

Output

Output
                                      Commodity 1  Commodity 2  Commodity 3

            2020-08-05 12:31:10        110.1        250.1        310.1

            2020-08-05 12:32:11          NaN        250.0        320.0

            2020-08-05 12:33:12        109.6        245.1        312.5

            2020-08-05 12:34:13        105.5          NaN          NaN

            2020-08-05 12:35:14        104.2        249.5        315.2

            2020-08-05 12:35:15        107.0        252.5        309.1

                                 Commodity 1  Commodity 2  Commodity 3

            2020-08-05 12:31:10        110.1        250.1        310.1

            2020-08-05 12:35:14        104.2        249.5        315.2