📈 Загрузка данных временных рядов на сервер SQL с помощью Python

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

***

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

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

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

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

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

📈 Загрузка данных временных рядов на сервер SQL с помощью Python
Cтраница Historical Data на Yahoo Finance

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

Разберем скрипт 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 , который выглядит следующим образом:

📈 Загрузка данных временных рядов на сервер SQL с помощью Python

Вы можете вставить данные временных рядов из 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.

📈 Загрузка данных временных рядов на сервер SQL с помощью Python

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

📈 Загрузка данных временных рядов на сервер SQL с помощью Python

Вы можете загрузить цены и объёмы для этих новых символов с помощью скрипта 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

📈 Загрузка данных временных рядов на сервер SQL с помощью Python

Обновление списка наблюдения может быть выполнено с помощью инструкции 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.

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

📈 Загрузка данных временных рядов на сервер SQL с помощью Python

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

📈 Загрузка данных временных рядов на сервер SQL с помощью Python

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

📈 Загрузка данных временных рядов на сервер SQL с помощью Python

Содержимое файла можно добавить в таблицу наблюдения значений временных рядов (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 года. Остальные пять символов, также имеют значения временных рядов в том же диапазоне дат.

📈 Загрузка данных временных рядов на сервер SQL с помощью Python

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

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

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

proglib.io

Добавить комментарий

Ваш e-mail не будет опубликован.

5 × 3 =