Формат CSV. csvkit

Работа с файлами формата CSV.

1 Общая информация

• Репозиторий: https://github.com/wireservice/csvkit
• Документация: https://csvkit.rtfd.org/
• Язык реализации: python
• Набор инструментов командной строки для различных преобразований, анализа и работы с CSV-файлами.
• Имеет встроенную поддержку для чтения входных файлов, сжатых в gzip, bz2 и xz (LZMA). Сжатие автоматически определяется на основе расширения файла и не требует добавления дополнительных опций.

2 Примеры

• Конвертировать Excel в CSV:

  in2csv data.xls > data.csv
  

• Преобразовать JSON в CSV:

  in2csv data.json > data.csv
  

• Напечатать имена столбцов:

  csvcut -n data.csv
  

• Выберите подмножество столбцов:

  csvcut -c column_a,column_c data.csv > new.csv
  

• Изменить порядок столбцов:

  csvcut -c column_c,column_a data.csv > new.csv
  

• Найдите строки с соответствующими ячейками:

  csvgrep -c phone_number -r "555-555-\d{4}" data.csv > new.csv
  

• Преобразовать в JSON:

  csvjson data.csv > data.json
  

• Сгенерировать сводную статистику:

  csvstat data.csv
  

• Запрос с SQL:

  csvsql --query "select name from data where age > 30" data.csv > new.csv
  

• Импортировать в PostgreSQL:

  csvsql --db postgresql:///database --insert data.csv
  

• Извлечь данные из PostgreSQL:

  sql2csv --db postgresql:///database --query "select * from data" > new.csv
  

3 Список утилит

• in2csv: преобразует различные форматы табличных данных в CSV (dbf, fixed, geojson, json, ndjson, xls, xlsx).
• csvstat: выводит описательную статистику для всех столбцов в файле CSV. Определяет тип каждого столбца, а затем выводит статистику (диапазоны для дат, среднее значение и медиана для целых чисел и т. д.).
• csvlook: отображает CSV-файл в командной строке в формате с фиксированной шириной, совместимом с Markdown.
• csvcut: фильтрует и усекает CSV-файлы. Подобно команде cut, но для табличных данных.
• csvgrep: фильтрует табличные данные только по тем строкам, в которых определенные столбцы содержат заданное значение или соответствуют регулярному выражению.
• csvjoin: объединяет две или более таблиц CSV вместе, используя метод, аналогичный операции SQL JOIN.
• csvsort: сортировка CSV-файлов. Подобно команде sort, но для табличных данных.
• sql2csv: выполняет произвольные команды для базы данных SQL и выводит результаты в виде CSV.
• csvsql: генерируйте инструкции SQL для CSV-файла или выполняет эти инструкции непосредственно в базе данных. В последнем случае поддерживается как создание таблиц, так и вставка данных.
• csvclean: очищает файл CSV от распространенных синтаксических ошибок:
  • сообщает о строках, которые имеют другое количество столбцов, чем строка заголовка;
  • пытается исправить CSV, объединяя короткие строки в одну строку.
• csvstack: складывает строки из нескольких CSV-файлов, при необходимости добавив значение группировки в каждую строку.
• csvformat преобразует файл CSV в пользовательский выходной формат (пользовательский разделитель и т.д.).
• csvjson: преобразует CSV-файл в JSON или GeoJSON.
• csvpy: загружает CSV-файл в объект agate.csv.Reader, а затем переходит в оболочку Python, чтобы пользователь мог просматривать данные так, как считает нужным.

4 Общие ключи

• -d DELIMITER, --delimiter DELIMITER: символ-разделитель входного CSV-файла.
• -t, --tabs: указывает, что входной файл CSV разделен табуляцией. Переопределяет -d.
• -q QUOTECHAR, --quotechar QUOTECHAR: символ, используемый для кавычек строк во входном CSV-файле.
• -u {0,1,2,3}, --quoting {0,1,2,3}: стиль цитирования, используемый во входном CSV-файле.
  • 0: цитата минимальная,
  • 1: цитата вся,
  • 2: цитата нечисловая,
  • 3: цитата отсутствует.
