a screenshot of a terminal querying a csv file on the web
Скриншот терминала, запрашивающего файл csv в вебе

Когда был максимальный курс доллара к евро?

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

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, создавая целую кучу мусорных строк в конце:

csv file in terminal with junk at the end
Файл csv в терминале с мусором в конце

Влияние этой лишней запятой можно устранить при помощи 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'"
a gnuplot graph in drawn in the
terminal
График gnuplot, нарисованный в терминале

Для более шести тысяч примеров данных в терминале на 80x25 символов получилось достаточно читаемо. Можно составить общий тренд. При этом вполне умеренное соотношение данных и чернил.

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

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'"
a gnuplot graph of usd:eur
График gnuplot для usd:eur

Вывод в 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
a rolling averaged gnuplot graph of usd:eur
Скользящее среднее графика gnuplot данных usd:eur

Круто. Если у вас не установлена duckdb, то не так уж сложно адаптировать это под sqlite3 (запрос будет таким же). Я хотел показать DuckDB, потому что она во многом похожа на sqlite, но только столбчатая (а не строковая). Однако для меня её основная ценность заключается в удобной эргономике.

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

-- работает с  csvbase!
CREATE TABLE eurofxref_hist AS SELECT * FROM
read_csv_auto("https://csvbase.com/calpaterson/eurofxref-hist");
eurofxref-hist in duckdb
eurofxref-hist в duckdb

Это довольно просто, а 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 евро.

