Цель статьи – познакомить читателей с процессом сбора, интерпретации и обработки данных для оперирования временными рядами с помощью сервера SQL и Python. Обсудить

Адаптированный текст публикуется с сокращениями, автор оригинальной статьи Rick Dobson.

***

Распространённая задача анализа данных – обработка временно́го ряда. Это может быть отслеживание распространения вируса или анализ стоимости ценных бумаг, который мы рассмотрим в данной статье. В качестве сервера SQL возьмём Microsoft SQL Server, а программировать будем на Python.

Для начала выделим три общие задачи:

1. Управление информацией после заполнения базы данных.
2. Увеличение числа отслеживаемых элементов после первоначального заполнения БД.
3. Добавление свежих данных для более поздних интервалов времени.

Cбор и настройка исходного набора данных

Начнем со сбора сведений. Раздобыть их можно в транзакционных базах данных, хранилищах информации вашей компании или в общедоступных интернет-источниках. Необходимо связать между собой по крайней два измерения: метки времени и соответствующие им значениями переменной.

Ниже показана информация о мартовских ценах акций корпорации Microsoft на Yahoo Finance. Обратите внимание, что торгующиеся на бирже ценные бумаги обозначаются специальными идентификаторами – тикерами (тикерными символами – от англ. ticker symbols). Например, MSFT – тикер акций Microsoft.

Cтраница Historical Data на Yahoo Finance

В серой части страницы приведены основные параметры запроса: в частности можно указать интервал времени и частоту.

Разберем скрипт Python, предназначенный для сбора данных временных рядов из файла MSSQLTips_4.txt.

         import pandas_datareader.data as web import datetime   symbol = [] with open('C:python_programsMSSQLTips_4.txt') as f:     for line in f:         symbol.append(line.strip()) f.close()   start = datetime.date(2019,9,17) end = datetime.date(2019,9,24)   path_out = 'c:/python_programs_output/' file_out = 'yahoo_prices_volumes_for_MSSQLTips_4_to_csv_demo.csv'   i = 0 while i < len(symbol):     try:         df = web.DataReader(symbol[i], 'yahoo', start, end)         df.insert(0, 'Symbol', symbol[i])         df = df.drop(['Adj Close'], axis=1)         if i == 0:             df.to_csv(path_out+file_out)             print (i, symbol[i],'has data stored to csv file')         else:             df.to_csv(path_out+file_out,mode = 'a', header=False)             print (i, symbol[i],'has data stored to csv file')     except:         print("No information for symbol or file is open in Excel:")         print (i, symbol[i])         continue     i+=1     

Скрипт записывает информацию в файл yahoo_prices_volumes_for_MSSQLTips_4_to_csv_demo.csv , который выглядит следующим образом:

Вы можете вставить данные временных рядов из yahoo_prices_volumes_for_MSSQLTips_4_to_csv_demo.csv в таблицу на сервере SQL с помощью группового insert-а. Приведённый ниже сценарий демонстрирует этот процесс:

• Начинается он с инструкции use для базы данных, содержащей таблицу для начальной загрузки.
• Сценарий создает новую копию таблицы yahoo_prices_volumes_for_MSSQLTips.
• Таблица yahoo_prices_volumes_for_MSSQLTips заполняется начальными данными.
• Оператор if exists проверяет наличие таблицы и если она существует, старая копия удаляется.
• Вставка содержимого файла yahoo_prices_volumes_for_MSSQLTips_4_to_csv_demo.csv в таблицу yahoo_prices_volumes_for_MSSQLTips осуществляется с помощью insert.
• Завершает скрипт оператор select, который возвращает содержимое заполненной таблицы.

         use [for_csv_from_python] go   -- drop table for watchlist if it exists if exists(select object_id('dbo.yahoo_prices_volumes_for_MSSQLTips')) drop table dbo.yahoo_prices_volumes_for_MSSQLTips   -- create table for watchlist create table dbo.yahoo_prices_volumes_for_MSSQLTips(    [Date] date,    [Symbol] nvarchar(10),    [Open] money NULL,    [High] money NULL,    [Low] money NULL,    [Close] money NULL,    [Volume] int NULL )  go    -- bulk insert first batch of symbols to watchlist bulk insert dbo.yahoo_prices_volumes_for_MSSQLTips from 'C:python_programs_outputyahoo_prices_volumes_for_MSSQLTips_4_to_csv_demo.csv' with (     firstrow = 2,     fieldterminator = ',',  --CSV field delimiter     rowterminator = 'n' )    -- display watchlist table with data for first batch of symbols select * from dbo.yahoo_prices_volumes_for_MSSQLTips order by symbol, date     

