CODEWITHSUNDEEP
pythonpandasdatetimetimedelta
TH14
TH14

Reputation: 622

Find Difference in minutes between two columns based on matching first two characters

I have this dataframe, I am trying to find the difference in minutes between Date1 and Date2 IF the first two characters are the same and create a column for that. For example, the first row, 22 = 22 so then find the difference between 20:27:45 and 20:52:03

         Date1     Date2        ID     City
0   22 20:27:45   22 20:52:03   76    Denver
1   02 20:16:28   02 20:49:02   45    Austin
2   15 19:35:09   15 20:52:44   233   Chicago
3   30 19:47:53   30 20:18:01   35    Detroit
4   09 19:01:52   09 19:45:26   342   New York City

This is what I've tried so far:

(pd.to_datetime(data['Date1'].str[3:]).dt.minute - pd.to_datetime(data['Date2'].str[3:]).dt.minute)

This works fine but I want to add that condition in here. I tried creating a function:

def f(data):
    if data['Date1'][:3] == data['Date2'][:3]:
        return pd.to_datetime(data['Date1'][3:]).dt.minute - pd.to_datetime(data['Date2'][3:]).dt.minute

Getting Error:

AttributeError: ("'Timestamp' object has no attribute 'dt'", 'occurred at index 0')

I know it's nonsensical to be adding in a pd.to_datetime to a series object but how can I convert this into a timestamp and find the difference in minutes?

Upvotes: 1

Views: 148

Answers (2)

danielR9
danielR9

Reputation: 455

Assuming your date columns are currently strings, you can parse the whole day hour:minute:second string, then do an apply based on the day attribute of the timestamp

I changed the day of one of the values to demonstrate what happens if the days aren't equal

def diff_func(x):
    date_1 = pd.to_datetime(x.Date1, format='%d %H:%M:%S')
    date_2 = pd.to_datetime(x.Date2, format='%d %H:%M:%S')
    if date_1.day == date_2.day:
        return (date_2-date_1).seconds / 60
    else:
        return None

df['minute_difference'] = df.apply(diff_func, axis=1)



         Date1        Date2  minute_difference
0  22 20:27:45  22 20:52:03          24.300000
1  03 20:16:28  02 20:49:02                NaN
2  15 19:35:09  15 20:52:44          77.583333
3  30 19:47:53  30 20:18:01          30.133333
4  09 19:01:52  09 19:45:26          43.566667

Upvotes: 2

Erfan
Erfan

Reputation: 42916

You can use Series.str.slice to create the day columns, then pd.to_datetime to create datetime objects. And finally use np.where to conditionally fill the new column called Difference:

df['Date1_day'] = df['Date1'].str.slice(start=0, stop=3)
df['Date2_day'] = df['Date2'].str.slice(start=0, stop=3)
df['Date1'] = pd.to_datetime(df['Date1'].str.slice(start=3))
df['Date2'] = pd.to_datetime(df['Date2'].str.slice(start=3))

df['Difference'] = np.where(df['Date1_day'] == df['Date2_day'],
                            df['Date2'] - df['Date1'],
                            np.NaN)

df.drop(['Date1_day', 'Date2_day'], axis=1, inplace=True)

print(df)
                Date1               Date2   ID           City Difference
0 2019-04-11 20:27:45 2019-04-11 20:52:03   76         Denver   00:24:18
1 2019-04-11 20:16:28 2019-04-11 20:49:02   45         Austin   00:32:34
2 2019-04-11 19:35:09 2019-04-11 20:52:44  233        Chicago   01:17:35
3 2019-04-11 19:47:53 2019-04-11 20:18:01   35        Detroit   00:30:08
4 2019-04-11 19:01:52 2019-04-11 19:45:26  342  New York City   00:43:34

Upvotes: 1

Related Questions