CODEWITHSUNDEEP
pythonpandasgoogle-bigquery
HayatoY
HayatoY

Reputation: 527

Is there a way to resample time series data in BigQuery?

pandas DataFrame has resample method like below, what I would like to achieve is equivalent method by querying in BigQuery.

Example method in pandas
Now I have data like this. assuming same data is stored in bigquery.

In [2]: df.head()
Out[2]: 
                        Open     High      Low    Close  Volume
Gmt time                                                       
2016-01-03 22:00:00  1.08730  1.08730  1.08702  1.08714    8.62
2016-01-03 22:01:00  1.08718  1.08718  1.08713  1.08713    3.75
2016-01-03 22:02:00  1.08714  1.08721  1.08714  1.08720    4.60
2016-01-03 22:03:00  1.08717  1.08721  1.08714  1.08721    7.57
2016-01-03 22:04:00  1.08718  1.08718  1.08711  1.08711    5.52

Then resample the data with 5 minutes frequency using DataFrame.

In [3]: ohlcv = {
      :         'Open':'first',
      :         'High':'max',
      :         'Low':'min',
      :         'Close':'last',
      :         'Volume':'sum'
      :         }
      : df = df.resample('5T').apply(ohlcv)  # 5 minutes frequency
      : df = df[['Open', 'High', 'Low', 'Close', 'Volume']]  # reorder columns
      : df.head()
      : 
      : 
Out[3]: 
                        Open     High      Low    Close  Volume
Gmt time                                                       
2016-01-03 22:00:00  1.08730  1.08730  1.08702  1.08711   30.06
2016-01-03 22:05:00  1.08711  1.08727  1.08709  1.08709  190.63
2016-01-03 22:10:00  1.08708  1.08709  1.08662  1.08666  168.79
2016-01-03 22:15:00  1.08666  1.08674  1.08666  1.08667  223.83
2016-01-03 22:20:00  1.08667  1.08713  1.08666  1.08667  170.17

This can be done after fetching 1 minute frequency data from bigquery.
But is there a way to QUERY resampling in bigquery?

EDIT

detailed explanation of pandas DataFrame resample.

                        Open     High      Low    Close  Volume
Gmt time                                                       
# 1 minute frequency data stored in bigquery
2016-01-03 22:00:00  1.08730  1.08730  1.08702  1.08714    8.62
2016-01-03 22:01:00  1.08718  1.08718  1.08713  1.08713    3.75
2016-01-03 22:02:00  1.08714  1.08721  1.08714  1.08720    4.60
2016-01-03 22:03:00  1.08717  1.08721  1.08714  1.08721    7.57
2016-01-03 22:04:00  1.08718  1.08718  1.08711  1.08711    5.52

2016-01-03 22:05:00  1.08711  1.08714  1.08711  1.08711   27.47
2016-01-03 22:06:00  1.08717  1.08720  1.08711  1.08711   21.58
2016-01-03 22:07:00  1.08713  1.08718  1.08712  1.08715   28.12
2016-01-03 22:08:00  1.08714  1.08723  1.08712  1.08718   49.74
2016-01-03 22:09:00  1.08722  1.08727  1.08709  1.08709   63.72

# expected query result
# above will be resampled into below..
2016-01-03 22:00:00  1.08730  1.08730  1.08702  1.08711   30.06
2016-01-03 22:05:00  1.08711  1.08727  1.08709  1.08709  190.63
# method to resample 'first'  'max'    'min'    'last'    'sum'

First 5 rows (22:00 to 22:04) in 1min frequency is resampled into 1 row (22:00),
next 5 rows (22:05 to 22:09) into (22:05).
Resampling methods are first, max, min, last and sum respectively.

first computes first value of group (which here means 5 rows)
max computes maximum value,
min computes minimum value,
last computes last value,
sum computes sum of the column in the group

for more detail see pandas Document

Upvotes: 5

Views: 2654

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Try below 

