How to implement a similar function to the mysql lookup statement in PANDAS

select * from table where column_name = some_value;Copy the code

There are several methods for retrieving data in PANDAS:

  • Boolean indexing
  • Location index
  • The label index
  • Use the API

Assume the following data:

import pandas as pd
import numpy as np

df = pd.DataFrame({'A': 'foo bar foo bar foo bar foo foo'.split(),
                   'B': 'one one two three two two one three'.split(),
                   'C': np.arange(8), 'D': np.arange(8) * 2})Copy the code

Boolean indexing

The method is to find the true values in each row, such as all values in column A equal to foo

Df [df['A'] == 'foo'] #Copy the code

Location index

Use the ILOC method to find data based on the index position. This example needs to find the position of the eligible row

Mask = df['A'] == 'foo' pos = np. Flatnonzero (mask) # return array([0, 2, 4, 6, 7]) df.iloc[:3,1:3]Copy the code

The label index

If DataFrame columns and columns are labeled, then loC methods are appropriate.

df.set_index('A', append=True, drop=False).xs('foo', Df.index =df['A'] # select column A as row index df.loc['foo', Loc [df['A']=='foo']Copy the code

Use the API

The pd.datafame. Query method is more efficient than the conventional method when the data volume is large.

Df. Query (' A = = "foo" ') # conditions more df. Query (' A = = "foo" | A = = "bar" ')Copy the code

1. Select rows whose column values are equal to scalar values using ==

df.loc[df['column_name'] == some_value]Copy the code

2. Filter out rows whose column values fall within a range using ISIN

Df.loc [df['column_name'].isin(some_values)] # some_values is an iterableCopy the code

Ampersand has precedence over >= or <=, so pay attention to parentheses

df.loc[(df['column_name'] >= A) & (df['column_name'] <= B)]Copy the code

Filter out rows whose column values are not equal to one or more values

Df. Loc [df [' column_name ']! = 'some_value'] df. Loc [~ df [' column_name '] the isin (' some_values)] # ~ invertCopy the code

If you think my article is ok, you can also scan the following QR code to add my wechat account