Почему мой любимый 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-запрос.
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 — это собственный маленький мини-язык программирования. Вот что делает приведенный выше фрагмент:
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! И т. д!
Простой zip-файл с CSV-файлом кажется таким крошечным, но на самом деле огромное количество финансовых приложений используют именно этот zip-файл каждый день. Я почти уверен, что именно поэтому они оставили эти запятые — если бы они удалили их сейчас, они сломали бы много кода.
Когда открытые данные становятся действительно легкодоступными, они также выполняют двойную функцию как открытый API. В конце концов, в чем функциональная разница для значительной части API, в которых речь идет не столько о вызове удаленных функций, сколько об обмене данными?
Поэтому я думаю, что zip-файл ЕЦБ является довольно хорошей отправной точкой для формата обмена данными. Мне нравится простота — и я попытался сохранить ее с помощью csvbase.
В csvbase каждая таблица имеет один URL-адрес следующего вида:
https://csvbase.com/<username>/<table_name>
например
GET для получения csv
PUT для создания новой таблицы, или перезаписи существующей
POST для записи в таблицу
DELETE для удаления
Аутентификация HTTP Basic Auth
Одной из недооцененных особенностей пайплайнов bash является то, что они являются многопроцессными. Каждая программа работает независимо, в своем собственном процессе. Пока curl загружает данные из Интернета, grep их фильтрует, sqlite запрашивает их и, возможно, curl снова загружает их и т. д. И все это параллельно, что, как ни удивительно, может сделать его очень конкурентоспособным по сравнению с причудливыми облачными альтернативами.
Почему евро был таким слабым в 2000 году? Он был запущен без монет и банкнот в январе 1999 года. Изначально евро было своего рода внутриигровой валютой Европейского Союза. Он существовал только внутри банков — поэтому для него не было ни банкнот, ни монет. Это все пришло позже. То же самое произошло и с верой: поначалу казалось, что маленькому евро не удастся выжить: поэтому курс по отношению к доллару составил 0,8252. Это означает, что в октябре 2000 года за доллар можно было купить 1,21 евро (чтобы повернуть обменный курс вспять, поделите 1 на курс). Сегодня евро намного сильнее: за доллар можно купить менее 1 евро.
Когда доллар был самым высоким по отношению к евро?
Вот небольшая программа, которая это вычисляет:
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>
например
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 евро.
Простые инструменты для пайплайнов: sqlite, pandas, gnuplot и другие
Почему мой любимый API — это zip-файл на сайте Европейского центрального банка? Когда доллар был самым высоким по отношению к евро? Вот небольшая программа, которая это вычисляет: curl -s...
habr.com