索引具有多个条件的Python Pandas数据帧SQL where where语句 [英] index a Python Pandas dataframe with multiple conditions SQL like where statement

问题描述

我在R和Python Pandas的新手中很有经验。我试图索引一个DataFrame来检索满足一组几个逻辑条件的行 - 很像SQL的where语句。

I am experienced in R and new to Python Pandas. I am trying to index a DataFrame to retrieve rows that meet a set of several logical conditions - much like the "where" statement of SQL.

我知道如何在R中使用数据帧（以及使用R的data.table包，这更像是Pandas DataFrame而不是R的本机数据帧）。

I know how to do this in R with dataframes (and with R's data.table package, which is more like a Pandas DataFrame than R's native dataframe).

下面是一些构建DataFrame的示例代码以及我想如何为其编制索引的说明。有没有简单的方法呢？

Here's some sample code that constructs a DataFrame and a description of how I would like to index it. Is there an easy way to do this?

import pandas as pd
import numpy as np

# generate some data
mult = 10000
fruits = ['Apple', 'Banana', 'Kiwi', 'Grape', 'Orange', 'Strawberry']*mult
vegetables = ['Asparagus', 'Broccoli', 'Carrot', 'Lettuce', 'Rutabaga', 'Spinach']*mult
animals = ['Dog', 'Cat', 'Bird', 'Fish', 'Lion', 'Mouse']*mult
xValues = np.random.normal(loc=80, scale=2, size=6*mult)
yValues = np.random.normal(loc=79, scale=2, size=6*mult)

data = {'Fruit': fruits,
        'Vegetable': vegetables, 
        'Animal': animals, 
        'xValue': xValues,
        'yValue': yValues,}

df = pd.DataFrame(data)

# shuffle the columns to break structure of repeating fruits, vegetables, animals
np.random.shuffle(df.Fruit)
np.random.shuffle(df.Vegetable)
np.random.shuffle(df.Animal)

df.head(30)

# filter sets
fruitsInclude = ['Apple', 'Banana', 'Grape']
vegetablesExclude = ['Asparagus', 'Broccoli']

# subset1:  All rows and columns where:
#   (fruit in fruitsInclude) AND (Vegetable not in vegetablesExlude)

# subset2:  All rows and columns where:
#   (fruit in fruitsInclude) AND [(Vegetable not in vegetablesExlude) OR (Animal == 'Dog')]

# subset3:  All rows and specific columns where above logical conditions are true.

欢迎并高度赞赏所有帮助和输入！

All help and inputs welcomed and highly appreciated!

谢谢，
Randall

Thanks, Randall

推荐答案

# subset1:  All rows and columns where:
#   (fruit in fruitsInclude) AND (Vegetable not in vegetablesExlude)
df.ix[df['Fruit'].isin(fruitsInclude) & ~df['Vegetable'].isin(vegetablesExclude)]

# subset2:  All rows and columns where:
#   (fruit in fruitsInclude) AND [(Vegetable not in vegetablesExlude) OR (Animal == 'Dog')]
df.ix[df['Fruit'].isin(fruitsInclude) & (~df['Vegetable'].isin(vegetablesExclude) | (df['Animal']=='Dog'))]

# subset3:  All rows and specific columns where above logical conditions are true.
df.ix[df['Fruit'].isin(fruitsInclude) & ~df['Vegetable'].isin(vegetablesExclude) & (df['Animal']=='Dog')]

这篇关于索引具有多个条件的Python Pandas数据帧SQL where where语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！