<jupyter_start><jupyter_text>Начнем с подключения неободимых библиотек, применение каждой из них будет рассмотрено отдельно позднее.<jupyter_code>import requests
import psycopg2
import petl as etl
import numpy as np

from datetime import datetime as dt<jupyter_output><empty_output><jupyter_text>Библиотека PETL  Загрузка данных`petl` поддерживает различные источники данных, мы рассмотрим следующие:- Загрузка из xlsx-файла  - Использование открытых источников через API- Работа с базой данных  Данные из xlsx-файла Рассмотрим работу с `petl` на наборе результатов летних олимпиад по странам. Нам понадобится файл `datasets/summer_olympics.xlsx`, посмотрим на первые строки, пока не сохраняя таблицу в переменную.<jupyter_code>etl.fromxlsx('datasets/summer_olympics.xlsx')<jupyter_output><empty_output><jupyter_text>Видим, что данные загрузились без ошибок, однако заголовки столбцов не определились, потому что в начале файла есть лишняя пустая строка. Исправим это, используя функцию `skip` и поместим результат в переменную `olympics`<jupyter_code>olympics = etl.fromxlsx('datasets/summer_olympics.xlsx').skip(1)
olympics<jupyter_output><empty_output><jupyter_text>Теперь заголовки у столбцов корректные, однако не достаточно информативны, исправим это, задав заголовки в ручную.<jupyter_code>olympics2 = olympics.setheader(['country','games','gold','silver','bronze'])
olympics2<jupyter_output><empty_output><jupyter_text>Мы начали выстраивать цепочку преобразования таблицы, это удобно, так как можно просмотреть результат работы на каждом этапе. Иногда, наоборот, удобнее объединить цепочку сразу в одной команде. Например, совместим изменение заголовка с сортировкой по количеству золотых медалей.<jupyter_code>olympics2 = olympics.setheader(['country','games','gold','silver','bronze']).sort('gold', reverse=True)
olympics2<jupyter_output><empty_output><jupyter_text>Теперь мы можем посчитать общее количество медалей и сохранить его в новом столбце, используя функцию `addfield`.Мы также применим мощный инструмент Python - **Анонимные функции**. **Анонимная функция** (функция без имени) - это запись вида `lambda x: `. Читается как: "То, что было подано на вход этого выражения, будет положено в `x`, а результатом исполнения будет ``. В PETL это часто применяется, чтобы выполнить быстрое преобразования значения какого-либо из полей. Например, если нужно все значения таблицы `table` в поле `field` умножить на два, это можно написать как `table.convert('field', lambda x : x * 2)`. В примере ниже функция применяется не к отдельным значениям, а к строке целиком.<jupyter_code>olympics2.addfield('total', lambda row : row['gold'] + row['silver'] + row['bronze'])<jupyter_output><empty_output><jupyter_text>Вместо того, чтобы получить суммы, мы просто склеили значения. Чтобы такого не происходило, будем преобразовывать формат данных в целочисленные. Выясним, какая страна смогла набрать наибольшее число медалей, отсортировав сразу таблицу по новому столбцу по убыванию, с помощью функции `sort`. Также используем символ `\`, чтобы разбить команду на несколько строк для улучшения читаемости.<jupyter_code>olympics3 = olympics2\
    .addfield('total', lambda x: int(x['gold']) + int(x['silver']) + int(x['bronze']))\
    .sort('total', reverse=True)
olympics3<jupyter_output><empty_output><jupyter_text>Видим, что в таблице есть сумма по всем странам, что нас не интересует в данной задаче. Можем выбрать из таблицы все строки, кроме строки со значением `country == Totals`. Воспользуемся функцией `select`.  Кроме того, дополнительно рассчитаем новый показатель - результативность страны, определив её как среднее число медалей за игру.<jupyter_code>olympics4 = olympics3\
    .select(lambda x: x.country != 'Totals')\
    .addfield('effectiveness', lambda x: round(x['total'] / float(x['games']), 2))
olympics4<jupyter_output><empty_output><jupyter_text>Сохраним полученные результаты в новый xlsx-файл.<jupyter_code>olympics4.toxlsx('olympics.xlsx')<jupyter_output><empty_output><jupyter_text>Готово! Теперь обработанный файл можно скачать или загрузить в BI-систему  Данные из открытого источника рынка акций Рассмотрим немного более продвинутый пример - получение данных из веб-сервиса по API. Это также делается очень просто с использованием библиотеки `requests`<jupyter_code>response = requests.get('https://www.quandl.com/api/v3/datasets/WIKI/AAPL.json?start_date=2017-05-01&end_date=2017-07-01')<jupyter_output><empty_output><jupyter_text>Посмотрим, что мы получили в ответ. Мы увидим данные в формате JSON, которые нужно будет промотать до конца<jupyter_code>stock_prices_json = response.json()
stock_prices_json<jupyter_output><empty_output><jupyter_text>Видим, что в полученном JSON сама таблица с данными лежит в разделе `dataset`. Посмотрим, какие в ней есть поля.<jupyter_code>stock_prices_json['dataset'].keys()<jupyter_output><empty_output><jupyter_text>Нас интересуют два поля ответа: `column_names`, который мы будем использовать в качестве заголовков таблицы, и `data`, содержащий все необходимые данные построчно. Для преобразования данных из объекта `dict` в таблицу `petl` сделаем следующее:  - Транспонируем содержимое `data`, чтобы превратить строки в столбцы  - Используем `column_names` в качестве значения параметра `header` функции `fromcolumns`<jupyter_code>stock_prices = etl.fromcolumns(stock_prices_json['dataset']['data']).skip(1)\
    .transpose()\
    .setheader(stock_prices_json['dataset']['column_names'])
