Немного о Pivot tables в PostgreSQL и Python

Доброго времени суток.

Работая в институте, мне приходится иметь дело с большим количеством полу-структурированной информации. Здесь приставка «полу» значит, что в целом все данные похожи, но, как правило, распиханы в локальных папках на компьютерах у сотрудников, в .xls, .txt или в бинарном формате. Информация представляет из себя данные полученные с различных приборов( датчиков уровня, температуры, скорости течений, атмосферного давления, влажности и так далее до 20-30 различных параметров). Все приборы выгружают данные каждый в своем формате: либо в ascii либо бинарный формат, который потом обрабатывается, и, на выходе, снова получаются ascii. Ну вообщем все как всегда, вы и сами представляете весь этот хаос.

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

Обработка всего этого хозяйства — вполне стандартные вещь, ничего нового и интересного: проверка временных рядов на целостность(если нужна – интерполяция), построение кучи различных графиков, запуск различных моделей на этих данных, обработка вывода моделей(снова куча графиков), вывод статистики. О последней я и расскажу в этой статье.

Статистика

Все было бы не так печально если бы необходимо было получить не просто обычные показатели(минимум, максимум, среднее, отклонение и прочее), но еще и сводные таблицы (Pivot tables) различных видов.

Простое

У нас имеются данные по скорости, нужно построить таблицу вида:

Сам исходный ряд – это примерно 10к значений скорости. Я не гуру Excel и, кроме как на VBA, не знаю простого способа как такое сделать (нет, существует еще конечно же вариант протягивать формулы, потом сортировать и прочее, а так же делать сводные таблицы в самом excel, но каждый раз для каждого прибора это перебор).
В базе данных (я использую PostgresSQL) задача выглядит так:

СREATE TABLE tbldata
(
  dataid serial NOT NULL,
  measurementdate timestamp without time zone,
  velocity double precision,
  direction double precision
);
Select 100*(count(velocity)/(select count(*) from tbldata)::float) from tbldata velocity>0 and measurement<10;

Сколько градаций – столько и запросов, автоматизируется легко, работает быстро. Можно написать хранимую процедуру, которая будет принимать значения «от»,«шаг», «количество шагов» и которая будет выводить SETOF RECORDS. В целом задача решена.

Посложнее

Проблемы начинаются, когда необходима таблица вида:

То есть, нужно посчитать процент по градациям для двух переменных:

Select 100*(count(*)/(select count(*) from tbldata)::float) from tbldata where velocity >0 and velocity<10 and (direction>337.5 or direction<22.5);

Далее можно воспользоваться прекрасными crosstab. Для этого нужно немного модифицировать SQL добавив в него информацию о порядковом номере градации по скорости и порядковом номере градации по направлению:

select 1 as VelGrd,1 as DirGrid, 100*(count(*)/(select count(*) from tbldata)::float) from tbldata where velocity >0 and velocity<10 and (direction>337.5 or direction<22.5);

Дальше обернуть это все в функцию, в которую будут входить уже начало, шаг и количество шагов как для скорости так и для направления, которая выдаст UNION ALL всех этих запросов.
В итоге наш запрос будет выглядеть таким образом

select * from crosstab(‘fnMakePercentageByTwoVariables(0,10,10,22.5,45,8)’) as tbldata(velGrid int,dirGrid1 double precision, …,dirGrid8 double precision);

Совсем плохо

Если честно тут мое терпение уже кончилось, к предыдущему запросу нужно добавить три union и один join. А если захочется узнать квадратичное отклонение и еще что-нибудь? Еще один union и так далее пока хватает фантазии. Обеспеченность (фактически это кумулятивный процент снизу вверх) я так до сих пор и не знаю как посчитать, кроме как ручками. В целом если вид таблицы не будет меняться, то можно обойтись и этим.

Куда уж хуже

Но возникает же задача сравнить показание двух приборов. Тогда получается что еще каждую колонку еще разделить нужно на две:

Нам же интересно сравнить показания приборов сходу? Мне – да. Когда я подумал о таком сравнение, мне стало совсем грустно. Тут уж не было варианта быстрее, чем загрузить данные в SPSS, перекодировать все и вывести именно, то что нужно без тонн ненависти. А еще важно иметь таблицы, где по градациям скорости нужно иметь тоже кумулятивный процент, что тоже приходилось делать в Excel руками.