Комментарии (24)


  1. savostin
    16.09.2023 10:52

    Я не специалист, но мне кажется все это можно "относительно легко" автоматизировать с помощью Google таблиц и получить вполне себе онлайн решение.


    1. Didimus
      16.09.2023 10:52
      +1

      Или с помощью экселя. Кто не делал автоматическое подтягивание курса доллара с сайта цб?


      1. GameJAM1
        16.09.2023 10:52

        для меня pandas и есть своего рода excel


  1. sshikov
    16.09.2023 10:52
    +51

    Каким был максимальный курс доллара к евро?

    Результат: 2000-10-26.


    2000-10-26 — это курс доллара? Агащазблин.

    А теперь — что на самом деле было написано:

    When was the Dollar highest against the Euro?


    Как говорится, найдите три отличия.


    1. PatientZero Автор
      16.09.2023 10:52
      +6

      Исправил


  1. zabanen2
    16.09.2023 10:52
    +7

    найди 3 отличия https://habr.com/ru/articles/761442/


    1. bel1k0v
      16.09.2023 10:52
      +8

      Ключевое в том, что переводчик использовал подзаголовок, а не заголовок оригинальной статьи (подзаголовок работает лучше), так же, как на HN. В остальном думаю вопрос в том, как почти одинаковый контент прошёл. Можно было бы хотя бы на соответствие проверить процентное, но этот вопрос уже скорее к технической части Сайта.


      1. Fil
        16.09.2023 10:52
        +8

        Вы написали раньше и перевели корректнее. Только эта набрала 36, а ваша всего 7 (сейчас добавлю свои +2). Что бы там ни говорили, кликбейтный заголовок решает.


    1. youngmysteriouslight
      16.09.2023 10:52
      +10

      Легко. Вот первая.

      Оригинал:

      The -s flag just removes some noise from standard error.

      Здесь:

      Флаг -s просто удаляет шум из стандартной ошибки.

      Там:

      Флаг -s для того, чтобы не показывать индикатор выполнения или сообщения об ошибках.

      Кто из этих переводчиков знает, о чём пишет?


      1. bel1k0v
        16.09.2023 10:52
        +11

        С curl каждый день имею дело, поэтому да, не поленился и заменил на строчку из документации.


      1. Mingun
        16.09.2023 10:52
        +5

        Этот


        Она может выводить таблицы в markdown!

        Тот


        Он может выводить таблицы уценок!

        Оригинал


        It can output markdown tables!

        И так весь перевод. Не знаю, кто из них лучше знает предметную область, но первый фрагмент — литературный перевод, второй — огрызок Google Translate'а (или ChatGPT, или что там сейчас в моде?)


        1. bel1k0v
          16.09.2023 10:52

          Огромное спасибо! Всё поправил


  1. akakoychenko
    16.09.2023 10:52
    +8

    В целом, если размер всего датасета таков, что он может уместится в полмегабайта в zip архиве, то, кажется, есть просто безграничное множество классных вариантов раздать эти данные.
    Была б задача раздать историю изменения курса с точностью до каждого тика межбанка, и все вот это

    >Например, нам не нужно выполнять следующие задачи: договариваться о доступе (например, платя деньги или общаясь с поставщиком); сохранять адрес электронной почты/название компании/должность в чью-то базу лидов, проверять, не израсходовали ли мы лимит доступа; выполнять аутентификацию (часто это само по себе становится объёмным сайд-квестом), читать документацию по API или решать проблемы более серьёзные, чем простое форматирование.

    вдруг становится неожиданно хорошей идеей, ибо по другому оно просто работать не будет, увы


  1. Ivan22
    16.09.2023 10:52
    +3

    в большинстве баз данных SQL нет операции melt

    Зато есть UNPIVOT


  1. DustCn
    16.09.2023 10:52
    +2

    Ну давайте сравним, раз их, перевода, два:

    1) Флаг -s просто удаляет шум из стандартной ошибки
    2) Флаг -s для того, чтобы не показывать индикатор выполнения или сообщения об ошибках

    Какой из переводов более гуглтранслейтный?


    1. 1dNDN
      16.09.2023 10:52

      Второй из доки curl утащен, как подсказывает автор парой комментариев выше

      -s, --silent

      Silent or quiet mode. Do not show progress meter or error messages. Makes Curl mute. It still outputs the data you ask for, potentially even to the terminal/stdout unless you redirect it.

      Use -S, --show-error in addition to this option to disable progress meter but still show error messages.

      Providing -s, --silent multiple times has no extra effect. Disable it again with --no-silent.


    1. IvanPetrof
      16.09.2023 10:52
      +1

      Я когда читал, вообще подумал, что речь идёт о фильтровании шума в данных (ну типа скользящего среднего)


    1. Mingun
      16.09.2023 10:52
      +4

      А теперь давайте посмотрим на другой фрагмент:
      Этот


      Данные имеют «широкий» формат — столбец Date, а затем дополнительный столбец для каждой валюты.

      Тот


      Он имеет "широкий" формат для столбца Date, а затем дополнительный столбец для каждой валюты.

      Что в оригинале:


      It's in "wide" format — a Date column, and then an extra column for every currency.


  1. RolexStrider
    16.09.2023 10:52

    А разве gunzip умеет распаковывать .zip, а не только .gz?


    1. alex_shpak
      16.09.2023 10:52
      +2

      Как ни странно, оказывается, да:

      $ file eurofxref-hist.zip
      eurofxref-hist.zip: Zip archive data, at least v2.0 to extract, compression method=deflate
      $ gunzip eurofxref-hist.zip
      gzip: eurofxref-hist.zip: unknown suffix -- ignored
      $ cat eurofxref-hist.zip | gunzip | head -c1
      Date,USD,J

      Более того, если переименовать файл в *.gz, то gunzip его тоже принимает!

      $ mv eurofxref-hist.zip eurofxref-hist.csv.gz
      $ gunzip eurofxref-hist.csv.gz
      $ file eurofxref-hist.csv
      eurofxref-hist.csv: CSV text


  1. vzhilin
    16.09.2023 10:52
    +1

    Подытожу: для того, чтобы получить и обработать данные курса валют нужно знать синтаксис десятка разнородных программ. А еще делать это нужно в примитивной неудобной командной строке, которая об этих программах и об их типах данных ничего не знает и никак вам не поможет.

    Вам не кажется, что индустрия свернула куда-то не туда? Что компьютеры не должны быть настолько сложными? Нет повода гордиться этим лоскутным одеялом, небрежно сшитым на коленке.


    1. iliazeus
      16.09.2023 10:52
      +5

      У вас неверный итог получился.

      для того, чтобы получить и обработать данные курса валют нужно можно знать синтаксис десятка разнородных программ обойтись парой инструментов, которые найдутся на любой системе. А еще делать это нужно можно в примитивной неудобной интерактивной командной строке на коленке, если нужен быстрый ответ на запрос, а не полноценная разработка.


    1. vlivyur
      16.09.2023 10:52
      +2

      Все тянут этот файлик к себе и кладут в свою структуру. И работают уже потом с ней. Никто напрямую не работает с этим файликом, хотя при желании можно, что и продемонстрировано в статье


    1. AlexanderS
      16.09.2023 10:52

      Тут решение с хранением и получением информации выглядит довольно изящно. Есть информация упакованная в zip. Можно скачать файл, распаковать, импортировать в тот же excel и мышкой построить любые графики. А можно как автор сделать. А можно в питоне. Это всяко лучше, чем открывать сайт, дизайн которого постоянно меняется, тыркать всякие менюшки и потом копировать данные со страницы, потому что нормальный экспорт не предусмотрен. И сайт браузером 15-летней давности не открыть. А файл можно получить на системе 25-летней давности)