Ниже приведён набор значений из последнего select в предыдущем скрипте. Строки упорядочиваются с помощью директивы order by.

Добавление новых тикеров в начальную загрузку

После начальной загрузки данными нужно управлять. Пользователи могут запрашивать информацию о дополнительных элементах: например, о ценах в том же диапазоне дат ещё для трех тикеров, вроде ENPH, INS и KL. Вам в помощь txt-файл MSSQLTips_3.txt с тикерными символами для начальной загрузки.

Вы можете загрузить цены и объёмы для этих новых символов с помощью скрипта Python read_mssqltips_3_for_export_via_csv.py. Он ссылается на MSSQLTips_3.txt и сохраняет выходные данные в файле yahoo_prices_volumes_for_MSSQLTips_3_to_csv_demo.csv. Полный список всех скриптов Python в этом руководстве доступен по этой ссылке.

Приведем файл с данными: yahoo_prices_volumes_for_MSSQLTips_3_to_csv_demo.csv

Обновление списка наблюдения может быть выполнено с помощью инструкции bulk insert, которая перекачивает содержимое yahoo_prices_volumes_for_MSSQLTips_3_to_csv_demo.csv в таблицу yahoo_prices_volumes_for_MSSQLTips. Следующий скрипт показывает код T-SQL для обновления таблицы:

         -- bulk insert second batch of symbols to watchlist bulk insert dbo.yahoo_prices_volumes_for_MSSQLTips from 'C:python_programs_outputyahoo_prices_volumes_for_MSSQLTips_3_to_csv_demo.csv' with (     firstrow = 2,     fieldterminator = ',',  --CSV field delimiter     rowterminator = 'n' )   -- display watchlist table with data for first and second batches of symbols select * from dbo.yahoo_prices_volumes_for_MSSQLTips order by symbol, date     

Последний select показывает таблицу наблюдения после того, как в нее добавились тикеры ENPH, INS и KL. Напомним, что изначально в таблице их было всего четыре: AMZN, MSFT, ORCL, PAYS.

Таблица наблюдения содержит сорок две строки данных — по шесть строк для каждого из семи тикеров.

Добавление новых строк с данными

В этом разделе представлен обзор изменений кода для обновления существующей таблицы значений временных рядов данными для дополнительного периода времени. До этого момента в статье фигурировал интервал с 17 сентября 2019 по 24 сентября 2019. При работе с данными временных рядов обычно происходит регулярное обновление информации, например, один раз в день. Поскольку в примерах используются последовательные торговые дни, добавим данные за 25 сентября 2019 года для тикеров из MSSLQTIPS_7.txt.

В файле yahoo_prices_volumes_for_MSSQLTips_7_to_csv_demo.csv с данными за 25 сентября 2019 года для каждого тикера существует только одна строка.

Содержимое файла можно добавить в таблицу наблюдения значений временных рядов (yahoo_prices_volumes_for_MSSQLTips) с помощью следующего кода.

         -- bulk insert fresh time series data to watchlist bulk insert dbo.yahoo_prices_volumes_for_MSSQLTips from 'C:python_programs_outputyahoo_prices_volumes_for_MSSQLTips_7_to_csv_demo.csv' with (     firstrow = 2,     fieldterminator = ',',  --CSV field delimiter     rowterminator = 'n' )    -- display watchlist table with data for first and second batches of symbols -- and with an extra row of time series data from the preceding select statement select * from dbo.yahoo_prices_volumes_for_MSSQLTips order by symbol, date     

В выводе оператора select из предыдущего скрипта показаны результаты по AMZN для начальной загрузки данных временных рядов, а также по ENPH из обновления. Как видите, оба символа имеют исторические значения временных рядов для торговых дат с 17 сентября 2019 года по 25 сентября 2019 года. Остальные пять символов, также имеют значения временных рядов в том же диапазоне дат.

Заключение

Для закрепления информации, предлагаем вам домашнее задание:

• попробуйте повторить описанные в статье действия;
• измените тикеры и даты на те, которые вам больше нравятся;
• замените текущие файлы cave новыми, из систем вашей компании.

Не останавливайтесь на достигнутом. Удачи!