create weekly data for stocks faster based on daily data

Question

daily data:

           ts_code  close  low
2021-03-10  APPL   6.67    6.66
2021-03-10  AMA   20.24   20.12
2021-03-11  APPL   6.75    6.50
2021-03-11  AMA   21.10   20.40
2021-03-12  AMA   21.31   21.03
2021-03-12  APPL   6.81    6.76
2021-03-15  AMA   21.43   20.95
2021-03-15  APPL   6.74    6.68
2021-03-16  AMA   21.49   21.10
2021-03-16  APPL   6.74    6.67
2021-03-17  APPL   6.67    6.64
2021-03-17  AMA   21.03   20.74
2021-03-18  APPL   6.56    6.51
2021-03-18  AMA   21.56   20.84
2021-03-19  APPL   6.55    6.47
2021-03-19  AMA   20.31   20.21
2021-03-22  AMA   21.38   20.37
2021-03-22  APPL   6.63    6.55
2021-03-23  APPL   6.60    6.54
2021-03-23  AMA   21.06   20.80
2021-03-24  APPL   6.62    6.55
2021-03-24  AMA   20.37   20.29
2021-03-25  AMA   20.59   20.24
2021-03-25  APPL   6.67    6.57
2021-03-26  APPL   6.69    6.64
2021-03-26  AMA   20.98   20.60
2021-03-29  AMA   21.32   21.03
2021-03-29  APPL   6.57    6.54
2021-03-30  AMA   21.76   21.04
2021-03-30  APPL   6.42    6.40
2021-03-31  APPL   6.39    6.33
2021-03-31  AMA   21.84   21.43
2021-04-01  APPL   6.51    6.32
2021-04-01  AMA   21.61   21.33
2021-04-02  AMA   21.33   21.19
2021-04-02  APPL   6.51    6.44
2021-04-06  AMA   21.51   21.34
2021-04-06  APPL   6.60    6.50
2021-04-07  AMA   21.47   21.14
2021-04-07  APPL   6.58    6.52
2021-04-08  AMA   21.39   21.16
2021-04-08  APPL   6.43    6.40
2021-04-09  AMA   21.13   20.92
2021-04-09  APPL   6.40    6.38
2021-04-12  APPL   6.34    6.32
2021-04-12  AMA   20.54   20.47
2021-04-13  APPL   6.34    6.31
2021-04-13  AMA   20.62   20.43
2021-04-14  APPL   6.36    6.28
2021-04-14  AMA   20.51   20.26
2021-04-15  AMA   20.20   19.92
2021-04-15  APPL   6.49    6.32
2021-04-16  APPL   6.69    6.40
2021-04-16  AMA   20.10   19.66
2021-04-19  AMA   20.98   19.75
2021-04-19  APPL   6.68    6.64
2021-04-20  AMA   21.52   20.76
2021-04-20  APPL   6.68    6.64
2021-04-21  APPL   6.58    6.57
2021-04-21  AMA   22.83   22.12
2021-04-22  APPL   6.57    6.56
2021-04-22  AMA   22.80   22.60
2021-04-23  AMA   23.11   22.89
2021-04-23  APPL   6.47    6.44
2021-04-26  APPL   6.36    6.36
2021-04-26  AMA   22.76   22.72
2021-04-27  AMA   22.76   22.68
2021-04-27  APPL   6.34    6.31
2021-04-28  APPL   6.31    6.28
2021-04-28  AMA   23.17   22.60
2021-04-29  AMA   23.41   22.93
2021-04-29  APPL   6.48    6.31
2021-04-30  AMA   23.11   22.83
2021-04-30  APPL   6.33    6.30

Goal： create weekly data

logic = {
     'low'   : 'min',
     'close' : 'last'}

 df_w=df_daily.groupby('ts_code').resample('W').agg({
                           'low': 'min',
                           'close': 'last'}).reset_index().dropna()

It works slow when there are many dates and stocks. I don't know which part leads slow.

Ref:

This post introduces the definition of weekly data and how to convert based on daily data.

Can you show your complete code? Snippets like this unfortunately can't give us enough review context. — Reinderien
– Reinderien, Commented May 28, 2021 at 15:52
@Reinderien This is complete code. Run this code, the df_w is what I want. Could you tell which part you think is lacked or unclear? — Jack
– Jack, Commented May 29, 2021 at 10:11
df_daily is undefined and logic is defined but never used. — RootTwo
– RootTwo, Commented May 31, 2021 at 2:30