stock_prices<jupyter_output><empty_output><jupyter_text>Уберём часть столбцов, все, содержащие `'Adj'`, переведём все значения в числа (где это возможно), вычислим разницу курса на определённую дату. В этом примере мы используем **List comprehension**, инструмент Python, который позволяет делать довольно сложные преобразования в наглядном функциональном стиле и без циклов.**List comprehension** - это запись вида `( for x in  if `, отбери те их них, для которых истинно ``, выполни над каждым `` и верни результаты в виде списка.Например, есть массив чисел `arr` и нужно отобрать из него четные числа и разделить их на 4. Это можно записать как `(x/4 for x in arr if x % 2 == 0)`<jupyter_code>stock_prices2 = stock_prices\
    .cutout(*(x for x in stock_prices.fieldnames() if 'Adj' in x))\
    .convertnumbers()\
    .addfield('Difference', lambda row: round(row.Close - row.Open, 2))

stock_prices2<jupyter_output><empty_output><jupyter_text>Сохраним полученную табличку в csv-файл.<jupyter_code>stock_prices2.tocsv('stock.csv')<jupyter_output><empty_output><jupyter_text>Через несколько секунд созданный файл появится в панели файлов слева, и вы сможете просмотреть или скачать его.  Данные из БД (PostgreSQL) В состав ViXtract входит предустановленная СУБД PostgreSQL, её удобно использовать как промежуточное хранилище данных, из которого их уже забирает BI-система. Похожие подходы могут быть использованы и с любой другой СУБД.Рассмотрим следующий пример.  Доступны данные о состояниях различных типов транспортных средств. В базе есть 2 таблицы:- `status_ts` содержит информацию о состояниях различных ТС  - `ts_types` содержит наименования типов ТС  Необходимо подготовить таблицу, содержащую валидные данные по бульдозерам:- В данных не должно быть пропусков  - Время указано в формате datetime  - Кроме данных по бульдозерам других нет  - Все состояния, кроме отсутствия данных  - Для каждого состояния рассчитана продолжительность<jupyter_code>connection = psycopg2.connect("dbname=demo user=demo password=<PASSWORD>", host='localhost')

statuses = etl.fromdb(connection, 'SELECT * FROM status_ts')
ts_types = etl.fromdb(connection, 'SELECT * FROM ts_types')

# Вспомогательные функции
# Определяем фильтр для исключения строк с пустыми значениями
row_without_nones = lambda x: all(x[field] != '' for field in statuses.fieldnames())
# Перевод отметки времени в формат datetime
to_datetime = lambda x: dt.fromtimestamp(int(x))<jupyter_output><empty_output><jupyter_text>Чтобы исключить строки с пропусками, используем функцию `select` и определенный выше фильтр `row_without_nones`<jupyter_code>statuses.select(row_without_nones)<jupyter_output><empty_output><jupyter_text>Переведём столбцы со временем в требуемый формат. Для этого необходимо воспользоваться функцией `convert`.Сразу можем добавить расчёт продолжительности функцией `addfield`.<jupyter_code>statuses.\
    convert('Начало', to_datetime).\
    convert('Окончание', to_datetime).\
    addfield('Продолжительность', lambda x: x['Окончание'] - x['Начало'])<jupyter_output><empty_output><jupyter_text>Объединим обе таблицы и выберем данные только по бульдозерам, сразу уберём строки с состоянием "Отсутствие данных".<jupyter_code>statuses.\
    join(ts_types, lkey='id ТС', rkey='id').\
    select(lambda x: 'Бульдозер' in x['Тип ТС'] and x['Состояние'] != 'Отсутствие данных')<jupyter_output><empty_output><jupyter_text>Все перечисленные операции можно произвести за раз, сформируем цепочку функций. Заметим, что столбец `id ТС` уже не требуется, его можно убрать функцией `cutout`.В дополнение ко всему отсортируем таблицу по времени начала состояний, применив `sort`.<jupyter_code>result = statuses.\
    join(ts_types, lkey='id ТС', rkey='id').\
    select(lambda x: 'Бульдозер' in x['Тип ТС'] and x['Состояние'] != 'Отсутствие данных').\
    select(row_without_nones).\
    convert('Начало', to_datetime).\
    convert('Окончание', to_datetime).\
    addfield('Продолжительность', lambda x: x['Окончание'] - x['Начало']).\
    convert('Начало', str).convert('Окончание', str).convert('Продолжительность', str).\
    cutout('id ТС').\
    sort('Начало')
result
# Импортируем библиотеку, позволяющую создавать таблицы в БД
import sqlalchemy as db

# Подготовим подключение
_user = 'demo'
_pass = '<PASSWORD>'
_host = 'localhost'
_port = 5432
target_db = db.create_engine(f"postgres://{_user}:{_pass}@{_host}:{_port}/etl")

# Пробуем пересоздать таблицу (удалить и создать заново). Если таблицы нет - просто создаем новую.
try:
    result.todb(target_db, 'status_cleaned', create=True, drop=True, sample=0)
except:
    result.todb(target_db, 'status_cleaned', create=True, sample=0)<jupyter_output><empty_output><jupyter_text>Проверим, что таблица создалась. Обратите внимание, что схема таблицы (типы полей, их названия и так далее) была создана полностью автоматически.<jupyter_code>etl.fromdb(connection, 'SELECT * FROM status_cleaned')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09664713228936497, 'fraction_numerical': 0.004803535402055913, 'mean_word_length': 6.626373626373627, 'pattern_counts': {'":': 0, '<': 87, '<?xml version=': 0, '>': 87, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '475033', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 4111, 'n_tokens_neox': 4693, 'n_words': 990, 'path': 'tutorials/1_Intro_to_petl.ipynb', 'repo_name': 'budda27/vixtract', 'size': '10397'}