Решение

Мой основной инструмент для работы – python, использую Django для разработки приложений. И я в конце концов решил – к черту все, хватит боли и страданий. Хранимые процедуры быстро и хорошо, но поддерживать их для меня тяжко. Плюс скорость работы не является каким либо фактором – люди, которые имели с этим дело до меня, дай боже если не считали все руками. Так что подождать минуту – другую вместо того, что бы пару часов лопатить серии данных – это просто райское наслаждение.

И так спасение пришло в лице Pandas, о которой на Хабре я не нашел ни одного упоминания. Основная прелесть «панды» в их типах данных. Одномерный – Series. По сути в сериях есть все то, чего мне так не хватало в dict. Очень много сделано велосипедов, что бы расширить функциональность словаря и это все от незнания о панде.
Обращаться можно как к словарю по ключу s['a'], так и как s.a. Ко всем прочему, структуры панды работают с векторизованными функциями от numpy (без numpy.where жить не могу). Series складываются в DataFrame – уже двух мерная структура. DataFrame в Panel. Все это работает со слайсами, сабиндексами, набором индексов, со смешанными данными – в общем все божественно!

И вернемся к моей задаче получить нужную мне таблицу:

Для начала подключимся к постгресу и получим результат запроса

import psycopg2
import pandas as pd
import numpy as np
from pandas.io.sql import frame_query

conn_string = "host='localhost' dbname='test' user='postgres' password='**********'"
conn = psycopg2.connect(conn_string)
#frame_query принимает результат sql запроса, а вовзращает DataFrame
df = frame_query('select velocity,direction from tbldata', con=conn)

Создадим градации и лейблы для них

def makeVelocityLabels(c):
    ret = dict()
    for levidx in xrange(len(c.levels)):
        lev=c.levels[levidx]
        ret[levidx]=str(lev).replace('(',"").replace(", ","-").replace(']',"")
    return ret
velGrid = np.linspace(0,100,11)
dirGrid = np.linspace(-22.5,337.5,9)
dirLabels=['С','СВ',"В","ЮВ","Ю","ЮЗ","З","СЗ"]
velLabels = makeVelocityLabels(pd.cut(velGrid,velGrid)).values()

Пытливый взгляд заметил функцию cut. Она занимается тем, что из непрерывного ряда делает дискретный, фактически превращает переменную в тип Категории. Если раньше я всегда пользовался numpy.where, громоздя что-то типа:

for i in xrange(len(velGrid)-1):
    veloсity[np.where((velocity>velGrid[i]) & (velocity<velGrid[i+1]))]=velGrid[i]

то теперь, я это делаю

pd.cut(velocity,velGrid)

Далее, создадим новый DataFrame, положим в него категориальные переменные:

resultDf = pd.DataFrame(index=df.index,columns=df.columns)
#df[df.direction>337.5].direction - не будет работать.
#маленький костыль, что бы считать нормально направления.
df.direction[df.direction>337.5]-=360
resultDf.velocity = pd.cut(df.velocity,velGrid,labels=velLabels,include_lowest=True)
resultDf.direction = pd.cut(df.direction,dirGrid,labels=dirLabels,include_lowest=True)

Дальше начинается самое интересное.Так как в шапке таблицы будут направления ( фактически я пересекаю скорость на направления), то что бы считать статистики ( минимум, максимум и тп), мне нужно считать их так же для каждого направления.
Поймем сколько всего у нас есть измерений по каждой градации направления.

totalDir = resultDf.groupby("direction").size()
direction
В              97
З             503
С            1251
СВ            592
СЗ           2773
Ю             736
ЮВ            388
ЮЗ            773

Я бы рекомендовал пользоваться вместо size() функцией count(), которая вернет все значения, которые не missing.
Дальше нам нужно посчитать статистики, так же по направлениям, для максимума, среднего и построить собственно таблицу.

dfDGr = df.copy()
dfDGr.direction = resultDf.direction

