Простые инструменты для пайплайнов: sqlite, pandas, gnuplot и другие

Почему мой любимый API — это zip-файл на сайте Европейского центрального банка?

Когда доллар был самым высоким по отношению к евро?

Вот небольшая программа, которая это вычисляет:

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;"

Вывод: 26.10.2000. (Попробуйте запустить его самостоятельно.)

Как это работает:

сurl загружает официальные исторические данные, которые публикует Европейский центральный банк о положении евро по отношению к другим валютам. (Флаг -s для того, чтобы не показывать индикатор выполнения или сообщения об ошибках.)

Эти данные поступают в виде zip-файла, который gunzip распакует.

sqlite3 читает CSV. :memory опция sqlite говорит использовать файл в памяти. После этого .import /dev/stdin stdin говоритsqlite загрузить стандартный ввод в таблицу с именем stdin. Следующая за ней строка - SQL-запрос.

Очистка в столбце 42

Хотя получить простой максимум легко, форма данных не идеальна. Он имеет "широкий" формат - столбец Date, а затем дополнительный столбец для каждой валюты. Вот заголовок CSV для этого файла:

Date,USD,JPY,BGN,CYP,CZK,DKK,EEK,GBP,HUF,LTL,LVL,MTL,[and on, and on]

При использовании фильтров и агрегирования жизнь проще, если данные имеют "длинный" формат, например:

Date,Currency,Rate

Переключение с широкого на длинное — это простая операция, обычно называемая "плавлением". К сожалению, он недоступен в SQL (прим. пер.: sqlite3).

Неважно, расплавим с помощью 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-запрос этого не заметил, но эти запятые мешают "плавлению", создавая в конце целый набор мусорных строк:

Эффект этой дополнительной запятой можно удалить с помощью 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, для отправки 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'"

Это достаточно разборчиво для более чем 6000 точек данных в терминале размером 80x25 символов. Вы можете различить общую тенденцию. Разумное соотношение данных и чернил.

(Если вам интересно, как преобразовать данные из файла в https://csvbase.com/calpaterson/eurofxref-hist, см. более раннюю публикацию в блоге.)

gnuplot — это собственный маленький мини-язык программирования. Вот что делает приведенный выше фрагмент:

• set datafile separator ',' - задаём разделитель (csv)

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

Вот один из них: вы можете загружать CSV-файлы в таблицы прямо из HTTP:

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

GET для получения csv

PUT для создания новой таблицы, или перезаписи существующей

POST для записи в таблицу

DELETE для удаления

Аутентификация HTTP Basic Auth

Примечания

Выше я сказал, что большинство баз данных SQL не имеют операции «плавления». Насколько мне известно, это реализовано в Snowflake и MS SQL Server. Один вопрос, который часто задают знающие SQL: почему кто-то вообще использует R или Pandas, когда SQL уже существует? Основная причина заключается в том, что R и Pandas очень сильны в очистке данных.

Одной из недооцененных особенностей пайплайнов bash является то, что они являются многопроцессными. Каждая программа работает независимо, в своем собственном процессе. Пока curl загружает данные из Интернета, grep их фильтрует, sqlite запрашивает их и, возможно, curl снова загружает их и т. д. И все это параллельно, что, как ни удивительно, может сделать его очень конкурентоспособным по сравнению с причудливыми облачными альтернативами.

Почему евро был таким слабым в 2000 году? Он был запущен без монет и банкнот в январе 1999 года. Изначально евро было своего рода внутриигровой валютой Европейского Союза. Он существовал только внутри банков — поэтому для него не было ни банкнот, ни монет. Это все пришло позже. То же самое произошло и с верой: поначалу казалось, что маленькому евро не удастся выжить: поэтому курс по отношению к доллару составил 0,8252. Это означает, что в октябре 2000 года за доллар можно было купить 1,21 евро (чтобы повернуть обменный курс вспять, поделите 1 на курс). Сегодня евро намного сильнее: за доллар можно купить менее 1 евро.