• -b, --no-doublequote: будут ли удваиваться двойные кавычки во входном CSV-файле.
• -p ESCAPECHAR, --escapechar ESCAPECHAR: символ, используемый для экранирования разделителя, если указано --quoting 3 (‘Нет кавычек’), и для экранирования QUOTECHAR, если указано --no-doublequote.
• -z FIELD_SIZE_LIMIT, --maxfieldsize FIELD_SIZE_LIMIT: максимальная длина одного поля во входном CSV-файле.
• -e КОДИРОВАНИЕ, --encoding КОДИРОВАНИЕ: кодировка входного CSV-файла.
• -L LOCALE, --locale LOCALE: локаль (en_US) любых отформатированных чисел.
• -S, --skipinitialspace: игнорировать пробелы сразу после разделителя.
• --blanks: не приводить пустые строки ’na’, ’n/a’, ’none’, ’null’, ‘.’ к значениям NULL.
• --date-format DATE_FORMAT: строка формата даты date.strptime, например ‘%m/%d/%Y’.
• --datetime-format DATETIME_FORMAT: строка формата даты и времени datetime.strptime, например ‘%m/%d/%Y %I:%M %p’.
• -H, --no-header-row: во входном CSV-файле нет строки заголовка. Создаст заголовки по умолчанию (a,b,c,…).
• -K SKIP_LINES, --skip-lines SKIP_LINES: количество начальных строк, которые нужно пропустить перед строкой заголовка (например, комментарии, уведомления об авторских правах, пустые строки).
• -v, --verbose: выводить подробные трассировки при возникновении ошибок.
• -l, --linenumbers: вставляет столбец с номерами строк в начало. Полезно при передаче в grep или в качестве простого первичного ключа.
• --zero: при интерпретации или отображении номеров столбцов используйте нумерацию, начинающуюся с нуля, вместо нумерации по умолчанию, начинающуюся с 1.

5 Отдельные утилиты

5.1 in2csv

• Преобразование данных в CSV.

5.1.1 Примеры

• Посмотрим файл Excell через csvkit:

  in2csv ne_1033_data.xlsx | csvlook | less -S
  

  • По данным можно перемещаться вверх, вниз, влево, вправо.
  • Для выхода из просмотра данных, на клавиатуре нажмите латинскую букву q.

• Конвертируем файл Excel его в CSV:

  in2csv ne_1033_data.xlsx > data.csv
  

5.1.2 Параметры утилиты in2csv

• -f {csv,dbf,fixed,geojson,json,ndjson,xls,xlsx}: формат входного файла. Если не указано, будет определяться из типа файла.
• -s SCHEMA, --schema SCHEMA: файл схемы в формате CSV для преобразования файлов фиксированной ширины.
• -k KEY, --key KEY: ключ верхнего уровня для поиска списка объектов, подлежащих преобразованию при обработке JSON.
• n, -names: отображение имен листов из входного файла Excel.
• --sheet SHEET: имя листа Excel для преобразования.
• --write-sheets WRITE_SHEETS: имена листов Excel для записи в файлы или - для записи всех листов.
• --encoding-xls ENCODING_XLS: кодировка входного XLS-файла.

5.2 csvlook

• Удобный просмотр CSV-данных.

5.2.1 Примеры

• Просмотр файла:

  csvlook data.csv | less -S
  

5.2.2 Параметры утилиты csvlook

• --max-rows MAX_ROWS: максимальное количество отображаемых строк перед усечением данных.
• --max-columns MAX_COLUMNS: максимальное количество столбцов для отображения до усечения данных.
• --max-column-width MAX_COLUMN_WIDTH: обрезать все столбцы не более чем до этой ширины. Остаток будет заменен многоточием.

5.3 csvcut

• Просмотр определенных столбцов CSV.

5.3.1 Примеры

• Набор данных состоит из четырнадцати столбцов. Посмотрим только на столбцы 2, 5 и 6, тем самым сократим выходные данные всего до трех столбцов:

  csvcut -c 2,5,6 data.csv | csvlook
  

• Также можно ссылаться на столбцы по их названиям и чтобы облегчить себе жизнь добавим команду less:

  csvcut -c county,item_name,quantity data.csv | csvlook | less
  

