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

Question

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

@ Vikas P I have created a separate column depicting my intended output. Hope that clarify your question. — AB14
– AB14, Commented Sep 23, 2019 at 6:56
want to add values to forecasted column using seasonality index column with corresponding seasonality column. - problem is in first df1 is not (145th index value of seasonality), (146th index value of seasonality)... in sample data. I suggest create minimal, complete, and verifiable example, with 5, 3 rows of sample data and add also expcted output for easy verify solution. — jezrael
– jezrael, Commented Sep 23, 2019 at 7:02
@ jezrael as per your suggestion I have created a sample data and i guess this one is more specific — AB14
– AB14, Commented Sep 23, 2019 at 7:42

jezrael · Accepted Answer · 2019-09-23 08:40:54Z

2

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

edited Sep 23, 2019 at 8:40

answered Sep 23, 2019 at 8:24

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AB14 Over a year ago

@ Jezrael this is very useful . However, this is a specific case where you have use first = 3, What if I don't want to hard code anything ? Whatever be my last seasonality index , the addition in df2 starts from there only ?

jezrael Over a year ago

@AB14 - then use first = df['Seasonality_index'].iat[-1] + 1

jezrael Over a year ago

@AB14 - And instead a = df1['Seasonality'].tolist()[:7] use a = df1['Seasonality'].tolist()[:repeat]

jezrael Over a year ago

And also for arr = np.tile(b, int(len(df2) // repeat) + repeat)

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related