CODEWITHSUNDEEP
pythonpandasdataframe
Viktor.w
Viktor.w

Reputation: 2297

Update a dataframe's columns, if the value is between the intervals of another dataframe's column

My first dataframe(df1) looks like this: 

Un      s   spread_bin  gamma_star exp_star gamma_zero  interval
0   0   0.000000    NaN   NaN     NaN            NaN    [0.0, 2.828e-05)
1   1   0.000110    A   1.5466  -1.210453e-07   1.5466  [8.485e-05, 0.0001131)
2   2   0.000308    A   1.5466  -1.007298e-07   1.5466  [0.0002828, 0.0003111)
3   3   0.000484    A   1.5466  -8.274816e-08   1.5466  [0.0004808, 0.0005091)
4   4   0.000601    A   1.5466  -7.075857e-08   1.5466  [0.0005939, 0.0006222)
5   5   0.000691    A   1.5466  -6.149985e-08   1.5466  [0.0006788, 0.000707)

And the second one (df2): 

time                       close    high    low     open    midprice    s
0   2018-12-17 15:15:00 0.00318624  0.00318624  0.00318240  0.00318240  0.00318432  0.001206
1   2018-12-17 15:16:00 0.00319673  0.00319673  0.00318576  0.00318576  0.003191245 0.003438
2   2018-12-17 15:19:00 0.00319617  0.00319617  0.00319581  0.00319617  0.00319599  0.000113
3   2018-12-17 15:20:00 0.00318881  0.00319617  0.00318881  0.00319582  0.00319249  0.002305
4   2018-12-17 15:22:00 0.00319000  0.00319000  0.00319000  0.00319000  0.00319000  0.000000
5   2018-12-17 15:26:00 0.00319000  0.00319556  0.00319000  0.00319556  0.00319278  0.001741
6   2018-12-17 15:27:00 0.00318989  0.00319000  0.00318989  0.00319000  0.003189945 0.000034

What I need is to update the new column of df2['gamma'] as followed: if df2['s'] ends-up in one of the interval of df1['interval'], select the related df1['gamma_star'] and update df2['gamma'] with that value! I have no idea on how to proceed, any help? thanks!

Upvotes: 1

Views: 49

Answers (1)

T. Kau
T. Kau

Reputation: 643

I'm sure there's a nicer way to do this, but a fast way is doing:

def in_interval(value, lower, upper):
    if lower <= value <= upper:
        return True
    else:
        return False


df2['gamma'] = 0
for i, s in enumerate(df2['s']):
    for j, interval in enumerate(df1['interval']):
        if in_interval(s, interval[0], interval[1]):
            df2.loc[i, 'gamma'] = df1.loc[j, 'gamma_star']

Upvotes: 1

Related Questions