Обработка данных с помощью Flask: как это сделать с помощью языка SQL? Имеет ли смысл использовать панд?
Совершенно новый для SQL, и работа с колбой и sqlalchemy здесь моя проблема (надеюсь, это не слишком долго)
Обзор:
У меня есть таблица SQL, структурированная так:
name vector axis value unit ref
----------------------------------------------------------------
name1 v1 W 46504 psi ref1
name1 v1 L 51757 psi ref1
name1 v2 W 127 psi another ref
name1 v2 L 403 psi ref1
name2 ...
Моя цель состоит в том, чтобы "расстегнуть" результаты, чтобы я мог получить по одной строке на name пока unit а также ref подобные.
например, я хочу получить что-то вроде:
name v1-L v2-W v1-L v2-W unit ref
--------------------------------------------------------------
name1 46504 127 403 psi ref1
name1 127 psi another ref
name2...
Попытка с sqlalchemy:
до сих пор я пытался присоединиться к той же таблице на основе "имени" - пока нет проверки на unit ни ref -:
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, Table, Text, Date, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, aliased
engine = create_engine('sqlite://') #, echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
class Test(Base):
__tablename__ = 'test'
id = Column(Integer, primary_key=True)
name = Column(String(32))
vector = Column(String(32))
axis = Column(String(1))
value = Column(Float)
unit = Column(String(16), default='psi')
ref = Column(String(32))
Base.metadata.create_all(engine)
# some data to play with
data = [{'name':'name1', 'vector':'v1', 'axis':'W', 'value':'46504', 'unit':'psi', 'ref':'ref1'},
{'name':'name1', 'vector':'v1', 'axis':'L', 'value':'51757', 'unit':'psi', 'ref':'ref1'},
{'name':'name1', 'vector':'v2', 'axis':'W', 'value':'127', 'unit':'psi', 'ref':'another ref'},
{'name':'name1', 'vector':'v2', 'axis':'L', 'value':'403', 'unit':'psi', 'ref':'ref1'},
{'name':'name2', 'vector':'v1', 'axis':'L', 'value':'23000', 'unit':'psi', 'ref':'ref1'},
{'name':'name2', 'vector':'v1', 'axis':'W', 'value':'27000', 'unit':'psi', 'ref':'ref1'},
{'name':'name2', 'vector':'v2', 'axis':'L', 'value':'523', 'unit':'psi', 'ref':'ref1'},
{'name':'name2', 'vector':'v2', 'axis':'W', 'value':'217', 'unit':'psi', 'ref':'ref1'},]
for dic in data:
t = Test(**dic)
session.add(t)
session.commit()
test_alias = aliased(Test)
q = session.query(Test.id, Test.name, Test.value).filter(Test.vector == 'v1').\
join(test_alias, Test.name).filter(test_alias.vector == 'v2')
print q
Пример использования панд:
Вот что я хотел бы использовать pandas библиотека.
import pandas as pd
q = session.query(Test).order_by(Test.id) # that is the default table
row2dict = lambda r: {c.name: getattr(r, c.name) for c in r.__table__.columns}
df = pd.DataFrame([row2dict(i) for i in q])
df = df.drop(['id'], axis=1)
df = df.set_index(['ref', 'unit', 'name', 'vector', 'axis']).sort()
df = df.unstack(level=-2).unstack(level=-1)['value'].reset_index()
print(df)
vector ref unit name v1 v2
axis L W L W
0 another ref psi name1 NaN NaN NaN 127
1 ref1 psi name1 51757 46504 403 NaN
2 ref1 psi name2 23000 27000 523 217
... что не так далеко от моего ожидания.
Так имеет ли смысл делать это на языке SQL? Таким образом, мой следующий вопрос: использование Flask Framework, имеет ли смысл делать эти данные, используя панды? или я должен придерживаться языка SQL?
2 ответа
Панды, вероятно, лучше для такого рода вещей. Могут быть некоторые более экзотические функции SQL, которые выполняют такое преобразование, но я не уверен. Ниже приведен пример упрощенного подхода, который заключается в том, чтобы просто объединить представление каждого из них вместе. Это приходит на ум, так как вы создаете строки, которые имеют "виртуальные" столбцы, полученные из данных, что предполагает создание составной строки из более фундаментальных строк. Таким образом, приведенный ниже подход разбивает набор строк, соответствующих каждому из четырех условий: v1/W, v1/L, v2/W, v2/L. Теперь, если на практике есть какое-то число "vN", именно здесь трансформационные способности панд, вероятно, более уместны.
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, aliased
Base = declarative_base()
class Test(Base):
__tablename__ = 'test'
id = Column(Integer, primary_key=True)
name = Column(String(32))
vector = Column(String(32))
axis = Column(String(1))
value = Column(Float)
unit = Column(String(16), default='psi')
ref = Column(String(32))
engine = create_engine('sqlite://', echo=True)
session = Session(engine)
Base.metadata.create_all(engine)
# some data to play with
data = [{'name':'name1', 'vector':'v1', 'axis':'W', 'value':'46504', 'unit':'psi', 'ref':'ref1'},
{'name':'name1', 'vector':'v1', 'axis':'L', 'value':'51757', 'unit':'psi', 'ref':'ref1'},
{'name':'name1', 'vector':'v2', 'axis':'W', 'value':'127', 'unit':'psi', 'ref':'another ref'},
{'name':'name1', 'vector':'v2', 'axis':'L', 'value':'403', 'unit':'psi', 'ref':'ref1'},
{'name':'name2', 'vector':'v1', 'axis':'L', 'value':'23000', 'unit':'psi', 'ref':'ref1'},
{'name':'name2', 'vector':'v1', 'axis':'W', 'value':'27000', 'unit':'psi', 'ref':'ref1'},
{'name':'name2', 'vector':'v2', 'axis':'L', 'value':'523', 'unit':'psi', 'ref':'ref1'},
{'name':'name2', 'vector':'v2', 'axis':'W', 'value':'217', 'unit':'psi', 'ref':'ref1'},]
for dic in data:
t = Test(**dic)
session.add(t)
session.commit()
axis_w = session.query(Test).filter(Test.axis == "W")
axis_l = session.query(Test).filter(Test.axis == "L")
axis_v1_w = axis_w.filter(Test.vector == "v1").subquery()
axis_v1_l = axis_l.filter(Test.vector == "v1").subquery()
axis_v2_w = axis_w.filter(Test.vector == "v2").subquery()
axis_v2_l = axis_l.filter(Test.vector == "v2").subquery()
def join_axes(left, right):
return and_(
left.c.unit == right.c.unit,
left.c.ref == right.c.ref
)
name_unit_ref = session.query(Test.name, Test.unit, Test.ref).distinct().subquery()
q = session.query(name_unit_ref.c.name,
axis_v1_w.c.value.label('v1_w'),
axis_v1_l.c.value.label('v1_l'),
axis_v2_w.c.value.label('v2_w'),
axis_v2_l.c.value.label('v2_l'),
name_unit_ref.c.unit,
name_unit_ref.c.ref
).\
outerjoin(axis_v1_w, join_axes(name_unit_ref, axis_v1_w)).\
outerjoin(axis_v1_l, join_axes(name_unit_ref, axis_v1_l)).\
outerjoin(axis_v2_w, join_axes(name_unit_ref, axis_v2_w)).\
outerjoin(axis_v2_l, join_axes(name_unit_ref, axis_v2_l))
for row in q:
print row
Это более "экзотический" SQL для вашего вопроса. Я использую SQL Server 2008+, чтобы получить команду PIVOT. Я не уверен, если это точно охватывает случай для ваших столбцов, хотя.
--Setup the table and data
create table #t
(
name nvarchar(100) not null
,vector char(2) not null
,axis char(1) not null
,value int not null
,unit char(3) not null
,ref nvarchar(100) not null
);
insert into #t values ('name1','v1','W', 46504,'psi','ref1');
insert into #t values ('name1','v1','L', 51757,'psi','ref1');
insert into #t values ('name1','v2','W', 127,'psi','another ref');
insert into #t values ('name1','v2','L', 403,'psi','ref1');
-- Retrieve the data using SQL Server Pivot
-- http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
select *
from
(select
name
,vector + '-' + axis as vector_axis
,value
,unit
,ref
from #t) as t
pivot (sum(value) for vector_axis IN
([v1-w]
,[v1-L]
,[v2-W]
,[v2-L]
)
) as p
