📈 Загрузка данных временных рядов на сервер SQL с помощью Python
Цель статьи – познакомить читателей с процессом сбора, интерпретации и обработки данных для оперирования временными рядами с помощью сервера SQL и Python.
Адаптированный текст публикуется с сокращениями, автор оригинальной статьи Rick Dobson.
Распространённая задача анализа данных – обработка временно́го ряда. Это может быть отслеживание распространения вируса или анализ стоимости ценных бумаг, который мы рассмотрим в данной статье. В качестве сервера SQL возьмём Microsoft SQL Server, а программировать будем на Python.
Для начала выделим три общие задачи:
- Управление информацией после заполнения базы данных.
- Увеличение числа отслеживаемых элементов после первоначального заполнения БД.
- Добавление свежих данных для более поздних интервалов времени.
Cбор и настройка исходного набора данных
Начнем со сбора сведений. Раздобыть их можно в транзакционных базах данных, хранилищах информации вашей компании или в общедоступных интернет-источниках. Необходимо связать между собой по крайней два измерения: метки времени и соответствующие им значениями переменной.
Ниже показана информация о мартовских ценах акций корпорации Microsoft на Yahoo Finance. Обратите внимание, что торгующиеся на бирже ценные бумаги обозначаются специальными идентификаторами – тикерами (тикерными символами – от англ. ticker symbols). Например, MSFT – тикер акций Microsoft.
В серой части страницы приведены основные параметры запроса: в частности можно указать интервал времени и частоту.
Разберем скрипт
Python, предназначенный для сбора данных временных рядов из файла MSSQLTips_4.txt
.
import pandas_datareader.data as web import datetime symbol = [] with open('C:\python_programs\MSSQLTips_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_output\yahoo_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_output\yahoo_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_output\yahoo_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 новыми, из систем вашей компании.
Не останавливайтесь на достигнутом. Удачи!