How can I search trough a dataframe based on username and date from a dictionary

user_13270323 0
Viewed 61

I have a dictionary that contains usernames as keys and dates as values.

my_dict = {'User1': '06/01/2020 01:09:42', 'User2': '06/01/2020 01:04:42'}

How can I find occurrences for each user before and after the date in the dictionary?

My dataframe looks something like this:

Username Date
User1 06/01/2020 00:40:41
User1 06/01/2020 00:45:42
User2 06/01/2020 00:54:42
User2 06/01/2020 01:04:42
User1 06/01/2020 01:08:42
User1 06/01/2020 01:09:42
User2 06/01/2020 02:10:42
User1 06/01/2020 04:10:42
User2 06/01/2020 05:10:42

For before I need to have:

Username Date
User1 06/01/2020 00:40:41
User1 06/01/2020 00:45:42
User2 06/01/2020 00:54:42
User1 06/01/2020 01:08:42

For after and the same I need to have:

Username Date
User2 06/01/2020 01:04:42
User1 06/01/2020 01:09:42
User2 06/01/2020 02:10:42
User1 06/01/2020 04:10:42
User2 06/01/2020 05:10:42

Thank you in advance.

pandas for-loop python-3.x numpy python
2 Answers

We can do this using pd.DataFrame.merge and some filtering. Here, I am going to use .loc with a simple lambda function:

# First, let's create a dataframe from your dictionary:
my_dict = {'User1': '06/01/2020 01:09:42', 'User2': '06/01/2020 01:04:42'}
dfm = pd.DataFrame.from_dict(my_dict, orient='index')
dfm[0] = pd.to_datetime(dfm[0])

# Next, merge your original dataframe with the dataframe, dfm:
df_result = df.merge(dfm, right_index=True, left_on='Username')

# Now, filter for date condition before:
df_before = df_result.loc[lambda x: x['Date']<x[0], df.columns].sort_index()
df_before

Output:

  Username                Date
0    User1 2020-06-01 00:40:41
1    User1 2020-06-01 00:45:42
2    User2 2020-06-01 00:54:42
4    User1 2020-06-01 01:08:42

And now after:

df_after = df_result.loc[lambda x: x['Date']>=x[0], df.columns].sort_index()
df_after

Output:

  Username                Date
3    User2 2020-06-01 01:04:42
5    User1 2020-06-01 01:09:42
6    User2 2020-06-01 02:10:42
7    User1 2020-06-01 04:10:42
8    User2 2020-06-01 05:10:42
user_63615310 
import pandas as pd

def process(my_dict,date='2020-03-25 00:00:00'): #give date string here
    c = []
    for each_key in my_dict.keys():
        c.append([each_key,my_dict[each_key]])
    df = pd.DataFrame(c)
    time_mod = pd.Timestamp(date)
    df[1] = df[1].apply(lambda x:pd.Timestamp(x))
    df_after = df.loc[df[1] >= time_mod]
    df_before = df.loc[df[1] < time_mod] 
    print(df_after, df_before)
    return df_after,df_before

Function use: Pass the dictionary and the date as a string (here I took 25 Mar as an example) you want to divide your data upon into before and after to the function, this function returns two dataframes, once containing the data with dates before the input date and other containing after the input date.

user_69230180
Related