Utsav · Accepted Answer · 2021-06-01 07:04:24Z

Setup

I have considered first column as DatetimeIndex looking at the provided data where dates does not have a column name.

d="""date,ts_code,close,low
2021-03-10,APPL,6.67,6.66
2021-03-10,AMA,20.24,20.12
2021-03-11,APPL,6.75,6.50
2021-03-11,AMA,21.10,20.40
2021-03-12,AMA,21.31,21.03
2021-03-12,APPL,6.81,6.76
2021-03-15,AMA,21.43,20.95
2021-03-15,APPL,6.74,6.68
2021-03-16,AMA,21.49,21.10
2021-03-16,APPL,6.74,6.67
2021-03-17,APPL,6.67,6.64
2021-03-17,AMA,21.03,20.74
2021-03-18,APPL,6.56,6.51
2021-03-18,AMA,21.56,20.84
2021-03-19,APPL,6.55,6.47
2021-03-19,AMA,20.31,20.21
2021-03-22,AMA,21.38,20.37
2021-03-22,APPL,6.63,6.55
2021-03-23,APPL,6.60,6.54
2021-03-23,AMA,21.06,20.80
2021-03-24,APPL,6.62,6.55
2021-03-24,AMA,20.37,20.29
2021-03-25,AMA,20.59,20.24
2021-03-25,APPL,6.67,6.57
2021-03-26,APPL,6.69,6.64
2021-03-26,AMA,20.98,20.60
2021-03-29,AMA,21.32,21.03
2021-03-29,APPL,6.57,6.54
2021-03-30,AMA,21.76,21.04
2021-03-30,APPL,6.42,6.40
2021-03-31,APPL,6.39,6.33
2021-03-31,AMA,21.84,21.43
2021-04-01,APPL,6.51,6.32
2021-04-01,AMA,21.61,21.33
2021-04-02,AMA,21.33,21.19
2021-04-02,APPL,6.51,6.44
2021-04-06,AMA,21.51,21.34
2021-04-06,APPL,6.60,6.50
2021-04-07,AMA,21.47,21.14
2021-04-07,APPL,6.58,6.52
2021-04-08,AMA,21.39,21.16
2021-04-08,APPL,6.43,6.40
2021-04-09,AMA,21.13,20.92
2021-04-09,APPL,6.40,6.38
2021-04-12,APPL,6.34,6.32
2021-04-12,AMA,20.54,20.47
2021-04-13,APPL,6.34,6.31
2021-04-13,AMA,20.62,20.43
2021-04-14,APPL,6.36,6.28
2021-04-14,AMA,20.51,20.26
2021-04-15,AMA,20.20,19.92
2021-04-15,APPL,6.49,6.32
2021-04-16,APPL,6.69,6.40
2021-04-16,AMA,20.10,19.66
2021-04-19,AMA,20.98,19.75
2021-04-19,APPL,6.68,6.64
2021-04-20,AMA,21.52,20.76
2021-04-20,APPL,6.68,6.64
2021-04-21,APPL,6.58,6.57
2021-04-21,AMA,22.83,22.12
2021-04-22,APPL,6.57,6.56
2021-04-22,AMA,22.80,22.60
2021-04-23,AMA,23.11,22.89
2021-04-23,APPL,6.47,6.44
2021-04-26,APPL,6.36,6.36
2021-04-26,AMA,22.76,22.72
2021-04-27,AMA,22.76,22.68
2021-04-27,APPL,6.34,6.31
2021-04-28,APPL,6.31,6.28
2021-04-28,AMA,23.17,22.60
2021-04-29,AMA,23.41,22.93
2021-04-29,APPL,6.48,6.31
2021-04-30,AMA,23.11,22.83
2021-04-30,APPL,6.33,6.30"""
df=pd.read_csv(StringIO(d))
df.date = pd.to_datetime(df.date)
df = df.set_index('date')

Setup dataframe

           ts_code  close   low
date            
2021-03-10  APPL    6.67    6.66
2021-03-10  AMA     20.24   20.12
2021-03-11  APPL    6.75    6.50
2021-03-11  AMA     21.10   20.40
2021-03-12  AMA     21.31   21.03
... ... ... ...
2021-04-28  AMA     23.17   22.60
2021-04-29  AMA     23.41   22.93
2021-04-29  APPL    6.48    6.31
2021-04-30  AMA     23.11   22.83
2021-04-30  APPL    6.33    6.30

Explanation

We are using pd.Grouper which is much faster compared to resample when used with groupby.

Solution

