By bigbug


2012-08-22 03:16:56 8 Comments

I have a Python pandas DataFrame rpt:

rpt
<class 'pandas.core.frame.DataFrame'>
MultiIndex: 47518 entries, ('000002', '20120331') to ('603366', '20091231')
Data columns:
STK_ID                    47518  non-null values
STK_Name                  47518  non-null values
RPT_Date                  47518  non-null values
sales                     47518  non-null values

I can filter the rows whose stock id is '600809' like this: rpt[rpt['STK_ID'] == '600809']

<class 'pandas.core.frame.DataFrame'>
MultiIndex: 25 entries, ('600809', '20120331') to ('600809', '20060331')
Data columns:
STK_ID                    25  non-null values
STK_Name                  25  non-null values
RPT_Date                  25  non-null values
sales                     25  non-null values

and I want to get all the rows of some stocks together, such as ['600809','600141','600329']. That means I want a syntax like this:

stk_list = ['600809','600141','600329']

rst = rpt[rpt['STK_ID'] in stk_list] # this does not works in pandas 

Since pandas not accept above command, how to achieve the target?

7 comments

@akuriako 2017-09-28 03:04:39

You can also achieve similar results by using 'query' and @:

eg:

df = pd.DataFrame({'A': [1, 2, 3], 'B': ['a', 'b', 'f']})
df = pd.DataFrame({'A' : [5,6,3,4], 'B' : [1,2,3, 5]})
list_of_values = [3,6]
result= df.query("A in @list_of_values")
result
   A  B
1  6  2
2  3  3

@firelynx 2017-04-27 13:24:17

Slicing data with pandas

Given a dataframe like this:

    RPT_Date  STK_ID STK_Name  sales
0 1980-01-01       0   Arthur      0
1 1980-01-02       1    Beate      4
2 1980-01-03       2    Cecil      2
3 1980-01-04       3     Dana      8
4 1980-01-05       4     Eric      4
5 1980-01-06       5    Fidel      5
6 1980-01-07       6   George      4
7 1980-01-08       7     Hans      7
8 1980-01-09       8   Ingrid      7
9 1980-01-10       9    Jones      4

There are multiple ways of selecting or slicing the data.

Using .isin

The most obvious is the .isin feature. You can create a mask that gives you a series of True/False statements, which can be applied to a dataframe like this:

mask = df['STK_ID'].isin([4, 2, 6])

mask
0    False
1    False
2     True
3    False
4     True
5    False
6     True
7    False
8    False
9    False
Name: STK_ID, dtype: bool

df[mask]
    RPT_Date  STK_ID STK_Name  sales
2 1980-01-03       2    Cecil      2
4 1980-01-05       4     Eric      4
6 1980-01-07       6   George      4

Masking is the ad-hoc solution to the problem, but does not always perform well in terms of speed and memory.

With indexing

By setting the index to the STK_ID column, we can use the pandas builtin slicing object .loc

df.set_index('STK_ID', inplace=True)
         RPT_Date STK_Name  sales
STK_ID                           
0      1980-01-01   Arthur      0
1      1980-01-02    Beate      4
2      1980-01-03    Cecil      2
3      1980-01-04     Dana      8
4      1980-01-05     Eric      4
5      1980-01-06    Fidel      5
6      1980-01-07   George      4
7      1980-01-08     Hans      7
8      1980-01-09   Ingrid      7
9      1980-01-10    Jones      4

df.loc[[4, 2, 6]]
         RPT_Date STK_Name  sales
STK_ID                           
4      1980-01-05     Eric      4
2      1980-01-03    Cecil      2
6      1980-01-07   George      4

This is the fast way of doing it, even if the indexing can take a little while, it saves time if you want to do multiple queries like this.

Merging dataframes

This can also be done by merging dataframes. This would fit more for a scenario where you have a lot more data than in these examples.

stkid_df = pd.DataFrame({"STK_ID": [4,2,6]})
df.merge(stkid_df, on='STK_ID')
   STK_ID   RPT_Date STK_Name  sales
0       2 1980-01-03    Cecil      2
1       4 1980-01-05     Eric      4
2       6 1980-01-07   George      4

Note

All the above methods work even if there are multiple rows with the same 'STK_ID'

@Pedro Lobito 2017-04-26 20:09:12

You can use query, i.e.:

b = df.query('a > 1 & a < 5')

@bscan 2015-03-17 20:12:10

You can also directly query your DataFrame for this information.

rpt.query('STK_ID in (600809,600141,600329)')

Or similarly search for ranges:

rpt.query('60000 < STK_ID < 70000')

@Gourneau 2016-10-19 23:50:44

or to query by a list named my_list rpt.query('STK_ID in @my_list')

@Alex Riley 2014-11-03 22:49:27

