EN VI

Python - Pandas select rows with values are present for all elements in array?

How to Python - Pandas select rows with values are present for all elements in array

Firstly the title of the post may not do justice to the question, so my humble apologies for this.

Here is the question:

Date	Type	Value
2024-03-11	3	3
2024-3-11	4	5
2024-03-12	3	3
2024-3-12	4	5
2024-3-12	5	5
2024-03-13	3	3
2024-3-13	4	5
2024-3-13	5	2
2024-3-14	5	5

Type = [3,4,5]

Is there a simple way for me to, in Pandas, create a new DF from the above one where the data is only present if the date has values for all the elements in the list ? Meaning the reusltant DF should only contain data for date 12,13 since the original DF has values for elements in the Type array ? Thanks

Solution:

Use a set, aggregate with issuperset:

Type = {3,4,5}

df['Date'] = pd.to_datetime(df['Date'])

keep = df.groupby('Date')['Type'].agg(Type.issubset)

out = df[df['Date'].isin(keep.index[keep])]

Variant:

Type = {3,4,5}

df['Date'] = pd.to_datetime(df['Date'])

out = df[df.groupby('Date')['Type'].transform(Type.issubset)]

Output:

        Date  Type  Value
2 2024-03-12     3      3
3 2024-03-12     4      5
4 2024-03-12     5      5
5 2024-03-13     3      3
6 2024-03-13     4      5
7 2024-03-13     5      2

Tags