15

in pandas I want to do: df.groupby('A').filter(lambda x: x.name > 0) - group by column A and then filter groups that have the value of the name non positive. However this canceles the grouping as GroupBy.filter returns DataFrame and thus losing the groupings. I want to do it in this order as it should be less computationaly demanding because filter followed by groupby would walk the DataFrame twice no (first filtering and then grouping)? Also cloning the groups from the grouping (to a dict or something) would lose me the functionality to seamlessly go back to dataframe (like in the example of .filter that you directly get the DataFrame)

Thanks

Example:

   A  B
1 -1  1
2 -1  2
3  0  2
4  1  1
5  1  2

df.groupby('A'):

GroupBy object
-1 : [1, 2]
 0 : [3]
 1 : [4,5]

GroupBy.filter(lambda x: x.name >= 0):

GroupBy object
 0 : [3]
 1 : [4,5]
Péťa Poliak
  • 393
  • 1
  • 3
  • 11

4 Answers4

11

I think the previous answers propose workarounds, which are maybe useful in your case but doesn't answer the question.

You created groups, and you want to throw out or keep some groups based on group statistics THEN perform some group statistics you actually care for on the groups. This should be possible, and useful in many cases, however, it is not possible now as a chained command (as far as I know) only if you use two identical groupbys consequently.

Let's make a case: Groupby reveals some features that are not filterable on an item level basis (so previous filtering is not an option). For example a group sum. The annoyment in filter is, that it returns a dataframe rather than keeping the grouping and allow you to perform further computations on the groups.

Here is an example:

Let's say you want to group by 'C' and filter on the sums of 'A' in the groups (<700), but in the filtered groups you actually care for the std of the groups. If filter would just be a filter on groups, this would work:

df.groupby(['C']).filter(lambda x:x['A'].sum()<700, combine=False).std()

this doesn't work (note the nonexistent combine=False option on filter), what does is this:

df.groupby(['C']).filter(lambda x:x['A'].sum()<700).groupby(['C']).std()

What filter does is actually filter&combine, which follows the split-apply-combine logic.

petsol
  • 111
  • 1
  • 5
4

Let's run some timings.

df = pd.DataFrame({'A':np.random.randint(-10,10,1000000),'B':np.random.random(1000000)})

Test for see of both returns are equal

df1 = df.groupby('A').filter(lambda x: x.name >= 0)
df2 = df[df.A >= 0]

all(df1 == df2)
True

Timings:

%timeit df1 = df.groupby('A').filter(lambda x: x.name >= 0)

607 ms ± 10.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit df2 = df[df.A >= 0]

59.7 ms ± 724 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

@jacquot solutions sees to be 10x faster than grouping then filtering.

Scott Boston
  • 147,308
  • 15
  • 139
  • 187
  • 3
    This isn't exactly what I said. I said filtering 10 groups by the group name is faster than filtering the whole dataset and then grouping the whole dataset as both filtering and grouping (I am *assuming*) are a linear time operation. However if I try your example then `df[df.A >= 0].groupby('A')` is faster than something like `{name:group for name,group in df.groupby('A').groups.items() if name >= 0}` so I guess I don't have to worry about that speed so much. – Péťa Poliak Apr 14 '18 at 16:06
2

Using groupby doesn't actually aggregate the values in any way. It just creates the groupings, so the filter is essentially filtering on the original dataframe. I don't think you're saving time or computation by grouping first unless name is value achieved by applying a function to the groups.

Therefore I'd recommend something like

df.where(df.name > 0).groupby('A')  # now apply some transformation to the groups
David Stevens
  • 835
  • 1
  • 6
  • 15
  • But in order to create the groups you need to walk the whole dataframe no? What I mean is that if you `groupby` you have to check every row and if you filter the dataframe then as well so it takes two iterations over the whole dataframe, but If I filter only the groups I walk once over the whole dataframe but then only over the groups – Péťa Poliak Apr 14 '18 at 13:36
1

I understood the question like petsol and not like Scott, and so the equivalence of the example Scott gave should be: 

df = d.DataFrame({'A':np.random.randint(-10,10,1000000),'B':np.random.random(1000000)})
df1 = df.groupby('A').filter(lambda x: x['A'].mean()>0).groupby('A').count()

while the second method obviously cannot work.

However, before I found here this solution I thought of it simply as: do Groupby objects have a method similar to .iloc for DataFrames?

That way, when you create a Groupby object you can once create a filter by applying a condition with some aggregating function (and store it as a boolean where the indices correspond to each group), and then apply other function just on the requested groups.

Unfortunately, I did not find any useful method or function equivalent to iloc (I checked "nth", "take" and "get_group" but none of them worked).

Yuval Grossman
  • 21
  • 1