meanVel = dfDGr.groupby("direction").mean()
maxVel = dfDGr.groupby("direction").max()
totalVel = resultDf.groupby("velocity").size()
pivot = 100 * pd.pivot_table(resultDf,rows='velocity',cols='direction',aggfunc=len, fill_value=0)/totalDir.sum()

direction         В         З          С        СВ         СЗ         Ю        ЮВ         ЮЗ
velocity                                                                                    
0-10       1.279348  6.916913  10.839308  8.224378  25.432307  2.066639  1.265289  10.066076
10-20      0.056235  0.126529   3.992689  0.070294  10.881485  3.303810  1.560523   0.632644
20-30      0.000000  0.014059   1.813581  0.014059   2.235344  3.388163  1.223113   0.112470
30-40      0.014059  0.000000   0.674821  0.000000   0.323352  1.237171  0.801350   0.014059
40-50      0.000000  0.014059   0.140588  0.000000   0.056235  0.224940  0.253058   0.042176
50-60      0.014059  0.000000   0.084353  0.014059   0.042176  0.112470  0.168705   0.000000
60-70      0.000000  0.000000   0.028118  0.000000   0.014059  0.014059  0.126529   0.000000
70-80      0.000000  0.000000   0.014059  0.000000   0.000000  0.000000  0.056235   0.000000

Осталось только присоединить статистики к табличке, округлить и поставить столбцы в нужном порядке.

def getCumulate(s):
    ns = s.copy()
    for val in xrange(s.size):
        ns[ns.index[val]]=np.sum(s[s.index[val]:s.index[s.size-1]])
    return ns

pivot["Сумма"] = 100 * np.round(totalVel/float(np.sum(totalVel)),3)
pivot['Обеспеченность,%']=getCumulate(pivot["Сумма"])

totalDirFr = pd.DataFrame(100*totalDir/float(np.sum(totalDir))).T
totalDirFr.index= ["Сумма"]
pivot = pivot.append(totalDirFr)
meanVelFr = meanVel.T
meanVelFr.index=["Средняя скорость"]
pivot = pivot.append(meanVelFr)
maxVelFr = maxVel.T
maxVelFr.index=["Максимальная скорость"]
pivot = pivot.append(maxVelFr)
#если забудем какую колонку - ее не будет, если добавим не существующуюю, то появится везде с NA
pivot=pivot.reindex(columns=np.append(dirLabels,["Сумма","Обеспеченность,%"]))
pivot = np.round(pivot,1).fillna(0)

                         С    СВ     В    ЮВ     Ю    ЮЗ     З    СЗ  Сумма  Обеспеченность,%
0-10                   10.8   8.2   1.3   1.3   2.1  10.1   6.9  25.4   66.1             100.0
10-20                   4.0   0.1   0.1   1.6   3.3   0.6   0.1  10.9   20.6              33.9
20-30                   1.8   0.0   0.0   1.2   3.4   0.1   0.0   2.2    8.8              13.3
30-40                   0.7   0.0   0.0   0.8   1.2   0.0   0.0   0.3    3.1               4.5
40-50                   0.1   0.0   0.0   0.3   0.2   0.0   0.0   0.1    0.7               1.4
50-60                   0.1   0.0   0.0   0.2   0.1   0.0   0.0   0.0    0.4               0.7
60-70                   0.0   0.0   0.0   0.1   0.0   0.0   0.0   0.0    0.2               0.3
70-80                   0.0   0.0   0.0   0.1   0.0   0.0   0.0   0.0    0.1               0.1
Сумма                  17.6   8.3   1.4   5.5  10.3  10.9   7.1  39.0    0.0               0.0
Средняя скорость       10.7   2.6   2.9  22.6  19.9   4.3   3.3   9.2    0.0               0.0
Максимальная скорость  76.2  57.8  50.2  78.3  61.1  48.8  42.9  62.5    0.0               0.0

Резюме:

В итоге получилось именно то, что нужно. Меньшей кровью? — Да. Проще модифицировать? — Да.
На самом деле весь геморрой состоит в том, что переменные не категории, если на вход подавались бы сразу они, то было бы все на много проще. На моем слабеньком ноутбуке весь скрипт отрабатывает за 0.2 сек для 7113 значений.

Спасибо за внимание.

Автор: norecces

Источник