How to iterate two data frames in python?

Question

Dataset1:

Date         Weekday     OpenPrice ClosePrice
_______________________________________________
28/07/2022   Thursday    5678       5674
04/08/2022   Thursday    5274       5674
11/08/2022.  Thursday    7650       7652

Dataset2:

Date         Weekday     Open Price Close Price
______________________________________________
29/07/2022   Friday      4371        4387
05/08/2022   Friday      6785        6790
12/08/2022   Friday      4367        6756

I would like to iterate these two datasets and create a new dataset with shows data as below. This is the difference between Open Price of Week1 (Week n-1) on Friday and Close price of Week2 (Week n) on Thursday.

Week         Difference    
______________________________
Week2            543 (i.e 5674 - 4371) 
Week3            867 (i.e 7652 - 6785)

Here is the real file: https://github.com/ravindraprasad75/HRBot/blob/master/DatasetforSOF.xlsx

Does dataset1 only contain values on Thursday? Does dataset2 only contain values on Friday? Do both datasets only have the close price and the open price? This question will be easier to solve if you have a dataset with all the days and both close and open price columns — user3808430, Aug 21 '22 at 11:16
I have updated the data to make it easy. In case if it makes it easy, I can merge them in to same dataset. To reiterate, Week2 difference needs to be Open price of Week1 on Friday and Close Price of Week2 on Thursday. — Ravindra, Aug 21 '22 at 11:25
Can you provide the dataset so that we can copy and use it in our terminal? — Roshan, Aug 21 '22 at 11:35
Here is the real file. This contains all the weeks. But i am keen on week n-1 Friday Open and week n Thursday Close price https://github.com/ravindraprasad75/HRBot/blob/master/DatasetforSOF.xlsx — Ravindra, Aug 21 '22 at 11:57

score 0 · Answer 1 · answered Aug 21 '22 at 11:56

Don't iterate over dataframes. Merge them instead.

Reconstruction of your data (cf. How to make good reproducible pandas examples on how to share dataframes)

from io import StringIO
from datetime import datetime

cols = ['Date', 'Weekday', 'OpenPrice', 'ClosePrice']

data1 = """28/07/2022   Thursday    5674       5678
04/08/2022   Thursday    5274       5278
11/08/2022.  Thursday    7652       7687"""

data2 = """29/07/2022   Friday      4371        4387
05/08/2022   Friday      6785        6790
12/08/2022   Friday      4367        6756"""

df1, df2 = (pd.read_csv(StringIO(d), 
                        header = None, 
                        sep="\s+", 
                        names=cols, 
                        parse_dates=["Date"],
                        dayfirst=True) for d in (data1, data2))

Add Week column

df1['Week'] = df1.Date.dt.isocalendar().week
df2['Week'] = df2.Date.dt.isocalendar().week

Resulting dataframes:

>>> df1
        Date   Weekday  OpenPrice  ClosePrice  Week
0 2022-07-28  Thursday       5674        5678    30
1 2022-08-04  Thursday       5274        5278    31
2 2022-08-11  Thursday       7652        7687    32

>>> df2
        Date Weekday  OpenPrice  ClosePrice  Week
0 2022-07-29  Friday       4371        4387    30
1 2022-08-05  Friday       6785        6790    31
2 2022-08-12  Friday       4367        6756    32

Merge on Week

df3 = df1.merge(df2, on="Week", suffixes=("_Thursday", "_Friday"))

Result:

>>> df3
  Date_Thursday Weekday_Thursday  OpenPrice_Thursday  ClosePrice_Thursday  \
0    2022-07-28         Thursday                5674                 5678   
1    2022-08-04         Thursday                5274                 5278   
2    2022-08-11         Thursday                7652                 7687   

   Week Date_Friday Weekday_Friday  OpenPrice_Friday  ClosePrice_Friday  
0    30  2022-07-29         Friday              4371               4387  
1    31  2022-08-05         Friday              6785               6790  
2    32  2022-08-12         Friday              4367               6756

Now you can simply do df3.OpenPrice_Friday - df3.ClosePrice_Thursday, using shift where you need to compare different weeks.

Thank you. I followed slightly different approach – Ravindra Aug 24 '22 at 17:00 — Ravindra, Aug 24 '22 at 17:00

How to iterate two data frames in python?

1 Answers1