Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
Каким был максимальный курс доллара к евро?
Вот небольшая программа, вычисляющая это:
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip \
| gunzip \
| sqlite3 -csv ':memory:' '.import /dev/stdin stdin' \
"select Date from stdin order by USD asc limit 1;"Результат: 2000-10-26. (Можете попробовать запустить её самостоятельно.)
Как это работает:
Строка с curl скачивает официальные исторические данные о курсе евро к другим валютам, публикуемые Европейским центральным банком. (Флаг -s просто удаляет шум из стандартной ошибки.)
Данные передаются в файле zip, который распаковывает gunzip.
sqlite3 запрашивает файл csv из архива. :memory говорит sqlite использовать файл, находящийся в памяти. После этого .import /dev/stdin stdin говорит загрузить стандартный ввод в таблицу под названием stdin. Следующая строка — это SQL-запрос.
Очистка в столбце 42
Хотя извлечь простой максимум легко, формат данных неидеален. Данные имеют «широкий» формат — столбец Date, а затем дополнительный столбец для каждой валюты. Вот заголовок csv для этого файла:
Date,USD,JPY,BGN,CYP,CZK,DKK,EEK,GBP,HUF,LTL,LVL,MTL,[и так далее]При выполнении фильтрации и объединения работать проще, если данные хранятся в «длинном» формате, примерно так:
Date,Currency,RateПереход от широкого к длинному формату — это простая операция, обычно называемая melt. К сожалению, в SQL её нет.
Впрочем, это неважно, мы можем выполнить melt при помощи pandas:
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).melt("Date").to_csv(sys.stdout, index=False)'Но есть ещё одна проблема. Обработчики файлов в ЕЦБ ошибочно ставят завершающую запятую в конце каждой строки. Из-за этого парсеры csv распознают дополнительный пустой столбец в конце. Наш sqlite-запрос этого не заметил, но эти запятые мешают выполнять melt, создавая целую кучу мусорных строк в конце:
Влияние этой лишней запятой можно устранить при помощи pandas, добавив в нашу цепочку ещё один элемент: .iloc[:, :-1], который, по сути, говорит «дай мне все строки (":") и всё остальное, кроме последнего столбца (":-1")». То есть:
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).iloc[:, :-1].melt("Date")\
.to_csv(sys.stdout, index=False)'Неужели каждый, кто пользуется этим файлом, должен повторять всю эту возню с данными?
К сожалению, да. Как говорится, «никто не мечтал о работе уборщиком данных, но все ею занимаются».
Но если честно, данные о курсах валют ЕЦБ, вероятно, находится в 10% лучших публикаций открытых данных. Обычно для получения подходящих табличных данных требуется гораздо более мучительный и сложный процесс.
Например, нам не нужно выполнять следующие задачи: договариваться о доступе (например, платя деньги или общаясь с поставщиком); сохранять адрес электронной почты/название компании/должность в чью-то базу лидов, проверять, не израсходовали ли мы лимит доступа; выполнять аутентификацию (часто это само по себе становится объёмным сайд-квестом), читать документацию по API или решать проблемы более серьёзные, чем простое форматирование.
То есть на фоне остальныхeurofxref-hist.zip довольно удобен.
Я помещу очищенную копию в таблицу csvbase, чтобы моим дорогим читателям не приходилось с этим возиться.
Вот, как это сделать:
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).iloc[:, :-1].melt("Date")\
.to_csv(sys.stdout, index=False)' | \
# this is the new bit: \
curl -n --upload-file - \
'https://csvbase.com/calpaterson/eurofxref-hist?public=yes'Всё, что я сделал — это добавил ещё один curl, чтобы при помощи HTTP PUT поместить файл csv в csvbase. --upload-file - выполняет загрузку из стандартного ввода на заданный url (при помощи HTTP PUT). Если таблицы ещё нет в csvbase, то она создаётся. -n добавляет мои учётные данные из моего~/.netrc. Вот и всё. Ничего сложного.
Рисуем красивые графики
Итак, разобравшись с этапом очистки данных, можно переходить к чему-то более интересному.
Давайте создадим график данных:
curl -s https://csvbase.com/calpaterson/eurofxref-hist | \
grep USD | \
cut --delim=, -f 2,4 | \
gnuplot -e "set datafile separator ','; set term dumb; \
plot '-' using 1:2 with lines title 'usd'"
Для более шести тысяч примеров данных в терминале на 80x25 символов получилось достаточно читаемо. Можно составить общий тренд.
(Если вам любопытно, почему https://csvbase.com/calpaterson/eurofxref-hist в браузере возвращает веб-страницу, а curl возвращает файл csv, то прочитайте мой пост.)
gnuplot сам по себе похож на миниатюрный язык программирования. Вот, что делает представленный выше фрагмент:
set datafile separator ','— сообщает, что это csvset term dumb— рисует ascii-графику!plot -— создаёт график данных, поступающих из стандартного входаusing 1:2 with lines— рисует линии из столбцов 1 и 2 (дату и курс)title 'usd'— даёт название линии
Разумеется, можно рисовать графики и в настоящие изображения:
curl -s https://csvbase.com/calpaterson/eurofxref-hist | \
grep USD | \
cut --delim=, -f 2,4 | \
gnuplot -e "set datafile separator ','; set term svg; \
set output 'usd.svg'; set xdata time; set timefmt '%Y-%m-%d'; \
set format x '%Y-%m-%d'; set xtics rotate; \
plot '-' using 1:2 with lines title 'usd'"
Вывод в SVG лишь слегка сложнее, чем в ascii-графику. Чтобы изображение выглядело красиво, нужно помочь gnuplot понять, что это данные временных последовательностей, то есть что ось X — это время, указать формат этого времени, а затем приказать повернуть отметки на оси X, чтобы они были читаемыми. Но код довольно длинный: давайте привяжем его к функции bash, чтобы можно было использовать его многократно:
plot_timeseries_to_svg () {
# $1 is the first param
gnuplot -e "set datafile separator ','; set term svg; \
set output '$1.svg'; set xdata time; set timefmt '%Y-%m-%d'; \
set format x '%Y-%m-%d'; set xtics rotate; \
plot '-' using 1:2 with lines title '$1'"
}Скользящее среднее и новые инструменты
Пока всё получается здорово, но было бы неплохо добавить более сложного анализа: давайте попробуем вычислить скользящее среднее, чтобы можно было увидеть линию тренда:
curl -s https://csvbase.com/calpaterson/eurofxref-hist | \
duckdb -csv -c "select Date, avg(value) over \
(order by date rows between 100 preceding and current row) \
as rolling from read_csv_auto('/dev/stdin')
where variable = 'USD';" | \
plot_timeseries_to_svg rolling
Круто. Если у вас не установлена duckdb, то не так уж сложно адаптировать это под sqlite3 (запрос будет таким же). Я хотел показать DuckDB, потому что она во многом похожа на sqlite, но только столбчатая (а не строковая). Однако для меня её основная ценность заключается в удобной эргономике.
Вот один из примеров этого: можно загружать csv в файлы таблиц напрямую из HTTP:
-- работает с csvbase!
CREATE TABLE eurofxref_hist AS SELECT * FROM
read_csv_auto("https://csvbase.com/calpaterson/eurofxref-hist");
Это довольно просто, а DuckDB вполне хорошо справляется с определением типов. В ней есть множество других полезных возможностей: например, она определяет размер терминала и по умолчанию ограничивает таблицы, а не заваливает терминал огромным набором результатов. У неё есть полоса прогресса для больших запросов! Она может выводить таблицы в markdown! И ещё многое другое.
Открытые данные — это ещё и открытый API
С файлом zip данных и программами, которые или установлены, или устанавливаются простой командой brew install/apt install, можно сделать очень многое. Помню, как был впечатлён, когда я работал в банке и мне впервые показал этот eurofxref-hist.zip опытный коллега. Он был так прост: самый простой протокол обмена данными между организациями, который я видел.
Кажется, что простой файл zip с csv внутри — это очень мало, но на самом деле огромное количество финансовых приложений используют этот конкретный файл zip каждый день. Я практически уверен, что запятые оставили в нём именно поэтому — если бы их удалили, то большой объём кода поломался бы.
Если доступ к открытым данным очень прост, то он выполняет и дополнительную функцию открытого API. В конце концов, в чём здесь отличие от большой доли API, которые гораздо чаще обмениваются данными, а не вызывают удалённые функции?
Поэтому я считаю, что файл zip ЕЦБ — отличный фундамент для формата обмена данными. Мне нравится его простота, и я постарался сохранить её в csvbase.
В csvbase каждая таблица имеет единственный url, соответствующий такому виду:
https://csvbase.com/<username>/<table_name>
Например:
https://csvbase.com/calpaterson/eurofxref-hist
И для каждого url есть четыре основных действия:
При выполненииGET: вы получаете csv (или веб-страницу, если находитесь в браузере).
При выполнении PUT для нового csv: создаётся новая таблица или перезаписывается существующая.
При выполнении POST для нового csv: добавляются новые строки в существующую таблицу.
При выполненииDELETE: таблица удаляется.
Для аутентификации можно просто использовать HTTP Basic Auth.
Можно ли придумать что-то более простое?
Примечание
Выше я сказал, что в большинстве баз данных SQL нет операции melt. Из тех баз данных, которых я знаю, она есть в Snowflake и MS SQL Server. Знатоки SQL часто меня спрашивают: зачем использовать R или Pandas, если уже существует SQL? Самая главная причина заключается в том, что R и Pandas очень хорошо справляются с очисткой данных.
Ещё одна недооцениваемая особенность конвейеров bash заключается в том, что они многопроцессны. Каждая программа работает отдельно в собственном процессе. Пока curl скачивает данные из веба, grep фильтрует их, sqlite выполняет к ним запросы, и, возможно, curl снова загружает их на сервер, и так далее. Всё это делается параллельно, что, как ни странно, вполне позволяет конкурировать со сложными облачными альтернативами.
Почему евро был таким слабым в 2000 году? Эта валюта без монет и купюр была запущена в январе 1999 года. Изначально евро был своего рода внутриигровой валютой Евросоюза. Он существовал только внутри банков, поэтому для него не было ни купюр, ни монет. Всё это появилось позже. Как и вера в эту валюту — поначалу не было похоже, что маленький евро выживает, поэтому курс по отношению к доллару составлял 0,8252. Это значило, что в октябре 2000 года за доллар можно было купить 1,21 евро. Сегодня евро гораздо сильнее, за доллар можно купить меньше, чем 1 евро.
