Using Pandas all() or any()

This is the place for queries that don't fit in any of the other categories.

Using Pandas all() or any()

Postby tnknepp » Thu Aug 15, 2013 1:10 pm

For the life of me I cannot figure out how to us the any() or all() functions on a Pandas data frame. The help under all/any does not provide an example, so I am left wandering in the desert.

Code: Select all
import pandas as pd
from magic_square import magic as ms

# Create my data frame
a = pd.DataFrame(ms(10),columns=list('ABCDEFGHIJ'))

>>> a
    A   B    C   D   E   F   G   H   I   J
0  92  99    1   8  15  67  74  51  58  40
1  98  80    7  14  16  73  55  57  64  41
2   4  81   88  20  22  54  56  63  70  47
3  85  87   19  21   3  60  62  69  71  28
4  86  93   25   2   9  61  68  75  52  34
5  17  24   76  83  90  42  49  26  33  65
6  23   5   82  89  91  48  30  32  39  66
7  79   6   13  95  97  29  31  38  45  72
8  10  12   94  96  78  35  37  44  46  53
9  11  18  100  77  84  36  43  50  27  59

# Select only rows where A>85
>>> a[a.A>85]
    A   B   C   D   E   F   G   H   I   J
0  92  99   1   8  15  67  74  51  58  40
1  98  80   7  14  16  73  55  57  64  41
4  86  93  25   2   9  61  68  75  52  34

# Select rows where A>85 or A<20
a[a.A>85 or a.A<20]  -> Wrong, I get directed to use a.any() or a.all()
a[all(a.A>85 or a.A<20)]  -> Wrong again


What I want to do is extract data under the condition, e.g., 5 < x < 10, but cannot figure out how to do it.
Python: 2.7 via Anaconda
Numpy: 1.7
Pandas: 0.11
OS: Windows 7
IDE: Spyder/IPython
User avatar
tnknepp
 
Posts: 119
Joined: Mon Mar 11, 2013 7:41 pm

Re: Using Pandas all() or any()

Postby stranac » Thu Aug 15, 2013 2:17 pm

You need to use a bitwise or on the booolean arrays the weird numpy indexing thingy(whatever it's called) gives you:
Code: Select all
>>> a[(a.A > 85) | (a.A < 20)]
    A   B    C   D   E   F   G   H   I   J
0  92  99    1   8  15  67  74  51  58  40
1  98  80    7  14  16  73  55  57  64  41
2   4  81   88  20  22  54  56  63  70  47
4  86  93   25   2   9  61  68  75  52  34
5  17  24   76  83  90  42  49  26  33  65
8  10  12   94  96  78  35  37  44  46  53
9  11  18  100  77  84  36  43  50  27  59
Friendship is magic!

R.I.P. Tracy M. You will be missed.
User avatar
stranac
 
Posts: 1096
Joined: Thu Feb 07, 2013 3:42 pm

Re: Using Pandas all() or any()

Postby tnknepp » Thu Aug 15, 2013 2:30 pm

Great! Thanks for the solution. After seeing this, it does seem logical :lol: (pun intended).
Python: 2.7 via Anaconda
Numpy: 1.7
Pandas: 0.11
OS: Windows 7
IDE: Spyder/IPython
User avatar
tnknepp
 
Posts: 119
Joined: Mon Mar 11, 2013 7:41 pm

Re: Using Pandas all() or any()

Postby Mekire » Thu Aug 15, 2013 2:30 pm

I found I could use the panda.concat function (as I fought with the bitwise or and kept screwing it up). It seems to accomplish similar but the order of the axis gets screwed up:

Code: Select all
pieces = a[a.A>85],a[a.A<20]
concatenated = pd.concat(pieces)

print(concatenated)
Code: Select all
>>>
    A   B    C   D   E   F   G   H   I   J
0  92  99    1   8  15  67  74  51  58  40
1  98  80    7  14  16  73  55  57  64  41
4  86  93   25   2   9  61  68  75  52  34
2   4  81   88  20  22  54  56  63  70  47
5  17  24   76  83  90  42  49  26  33  65
8  10  12   94  96  78  35  37  44  46  53
9  11  18  100  77  84  36  43  50  27  59
>>>
Not sure how to then sort the axes.

-Mek

Edit: Figured out how to sort, though Stranac's suggestion is certainly better.
Code: Select all
pieces = a[a.A>85],a[a.A<20]
concatenated = pd.concat(pieces)
concatenated.sort(axis=0,inplace=True)

print(concatenated)
Code: Select all
>>>
    A   B    C   D   E   F   G   H   I   J
0  92  99    1   8  15  67  74  51  58  40
1  98  80    7  14  16  73  55  57  64  41
2   4  81   88  20  22  54  56  63  70  47
4  86  93   25   2   9  61  68  75  52  34
5  17  24   76  83  90  42  49  26  33  65
8  10  12   94  96  78  35  37  44  46  53
9  11  18  100  77  84  36  43  50  27  59
>>>
User avatar
Mekire
 
Posts: 986
Joined: Thu Feb 07, 2013 11:33 pm
Location: Amakusa, Japan

Re: Using Pandas all() or any()

Postby tnknepp » Thu Aug 15, 2013 7:47 pm

Thanks Mek. This works, but I was definitely looking for a one-liner here.
Python: 2.7 via Anaconda
Numpy: 1.7
Pandas: 0.11
OS: Windows 7
IDE: Spyder/IPython
User avatar
tnknepp
 
Posts: 119
Joined: Mon Mar 11, 2013 7:41 pm

Re: Using Pandas all() or any()

Postby Amyunimus » Thu Jan 16, 2014 1:53 am

I'm also stuck with a.any() and a.all(). How would you specify the condition 'x in [22,33,15]' or 'x not in [100, 201, 1000, 679... etc]' if you want to selectively include or exclude based on another list?
Last edited by Mekire on Thu Jan 16, 2014 2:10 am, edited 1 time in total.
Reason: First post lock.
Amyunimus
 
Posts: 1
Joined: Thu Jan 16, 2014 1:49 am

Re: Using Pandas all() or any()

Postby tnknepp » Tue Feb 04, 2014 2:04 pm

Amyunimus wrote:I'm also stuck with a.any() and a.all(). How would you specify the condition 'x in [22,33,15]' or 'x not in [100, 201, 1000, 679... etc]' if you want to selectively include or exclude based on another list?


I've been out for a while, so you may have this answered already. It is not clear what you are trying to do here. Can you open a new thread and elucidate?
Python: 2.7 via Anaconda
Numpy: 1.7
Pandas: 0.11
OS: Windows 7
IDE: Spyder/IPython
User avatar
tnknepp
 
Posts: 119
Joined: Mon Mar 11, 2013 7:41 pm


Return to General Coding Help

Who is online

Users browsing this forum: Bing [Bot], Majestic-12 [Bot] and 3 guests