CODEWITHSUNDEEP
pythonpandasdatetime
AB14
AB14

Reputation: 407

Mapping the values of one dataframe using the index to other dataframe column

I have a dataframe df1 which has three columns (Target, seasonality and seasonality index). Seasonality pattern repeats after every 7 points.The last index of seasonality is 2. I have another dataframe df2 which has forecasted column with 10 rows. Now I want to create new column in df2 which will be the sum of forecasted column and seasonality column of df1. The catch here is mapping. I want to add values to forecasted column using seasonality index column with corresponding seasonality column. For ex: the seasonality value of 4th seasonality index should be added to first element of forecasted column. After exhausting 6th index values the addition should start from zero again as the seasonality repeats after 7 points.

df1

                       Target   Seasonality Seasonality_index
Datetime            
2019-01-01 00:00:00     0.44    0.12    0
2019-01-01 01:00:00     0.44    0.06    1
2019-01-01 02:00:00     0.43    0.01    2
2019-01-01 03:00:00     0.43    -0.04   3
2019-01-01 04:00:00     0.43    -0.09   4
2019-01-01 05:00:00     0.43    -0.10   5
2019-01-01 06:00:00     0.42    -0.13   6
2019-01-01 07:00:00     0.42    0.12    0
2019-01-01 08:00:00     0.42    0.06    1
2019-01-01 09:00:00     0.43    0.01    2


df2

       Datetime         forecasted   Expected_output
    2019-01-01 10:00:00 7.21         7.21 -(-0.04) #4th element
    2019-01-01 11:00:00 7.20         7.20 -(-0.09) #5th element
    2019-01-01 12:00:00 7.19         7.19 -(-0.10) #6th element
    2019-01-01 13:00:00 7.18         7.18 -(-0.13) #7th element
    2019-01-01 14:00:00 7.19         7.19 -(0.12) #1st element
    2019-01-01 15:00:00 7.19         7.19 -(0.06) #2nd element
    2019-01-01 16:00:00 7.20         7.20 -(-0.10) #3rd element
    2019-01-01 17:00:00 7.20         7.20 -(-0.04) #4th element
    2019-01-01 18:00:00 7.21         7.21 -(-0.09) #5th element
    2019-01-01 19:00:00 7.20         7.20 -(-0.10) #6th element

Upvotes: 1

Views: 561

Answers (1)

jezrael
jezrael

Reputation: 862731

I believe you can use:

repeat = df['Seasonality_index'].max() + 1

#first convert first group values to list
a = df1['Seasonality'].tolist()[:repeat]
print (a)
[0.12, 0.06, 0.01, -0.04, -0.09, -0.1, -0.13]

#reorder values by constant
first = df['Seasonality_index'].iat[-1] + 1
b= a[first:] + a[:first]
print (b)
[-0.04, -0.09, -0.1, -0.13, 0.12, 0.06, 0.01]

#repeat values by length of df2
arr = np.tile(b, int(len(df2) // repeat) + repeat)
#assign by length of df2
df2['test'] = arr[:len(df2)]
df2['Expected_output'] = df2['forecasted']  - arr[:len(df2)]

print (df2)
                     forecasted  Expected_output  test
Datetime                                              
2019-01-01 10:00:00        7.21             7.25 -0.04
2019-01-01 11:00:00        7.20             7.29 -0.09
2019-01-01 12:00:00        7.19             7.29 -0.10
2019-01-01 13:00:00        7.18             7.31 -0.13
2019-01-01 14:00:00        7.19             7.07  0.12
2019-01-01 15:00:00        7.19             7.13  0.06
2019-01-01 16:00:00        7.20             7.19  0.01
2019-01-01 17:00:00        7.20             7.24 -0.04
2019-01-01 18:00:00        7.21             7.30 -0.09
2019-01-01 19:00:00        7.20             7.30 -0.10

Upvotes: 2

Related Questions