How to groupby in Pandas by datetime range from different DF

user_8383001 0
Viewed 62

I've stuck and can't solve this... I have 2 dataframes. One has datetimes intervals, another has datetimes and values. I need to get MIN() values based on datetime ranges.

import pandas as pd

timeseries = pd.DataFrame(
    [
        ['2018-01-01T00:00:00.000000000', '2018-01-01T03:00:00.000000000'],
        ['2018-01-02T00:00:00.000000000', '2018-01-02T03:00:00.000000000'],
        ['2018-01-03T00:00:00.000000000', '2018-01-03T03:00:00.000000000'],
    ], dtype='datetime64[ns]', columns=['Start DT', 'End DT'])

values = pd.DataFrame(
    [
        ['2018-01-01T00:00:00.000000000', 1],
        ['2018-01-01T01:00:00.000000000', 2],
        ['2018-01-01T02:00:00.000000000', 0],
        ['2018-01-02T00:00:00.000000000', -1],
        ['2018-01-02T01:00:00.000000000', 3],
        ['2018-01-02T02:00:00.000000000', 10],
        ['2018-01-03T00:00:00.000000000', 7],
        ['2018-01-03T01:00:00.000000000', 11],
        ['2018-01-03T02:00:00.000000000', 2],
    ], columns=['DT', 'Value'])

Required output:

    Start DT              End DT  Min
0 2018-01-01 2018-01-01 03:00:00    0
1 2018-01-02 2018-01-02 03:00:00   -1
2 2018-01-03 2018-01-03 03:00:00    2

And ideas?

pandas-groupby pandas dataframe
3 Answers

Use IntervalIndex created by timeseries columns, then get positions by Index.get_indexer, aggregate min and last add to column to timeseries:

s = pd.IntervalIndex.from_arrays(timeseries['Start DT'], 
                                 timeseries['End DT'], 
                                 closed='both')

values['new'] = timeseries.index[s.get_indexer(values['DT'])]
print (values)
                   DT  Value  new
0 2018-01-01 00:00:00      1    0
1 2018-01-01 01:00:00      2    0
2 2018-01-01 02:00:00      0    0
3 2018-01-02 00:00:00     -1    1
4 2018-01-02 01:00:00      3    1
5 2018-01-02 02:00:00     10    1
6 2018-01-03 00:00:00      7    2
7 2018-01-03 01:00:00     11    2
8 2018-01-03 02:00:00      2    2

df = timeseries.join(values.groupby('new')['Value'].min().rename('Min'))
print (df)
    Start DT              End DT  Min
0 2018-01-01 2018-01-01 03:00:00    0
1 2018-01-02 2018-01-02 03:00:00   -1
2 2018-01-03 2018-01-03 03:00:00    2

EDIT: If no match is added missing values instead -1, so was selected last index value, here 2:

timeseries = pd.DataFrame(
    [
        ['2018-01-01T00:00:00.000000000', '2018-01-01T03:00:00.000000000'],
        ['2018-01-02T00:00:00.000000000', '2018-01-02T03:00:00.000000000'],
        ['2018-01-03T00:00:00.000000000', '2018-01-03T03:00:00.000000000'],
    ], dtype='datetime64[ns]', columns=['Start DT', 'End DT'])

values = pd.DataFrame(
    [   ['2017-12-31T00:00:00.000000000', -10],
        ['2018-01-01T00:00:00.000000000', 1],
        ['2018-01-01T01:00:00.000000000', 2],
        ['2018-01-01T02:00:00.000000000', 0],
        ['2018-01-02T00:00:00.000000000', -1],
        ['2018-01-02T01:00:00.000000000', 3],
        ['2018-01-02T02:00:00.000000000', 10],
        ['2018-01-03T00:00:00.000000000', 7],
        ['2018-01-03T01:00:00.000000000', 11],
        ['2018-01-03T02:00:00.000000000', 2],
    ], columns=['DT', 'Value']) 

values['DT'] = pd.to_datetime(values['DT'])
print (values)
                   DT  Value
0 2017-12-31 00:00:00    -10
1 2018-01-01 00:00:00      1
2 2018-01-01 01:00:00      2
3 2018-01-01 02:00:00      0
4 2018-01-02 00:00:00     -1
5 2018-01-02 01:00:00      3
6 2018-01-02 02:00:00     10
7 2018-01-03 00:00:00      7
8 2018-01-03 01:00:00     11
9 2018-01-03 02:00:00      2

s = pd.IntervalIndex.from_arrays(timeseries['Start DT'], 
                                 timeseries['End DT'], closed='both')

pos = s.get_indexer(values['DT'])
values['new'] = timeseries.index[pos].where(pos != -1)
print (values)
                   DT  Value  new
0 2017-12-31 00:00:00    -10  NaN
1 2018-01-01 00:00:00      1  0.0
2 2018-01-01 01:00:00      2  0.0
3 2018-01-01 02:00:00      0  0.0
4 2018-01-02 00:00:00     -1  1.0
5 2018-01-02 01:00:00      3  1.0
6 2018-01-02 02:00:00     10  1.0
7 2018-01-03 00:00:00      7  2.0
8 2018-01-03 01:00:00     11  2.0
9 2018-01-03 02:00:00      2  2.0

df = timeseries.join(values.dropna(subset=['new']).groupby('new')['Value'].min().rename('Min'))
print (df)
    Start DT              End DT  Min
0 2018-01-01 2018-01-01 03:00:00    0
1 2018-01-02 2018-01-02 03:00:00   -1
2 2018-01-03 2018-01-03 03:00:00    2
user_29010020

One possible solution is to create a variable (key) on which to join the two datasets

# create 'key' variable
timeseries['key'] = timeseries['Start DT'].astype(str)
values['key'] = pd.to_datetime(values['DT'].str.replace('T', ' '), format='%Y-%m-%d %H:%M:%S.%f').dt.date.astype(str)

# create dataset with minima
mins = values.groupby('key').agg({'Value': 'min'}).reset_index()

# join
timeseries.merge(mins, on='key').drop(columns=['key'])

    Start DT              End DT  Value
0 2018-01-01 2018-01-01 03:00:00      0
1 2018-01-02 2018-01-02 03:00:00     -1
2 2018-01-03 2018-01-03 03:00:00      2
user_74654620 
values['DT']=values['DT'].astype(str) #convert to string
s=values['DT'].str.split(' ')#split on space 
values['day']=s.str[0] #take the day part
df4=values.groupby(by='day').min()#groupby and take min value
df4.reset_index(inplace=True) #reset index
df4['day']=pd.to_datetime(df4['day'])#convert back to datetime for merging
final=pd.merge(timeseries,df4,left_on='Start DT',right_on='day',how='inner') #merge
user_157551110
Related