How do I use python or pandas to filter a DataFrame based on a column that consists of a list of dictionary?

user_14303278 0
Viewed 44

I have a DataFrame and one of the columns is a list of dictionaries. I need to apply conditions on the column containing the list of dictionary and get a boolean value. The condition includes a DateTime format.

lastDate = pd.to_datetime(date.today().replace(day=1) - timedelta(1))

Output is: Timestamp('2020-10-31 00:00:00')

DataFrame,

ID    Name     Status
1     Rav      [{'status': 'SIGNUP', 'Date': datetime.datetime(2020, 10, 30, 2, 43, 18, 578000)}, {'status': 'REG_COMPLETED', 'Date': datetime.datetime(2020, 10, 30, 2, 55, 52, 120000)}]
2     Suv      [{'status': 'SIGNUP', 'Date': datetime.datetime(2020, 11, 01, 2, 43, 18, 578000)}, {'status': 'REG_COMPLETED', 'Date': datetime.datetime(2020, 11, 01, 2, 55, 52, 120000)}]
3     Tim      [{'status': 'SIGNUP', 'Date': datetime.datetime(2020, 11, 01, 2, 43, 18, 578000)}, {'status': 'REG_COMPLETED', 'Date': datetime.datetime(2020, 11, 01, 2, 55, 52, 120000)}]
4     Jaq      [{'status': 'SIGNUP', 'Date': datetime.datetime(2020, 11, 01, 2, 43, 18, 578000)}, {'status': 'REG_COMPLETED', 'Date': datetime.datetime(2020, 11, 01, 2, 55, 52, 120000)}]
5     Tan      [{'status': 'SIGNUP', 'Date': datetime.datetime(2020, 10, 30, 2, 43, 18, 578000)}, {'status': 'REG_COMPLETED', 'Date': datetime.datetime(2020, 10, 30, 2, 55, 52, 120000)}]

If the status = "REG_COMPLETED" and Date <= lastDate, THEN True else False

Required Output,

ID    Name     Status
1     Rav      True
2     Suv      False
3     Tim      False
4     Jaq      False
5     Tan      True

I tried this but gives me an empty DataFrame,

df[df['status'].apply(lambda x: x[0]['Status']=='REG_COMPLETED' and x[0]['Date']<=lastDate)]
pandas list datetime dictionary python
1 Answers

Use list comprehension for get Date if status match condition, compare and test if at least one True by any:

f = lambda x: any(y['Date'] <= lastDate for y in x if y['status']=='REG_COMPLETED')
df['Status'] = df['Status'].apply(f)
print (df)
   ID Name  Status
0   1  Rav    True
1   2  Suv   False
2   3  Tim   False
3   4  Jaq   False
4   5  Tan    True
user_29010020
Related