isin() is ideal if you have a list of exact matches, but if you have a list of partial matches or substrings to look for, you can filter using the str.contains method and regular expressions.

For example, if we want to return a DataFrame where all of the stock IDs which begin with '600' and then are followed by any three digits:

>>> rpt[rpt['STK_ID'].str.contains(r'^600[0-9]{3}$')] # ^ means start of string
...   STK_ID   ...                                    # [0-9]{3} means any three digits
...  '600809'  ...                                    # $ means end of string
...  '600141'  ...
...  '600329'  ...
...      ...   ...

Suppose now we have a list of strings which we want the values in 'STK_ID' to end with, e.g.

endstrings = ['01$', '02$', '05$']

We can join these strings with the regex 'or' character | and pass the string to str.contains to filter the DataFrame:

>>> rpt[rpt['STK_ID'].str.contains('|'.join(endstrings)]
...   STK_ID   ...
...  '155905'  ...
...  '633101'  ...
...  '210302'  ...
...      ...   ...

Finally, contains can ignore case (by setting case=False), allowing you to be more general when specifying the strings you want to match.

For example,

str.contains('pandas', case=False)

would match PANDAS, PanDAs, paNdAs123, and so on.

@alpha_989 2018-07-05 19:44:21

Thanks for this.. regex search would be very help. Even, though isin only works for perfect matched, it accepts dataframes, Series, Index etc.. @jakevdp provided a great solution here, which works to extract the matching values of df1, given another dataframe df2:stackoverflow.com/a/33282617/4752883. In my case, I have a df2, but the values in df2 wont be exact matches, so I am wondering if there is a way to use regex in isin (or another function), similar to what you pointed out here?

@yemu 2013-10-10 12:26:29

you can also use ranges by using:

b = df[(df['a'] > 1) & (df['a'] < 5)]

@BrenBarn 2012-08-22 03:21:12

Use the isin method. rpt[rpt['STK_ID'].isin(stk_list)].

@stites 2013-06-26 15:14:57

what about the negation of this- what would be the correct way of going about a !isin()?

@BrenBarn 2013-06-26 17:43:00

@dbyte: You just use the ~ operator: rpt[~rpt['STK_ID'].isin(stk_list)]

@mathtick 2014-02-11 16:51:14

Is there a direct way to do this on one level of a multiindex (i.e. without the reset_index?)

@user1669710 2015-02-27 22:11:48

Related to what @mathtick asked: is there a way to do this on an index in general (needn't necessarily be a multindex)?

@BrenBarn 2015-02-27 22:21:58

@user1669710: Indexes also have an isin method.

@howMuchCheeseIsTooMuchCheese 2015-09-16 18:32:40

In case anyone needs the syntax for an index: df[df.index.isin(ls)] where ls is your list

@toasteez 2016-02-08 10:00:14

@stites did you manage to find a not isin?

@stites 2016-02-08 17:17:58

@toasteez — yes, I recall the ~ being what I needed.

@Blairg23 2016-06-16 18:36:27

Wow, this saved me so much time. I was originally finding my unique values and then iterating over the list and doing a query on the dataframe for EACH unique value. Took over 12 hours with one of my dataframes! I used isin and it took 36.5 seconds!

@Mark Ginsburg 2017-05-17 19:00:38

isin is LIGHTNING quick. I didn't know it, and was using a lambda function to test "in", and it was sooooooooooo slow.

@Sigur 2017-10-02 16:47:14

How to eliminate possible repeated values?

@oya163 2017-10-21 04:06:32

Suppose the list is [1,2] and I want to get 500 rows of each class. How do I do it?

@BrenBarn 2017-10-21 04:24:15

@oya163: Well, first you ask your own separate question instead of commenting on one that is only slightly related.

@oya163 2017-10-21 04:27:03

@BrenBarn .... ya I just did that. Here is my question stackoverflow.com/questions/46860038/…

Related Questions

Sponsored Content

13 Answered Questions

[SOLVED] How to drop rows of Pandas DataFrame whose value in a certain column is NaN

9 Answered Questions

[SOLVED] Select rows from a DataFrame based on values in a column in pandas

18 Answered Questions

[SOLVED] Add one row to pandas DataFrame

23 Answered Questions

[SOLVED] Adding new column to existing DataFrame in Python pandas

17 Answered Questions

[SOLVED] How to iterate over rows in a DataFrame in Pandas?

18 Answered Questions

[SOLVED] Get list from pandas DataFrame column headers

14 Answered Questions

[SOLVED] "Large data" work flows using pandas

17 Answered Questions

[SOLVED] Selecting multiple columns in a pandas dataframe

14 Answered Questions

[SOLVED] Delete column from pandas DataFrame

19 Answered Questions

[SOLVED] How to sort a dataframe by multiple column(s)

Sponsored Content