#standardSQL
SELECT * EXCEPT(step) 
FROM (
  SELECT *, TIMESTAMP_DIFF(TIMESTAMP(ts), 
              TIMESTAMP(MIN(ts) OVER(ORDER BY ts)), MINUTE) AS step
  FROM yourTable
)
WHERE MOD(step, 5) = 0
-- ORDER BY ts

Sampling interval can be controlled by changing 5 in MOD(step, 5) and MINUTE in TIMESTAMP_DIFF

You can play with this using below dummy data 

WITH yourTable AS (
  SELECT '2016-01-03 22:00:00' AS ts, 1.08730 AS Open, 1.08730 AS High, 1.08702 AS Low, 1.08714 AS Close, 8.62 AS Volume UNION ALL
  SELECT '2016-01-03 22:01:00', 1.08718, 1.08718, 1.08713, 1.08713, 3.75 UNION ALL
  SELECT '2016-01-03 22:02:00', 1.08714, 1.08721, 1.08714, 1.08720, 4.60 UNION ALL
  SELECT '2016-01-03 22:03:00', 1.08717, 1.08721, 1.08714, 1.08721, 7.57 UNION ALL
  SELECT '2016-01-03 22:04:00', 1.08718, 1.08718, 1.08711, 1.08711, 5.52 UNION ALL
  SELECT '2016-01-03 22:05:00', 1.08718, 1.08718, 1.08713, 1.08713, 3.75 UNION ALL
  SELECT '2016-01-03 22:06:00', 1.08714, 1.08721, 1.08714, 1.08720, 4.60 UNION ALL
  SELECT '2016-01-03 22:07:00', 1.08717, 1.08721, 1.08714, 1.08721, 7.57 UNION ALL
  SELECT '2016-01-03 22:08:00', 1.08718, 1.08718, 1.08711, 1.08711, 5.52 UNION ALL
  SELECT '2016-01-03 22:09:00', 1.08718, 1.08718, 1.08713, 1.08713, 3.75 UNION ALL
  SELECT '2016-01-03 22:10:00', 1.08714, 1.08721, 1.08714, 1.08720, 4.60 UNION ALL
  SELECT '2016-01-03 22:11:00', 1.08717, 1.08721, 1.08714, 1.08721, 7.57 UNION ALL
  SELECT '2016-01-03 22:12:00', 1.08718, 1.08718, 1.08711, 1.08711, 5.52 
)

Below version implements "panda's resample" (per logic in updated question) 

#standardSQL
SELECT 
  MIN(ts) AS ts,
  ARRAY_AGG(Open ORDER BY ts)[OFFSET (0)] AS Open,
  MAX(High) AS High,
  MIN(Low) AS Low,
  ARRAY_AGG(Close ORDER BY ts DESC)[OFFSET (0)] AS Close,
  SUM(Volume) AS Volume
FROM (
  SELECT *, DIV(TIMESTAMP_DIFF(TIMESTAMP(ts), 
              TIMESTAMP(MIN(ts) OVER(ORDER BY ts)), MINUTE), 5) AS grp
  FROM yourTable
)
GROUP BY grp
-- ORDER BY ts

Or further simplified version, with just one GROUP BY and windowing function. Also assuming your data is for later than '2000-01-01 00:00:00' - otherwise you can adjust accordingly 

#standardSQL
SELECT 
  MIN(ts) AS ts,
  ARRAY_AGG(Open ORDER BY ts)[OFFSET (0)] AS Open,
  MAX(High) AS High,
  MIN(Low) AS Low,
  ARRAY_AGG(Close ORDER BY ts DESC)[OFFSET (0)] AS Close,
  SUM(Volume) AS Volume
FROM yourTable
GROUP BY DIV(TIMESTAMP_DIFF(TIMESTAMP(ts), 
             TIMESTAMP('2000-01-01 00:00:00'), MINUTE), 5)
-- ORDER BY ts

Upvotes: 8

Related Questions