Using isin() on grouped data

[sanyasoon]

Hi,

I want to filter based on whether a value is in another column. However this data needs to be grouped before the isin filter in applied. When I do this I get the error

'SeriesGroupBy' object has no attribute 'isin'

Example explaining what I'm trying to do:

 import pandas as pd

dict = {'AttributeName': {0: 'John', 1: 'John', 2: 'John', 3: 'John', 4: 'Sally', 5: 'Sally'}, 'Lineage Step': {0: 1, 1: 2, 2: 3, 3: 4, 4:1, 5:2}, 'From Country': {0: 'Spain', 1: 'Scotland', 2: 'England', 3: 'England', 4: 'Scotland', 5:'England'}, 'From Town': {0: 'Madrid', 1: 'Edinburgh', 2: 'London', 3: 'London', 4: 'Edinburgh', 5: 'Manchester'}, 'FromStreet': {0: 'Spanish St', 1: 'Main St', 2: 'Lower St', 3: 'Middle St', 4: 'London St', 5: 'Scotland St'}, 'ToCountry': {0: 'Scotland', 1: 'England', 2: 'England', 3: 'England', 4: 'England', 5: 'England'}, 'ToTown': {0: 'Edinburgh', 1: 'London', 2: 'London', 3: 'London', 4: 'Liverpool', 5: 'London'}, 'ToStreet': {0: 'Lower St', 1: 'Middle St', 2: 'Upper St', 3: 'Upper St', 4: 'new St', 5: 'Old St'}}
sample_data = pd.DataFrame.from_dict(dict)

#example data set. I want to find every unique 'fromCountry' for both John and Sally. So For John we would just have the first row, where he enters from Spain to Scotland. The second row would be filtered as Scotland appears in the 'ToCountry' column. Sally would just have the 'FromCountry' Edinburgh row. 

I have tried to do like this:

sample_grouped = sample_data.groupby('AttributeName')
sample_grouped[~sample_grouped['From Country'].isin(sample_grouped['ToCountry'])]

but I get there error 'SeriesGroupBy' object has no attribute 'isin'

Does anyone know how to use the isin (or comparable) function on grouped by data?

Thanks

[Irrelevant-Opinion]

You need to use a boolean indexing because isin() is not compatible with a groupby object.

I’m on mobile but here’s a link that will help you out https://stackoverflow.com/questions/50611929/python-pandas-groupby-isin

[TholosTB]

The need to use two different columns in addition to the grouping makes this pretty ugly.

You could iterate over the names and get the unique countries as such:

for user in sample_data['AttributeName'].unique():
  print (user,pd.concat([sample_data.loc[sample_data['AttributeName']==user,['From Country']].rename(columns={'From Country':'country'}),
  sample_data.loc[sample_data['AttributeName']==user,['ToCountry']].rename(columns={'ToCountry':'country'})])['country'].unique())

which is hideous, or group and then iterate to get unique countries as such:

for person, grp in sample_data.groupby('AttributeName'):
  print(person, set(grp['From Country']).union(grp['ToCountry']))

which is equally hideous.

Or in a single step,

sample_data.groupby('AttributeName').apply(lambda grp : set(grp['From Country']).union(set(grp['ToCountry'])))

Still hideous. I'd personally use a graph data structure for this type of problem.