Checking if one dataframe column is a subset of another column

user_16237085 0
Viewed 241

I have a dataframe with columns Enrolled_Months and Eligible_Months, described as follows:

month_list1 = [
    [(1, 2018), (2, 2018), (3, 2019)],
    [(7, 2018), (8, 2018), (10, 2018)],
    [(4, 2018), (5, 2018), (7, 2018)],
    [(1, 2019), (2, 2019), (4, 2019)]
]

month_list2 = [
    [(2, 2018), (3, 2019)],
    [(7, 2018), (8, 2018)],
    [(2, 2018), (3, 2019)],
    [(10, 2018), (11, 2019)]
]

EID = [1, 2, 3, 4]

df = pd.DataFrame({
    'EID': EID,
    'Enrolled_Months': month_list1,
    'Eligible_Months': month_list2
})
df

Out[6]: 
   EID                     Enrolled_Months           Eligible_Months
0    1   [(1, 2018), (2, 2018), (3, 2019)]    [(2, 2018), (3, 2019)]
1    2  [(7, 2018), (8, 2018), (10, 2018)]    [(7, 2018), (8, 2018)]
2    3   [(4, 2018), (5, 2018), (7, 2018)]    [(2, 2018), (3, 2019)]
3    4   [(1, 2019), (2, 2019), (4, 2019)]  [(10, 2018), (11, 2019)]

I want to create a new column called Check that is true if Enrolled_Months contains ALL elements of Eligible_Months. My desired output is below:

Out[8]: 
   EID                     Enrolled_Months           Eligible_Months  Check
0    1   [(1, 2018), (2, 2018), (3, 2019)]    [(2, 2018), (3, 2019)]   True
1    2  [(7, 2018), (8, 2018), (10, 2018)]    [(7, 2018), (8, 2018)]   True
2    3   [(4, 2018), (5, 2018), (7, 2018)]    [(2, 2018), (3, 2019)]  False
3    4   [(1, 2019), (2, 2019), (4, 2019)]  [(10, 2018), (11, 2019)]  False

I've tried the following:

df['Check'] = set(df['Eligible_Months']).issubset(df['Enrolled_Months'])

But end up getting the error TypeError: unhashable type: 'list'.

Any thoughts on how I can achieve this?

Side note: the Enrolled_Months data was originally in a much different format, with each month having its own binary column, and a separate Year column specifying the year (really bad design imo). I created the list columns as I thought it would be easier to work with, but let me know if that original format is better for what I want to achieve.

pandas dataframe tuples list python
3 Answers

You can use a few explodes and then eval and any:

df['Check'] = df.explode('Eligible_Months').explode('Enrolled_Months').eval('Enrolled_Months == Eligible_Months').groupby(level=0).any()

Output:

>>> df
   EID                     Enrolled_Months           Eligible_Months  Check
0    1   [(1, 2018), (2, 2018), (3, 2019)]    [(2, 2018), (3, 2019)]   True
1    2  [(7, 2018), (8, 2018), (10, 2018)]    [(7, 2018), (8, 2018)]   True
2    3   [(4, 2018), (5, 2018), (7, 2018)]    [(2, 2018), (3, 2019)]  False
3    4   [(1, 2019), (2, 2019), (4, 2019)]  [(10, 2018), (11, 2019)]  False
Admin1

You can use df.apply() to create the new column:

df['Check'] = df.apply(
    lambda row: set(row['Eligible_Months']).issubset(row['Enrolled_Months']), axis=1
)

This outputs:

   EID                     Enrolled_Months           Eligible_Months  Check
0    1   [(1, 2018), (2, 2018), (3, 2019)]    [(2, 2018), (3, 2019)]   True
1    2  [(7, 2018), (8, 2018), (10, 2018)]    [(7, 2018), (8, 2018)]   True
2    3   [(4, 2018), (5, 2018), (7, 2018)]    [(2, 2018), (3, 2019)]  False
3    4   [(1, 2019), (2, 2019), (4, 2019)]  [(10, 2018), (11, 2019)]  False
user_177698150

A list comprehension works fine:

df.assign(check = [set(l).issuperset(r) 
                   for l, r in 
                   zip(df.Enrolled_Months, df.Eligible_Months)])

   EID                     Enrolled_Months           Eligible_Months  check
0    1   [(1, 2018), (2, 2018), (3, 2019)]    [(2, 2018), (3, 2019)]   True
1    2  [(7, 2018), (8, 2018), (10, 2018)]    [(7, 2018), (8, 2018)]   True
2    3   [(4, 2018), (5, 2018), (7, 2018)]    [(2, 2018), (3, 2019)]  False
3    4   [(1, 2019), (2, 2019), (4, 2019)]  [(10, 2018), (11, 2019)]  False
user_71757130
Related