• Урезанные данные можно сохранить в новый файл:

  csvcut -c 2,5,6 data.csv > data_cut.csv
  

5.3.2 Параметры утилиты csvcut

• -n, --names: выводит имена столбцов и индексы из входного CSV-файла и выходит.
• -c COLUMNS, --columns COLUMNS: разделенный запятыми список индексов столбцов, имен или диапазонов, подлежащих извлечению, например “1,id,3-5”. По умолчанию все столбцы.
• -C NOT_COLUMNS, --not-columns NOT_COLUMNS: разделенный запятыми список индексов столбцов, имен или диапазонов, подлежащих исключению, например “1,id,3-5”. По умолчанию нет.
• -x, --delete-empty-rows: после вырезания удаляет полностью пустые строки

5.4 csvstat

• Статистика.

5.4.1 Примеры

• Рассмотрим сводную статистику для нескольких столбцов из набора данных:

  csvcut -c county,acquisition_cost,ship_date data.csv | csvstat
  

  • Утилита csvstat определяет тип данных в каждом столбце, а затем выполняет базовую статистику по ним.
  • Конкретная вычисляемая статистика зависит от типа столбца (числа, текст, даты и т.д.).

5.4.2 Параметры CLI утилиты csvstat:

• --csv: выводить результаты в формате CSV, а не в виде текста.
• -n, --names: показать имена столбцов и индексы из входного CSV и выйти.
• -c COLUMNS, --columns COLUMNS: разделенный запятыми список индексов столбцов, имен или диапазонов для проверки, например “1,id,3-5”. По умолчанию все столбцы.
• --type: выводить только тип выходных данных.
• --nulls: выводить только те столбцы, которые содержат пустые значения.
• --unique: выводить только количество уникальных значений.
• --min: выводить только наименьшие значения.
• --max: выводить только наибольшее значения.
• --sum: выводить только суммы.
• --mean: только средние.
• --median: только только медианы.
• --stdev: выводить только стандартные отклонения.
• --len: выводить только длину самых длинных значений.
• --freq: выводить только списки часто встречающихся значений.
• --freq-count FREQ_COUNT: максимальное количество часто отображаемых значений.
• --count: выводить только общее количество строк.
• --decimal-format DECIMAL_FORMAT: спецификация формата % для вывода десятичных чисел. По умолчанию используется форматирование, зависящее от локали: ‘%.3f’.
• -G, --no-grouping-separator: не использует группирующие разделители в десятичных числах.

5.5 csvgrep

• Поиск данных.

5.5.1 Примеры

• Поиск подстроки среди столбцов/строк.
• Дополнительно используем csvcut, чтобы просмотреть интересующие столбцы, и csvlook для форматирования вывода:

  csvcut -c county,item_name,total_cost data.csv | csvgrep -c county -m LANCASTER | csvlook
  

5.5.2 Параметры утилиты csvgrep

• -n, --names: отображение имен столбцов и индексов из входного CSV.
• -c COLUMNS, --columns COLUMNS: разделенный запятыми список индексов столбцов, имен или диапазонов для поиска, например. ‘1,id,3-5’.
• -m PATTERN, --match PATTERN: строка для поиска.
• -r REGEX, --regex REGEX: регулярное выражение для поиска.
• -f MATCHFILE, --file MATCHFILE: путь к файлу. Для каждой строки, если какая-либо строка в файле (без разделителей строк) точно соответствует значению ячейки, совпадающей строки.
• -i, --invert-match: выбирает не совпадающие строки вместо совпадающих строк.
• -a, --any-match: выбирает строки, в которых соответствует любой столбец, а не все столбцы

5.6 csvsort

• Сортировка данных.

5.6.1 Примеры

• Сортировка строк по столбцу total_cost в обратном (убывающем) порядке:

  csvcut -c county,item_name,total_cost data.csv | csvgrep -c county -m LANCASTER | csvsort -c total_cost -r | csvlook
  

5.7 csvjoin