df.groupby(['ts_code', pd.Grouper(level=0, freq='W')]).agg({'low': 'min','close': 'last'}).reset_index()

Ouput Head Sample

    ts_code date        low     close
0   AMA     2021-03-14  20.12   21.31
1   AMA     2021-03-21  20.21   20.31
2   AMA     2021-03-28  20.24   20.98
3   AMA     2021-04-04  21.03   21.33
4   AMA     2021-04-11  20.92   21.13
5   AMA     2021-04-18  19.66   20.10
6   AMA     2021-04-25  19.75   23.11
7   AMA     2021-05-02  22.60   23.11
8   APPL    2021-03-14  6.50    6.81
9   APPL    2021-03-21  6.47    6.55
10  APPL    2021-03-28  6.54    6.69
11  APPL    2021-04-04  6.32    6.51
12  APPL    2021-04-11  6.38    6.40
13  APPL    2021-04-18  6.28    6.69
14  APPL    2021-04-25  6.44    6.47
15  APPL    2021-05-02  6.28    6.33

Performance Comparison

Existing solution with resample

%timeit df.groupby('ts_code').resample('W').agg({'low': 'min','close': 'last'}).reset_index().dropna()
#Output - 23.6 ms ± 2.93 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

Using pd.Grouper

Around 3 times faster

%timeit df.groupby(['ts_code', pd.Grouper(level=0, freq='W')]).agg({'low': 'min','close': 'last'}).reset_index()
#Output - 7.76 ms ± 403 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Why is resample slower than pd.Grouper?

Let's consider an example to understand the difference between resample and Grouper.(Taken last 4 rows of provided data and added a last row with 1 month diff)

Sample Data

d="""date,ts_code,close,low
2021-04-29,AMA,23.41,22.93
2021-04-29,APPL,6.48,6.31
2021-04-30,AMA,23.11,22.83
2021-04-30,APPL,6.33,6.30
2021-05-30,APPL,6.33,6.30"""
df = pd.read_csv(StringIO(d))
df.date = pd.to_datetime(df.date)
df = df.set_index('date')
df


            ts_code close   low
date            
2021-04-29  AMA     23.41   22.93
2021-04-29  APPL    6.48    6.31
2021-04-30  AMA     23.11   22.83
2021-04-30  APPL    6.33    6.30
2021-05-30  APPL    6.33    6.30

When resampling using resample for weekly freq

df.groupby('ts_code').resample('W').agg({'low': 'min','close': 'last'}).reset_index()

Output

    ts_code date        low     close
0   AMA     2021-05-02  22.83   23.11
1   APPL    2021-05-02  6.30    6.33
2   APPL    2021-05-09  NaN     NaN
3   APPL    2021-05-16  NaN     NaN
4   APPL    2021-05-23  NaN     NaN
5   APPL    2021-05-30  6.30    6.33

Using pd.Groupby

df.groupby(['ts_code', pd.Grouper(level=0, freq='W')]).agg({'low': 'min','close': 'last'}).reset_index()

Output

    ts_code date        low     close
0   AMA     2021-05-02  22.83   23.11
1   APPL    2021-05-02  6.30    6.33
2   APPL    2021-05-30  6.30    6.33

Explanation

As we can see the difference in output of resample and Grouper, resample samples the data including the missing frequencies(weekly) and add NaN to the values whereas Grouper is concerned only about grouping the provided data and sampling it to provided freq hence Grouper has less work, specific data to deal thus faster.

In this question we were able to use Grouper since you were dropping na at the end but when we want to resample the data and need all the rows including the NaNs then we will have to use resample.

To my eyes and a couple other users, your post has one insightful observation. As such we have removed the comment from the other user and undeleted your answer. I'm sorry for any confusion around your post. Please feel free to ping me if you have any problems with your post henceforth. — Peilonrayz
– Peilonrayz ♦, Commented May 31, 2021 at 18:56
@Uts Great，but would you mind explaining why pd.Grouper is faster? — Jack
– Jack, Commented May 31, 2021 at 23:58

Stack Exchange Network

create weekly data for stocks faster based on daily data

1 Answer 1

Performance Comparison

Existing solution with resample

Using pd.Grouper

Why is resample slower than pd.Grouper?

You must log in to answer this question.

Hot Network Questions

create weekly data for stocks faster based on daily data

1 Answer 1

Performance Comparison

Existing solution with resample

Using pd.Grouper

Why is resample slower than pd.Grouper?

You must log in to answer this question.

Related

Hot Network Questions