• Объединение связанных данных.
• Позволяет взять два набора данных и объединить их, как с запросом SQL JOIN.
• Ключевые столбцы указываются с помощью флага -c.
• Если флаг столбцов не указан, то таблицы будут объединены последовательно, то есть они будут объединены в порядке строк без фильтрации.
• Операция соединения требует чтения всех файлов в память.

5.7.1 Примеры

• Например:

  csvjoin -c 1 join_a.csv join_b.csv
  csvjoin -c index join_a.csv join_b.csv
  

• Добавим два пустых столбца справа от CSV-файла:

  echo "," | csvjoin examples/dummy.csv -
  

• Добавим один пустой столбец справа от CSV-файла:

  echo "new-column" | csvjoin examples/dummy.csv -
  

5.7.2 Параметры утилиты csvjoin

• -c COLUMNS, --columns COLUMNS: имена столбцов, к которым необходимо присоединиться. Должно быть либо одно имя (или индекс), либо список, разделенный запятыми, с одним именем (или индексом) для каждого файла в том же порядке, в котором файлы были указаны. Если не указано, два файла будут соединены последовательно без сопоставления.
• --outer: выполняет полное внешнее соединение, а не внутреннее соединение по умолчанию.
• --left: выполняет левое внешнее соединение, а не внутреннее соединение по умолчанию. Если предоставлено более двух файлов, то будет выполнено как последовательность левых внешних соединений, начиная с левого файла.
• --right: выполняет правое внешнее соединение, а не внутреннее соединение по умолчанию. Если предоставлено более двух файлов, то будет выполнено как последовательность правых внешних соединений, начиная с правого файла.
• -I, --no-inference: отключает определение типа при анализе CSV.

5.8 csvstack

• Объединение подмножеств.

5.8.1 Примеры

• Соединить строки из CSV-файлов с одинаковыми столбцами (и одинаковыми именами столбцов):

  csvstack data1.csv data2.csv data3.csv > data.csv
  

• Если указать флаг -g, то можно добавить столбец группировки к каждой строке, чтобы можно было определить, из какого файла взята каждая строка.
• Объединение набора файлов за разные годы:

  csvstack -g 2009,2010 data1.csv data2.csv
  

• Добавим один новый столбец слева:

  csvstack -n NEWCOL -g "" data.csv
  

5.8.2 Параметры утилиты csvstack

• -g GROUPS, --groups GROUPS: список значений, разделенных запятыми, для добавления в качестве “факторов группировки”, по одному для каждого складываемого CSV-файла. Они добавляются к выходным данным как новый столбец. Можно указать имя для нового столбца, используя флаг -n.
• -n GROUP_NAME, --group-name GROUP_NAME: имя столбца группировки, например. ‘year’. Используется только при указании -g.
• --filenames: использует имя файла каждого входного файла в качестве значения его группировки. Если указано, -g будет игнорироваться.

5.9 csvsql

• Создание базы данных из CSV-файла.

5.9.1 Примеры

• По умолчанию csvsql сгенерирует SQL-код для создания таблицы для данных в CSV-файле.
• Можно указать желаемую базу данных (для которой генерируется код создания таблицы) с флагом -i:

  csvsql -i sqlite data.csv
  

• Можно использовать csvsql для создания таблицы непосредственно в базе данных. Если вы добавите параметр --insert, данные также будут импортированы:

  csvsql --db sqlite:///data.db --insert data.csv
  

• Если необходимо выполнить только один SQL запрос к данным, то можно не создавать базу данных, а выполнить запрос в памяти:

  csvsql --query "select county,item_name,total_cost from data where quantity > 5;" data.csv | csvlook | less
  

5.9.2 Параметры утилиты csvsql

• -i {firebird,mssql,mysql,oracle,postgresql,sqlite,sybase,crate}: диалект SQL для генерации. Не может использоваться с --db.
• --db CONNECTION_STRING: строка подключения, используемая для непосредственного выполнения сгенерированного SQL в базе данных.
• --query QUERY: выполняет один или несколько SQL-запросов, разделенных символом ‘;’ и выводит результат последнего запроса в формате CSV. QUERY может быть именем файла.
• --insert: вставляет данные в таблицу. Требует --db.
• --prefix PREFIX: добавляет выражение после ключевого слова INSERT, например OR IGNORE или OR REPLACE.
• --before-insert BEFORE_INSERT: выполняет SQL перед командой INSERT. Требует --insert.
• --after-insert AFTER_INSERT: выполняет SQL после команды INSERT. Требует --insert.
• --tables TABLE_NAMES: разделенный запятыми список имен создаваемых таблиц. По умолчанию таблицы будут называться по именам файлов без расширений.
• --no-constraints: создает схему без ограничений по длине или проверок на null. Полезно при выборке больших таблиц.
• --unique-constraint UNIQUE_CONSTRAINT: разделенный по столбцам список имен столбцов для включения в ограничение UNIQUE.
• --no-create: пропускает создание таблицы. Требует --insert.
• --create-if-not-exists: создает таблицу, если она не существует, в противном случае продолжает работу. Требуется --insert.
• --overwrite: удаляет таблицу, если она уже существует. Требуется --insert. Нельзя использовать с --no-create.
• --db-schema DB_SCHEMA: необязательное имя схемы базы данных для создания таблиц.
• -I, --no-inference: отключает вывод типа при анализе ввода.
• --chunk-size CHUNK_SIZE: размер данных для пакетной вставки в таблицу. Требует --insert.

5.10 sql2csv

• Запросы к базам данных.

5.10.1 Примеры

• Проверить, что данные были успешно импортированы:

  sql2csv --db sqlite:///data.db --query "select * from data"
  

• Экспортировать данные, в которых county=‘LANCASTER’ из базы данных sqlite:

  sql2csv --db sqlite:///data.db --query "select * from data where county='LANCASTER';" > LANCASTER.csv
  

5.10.2 Параметры утилиты sql2csv

• --db CONNECTION_STRING: cтрока подключения к базе данных (как в sqlalchemy).
• --query QUERY: SQL-запрос для выполнения. Переопределяет FILE и STDIN.
• -e ENCODING, --encoding ENCODING: кодировка входного файла.
• -H, --no-header-row: не выводить имена столбцов.

5.11 csvjson

• Преобразование CSV-данных в JSON.
• Очень часто одним из последних шагов в любом анализе данных является размещение данных в Интернете для отображения в виде таблицы, карты или диаграммы.
• Для этого, чаще всего нужен JSON.

5.11.1 Примеры

• Воспользуемся csvcut и csvgrep для преобразования небольшого фрагмента данных:

  csvcut -c county,item_name data.csv | csvgrep -c county -m "GREELEY" | csvjson --indent 4
  

5.11.2 Параметры утилиты csvjson

• -i INDENT, --indent INDENT: отступ в выходном JSON на это количество пробелов. Отключено по умолчанию.
• -k KEY, --key KEY: выводит JSON как объект, обозначенный заданным столбцом KEY, а не как массив. Все значения столбцов должны быть уникальными.
  • Если указаны --lat и --lon, этот столбец используется в качестве идентификатора объекта GeoJSON.
• --lat LAT: индекс или имя столбца, содержащие широту. Результатом будет GeoJSON вместо JSON. Требуется --lon.
• --lon LON: индекс столбца или имя, содержащее долготу. Результатом будет GeoJSON вместо JSON. Требуется --lat.
• --type TYPE: индекс столбца или имя, содержащее тип GeoJSON. Вывод будет GeoJSON вместо JSON. Требуется --lat и --lon.
• --geometry GEOMETRY: индекс столбца или имя, содержащее геометрию GeoJSON. Вывод будет GeoJSON вместо JSON. Требуется --lat и --lon.
• --crs CRS: строка системы отсчета координат, которая будет включена в выходные данные GeoJSON. Требуется --lat и --lon.
• --no-bbox: отключает вычисление ограничивающей рамки.
• --stream: вывод JSON в виде потока объектов, разделенных новой строкой, а не в виде массива.
• -y SNIFF_LIMIT, --snifflimit SNIFF_LIMIT: ограничивает прослушивание диалектов CSV указанным числом байтов. Укажите «0», чтобы отключить сниффинг.
• -I, --no-inference: отключает определение типа (--locale, --date-format, --datetime-format) при разборе входных данных CSV.