我有一些数据。在SQLite数据库中,它的224,000行。我想从中提取时间序列信息以提供数据可视化工具。从本质上讲,数据库中的每一行都是一个事件,该事件具有(自严格时期以来)以秒为单位的时间- 日期组以及一个负责该事件的名称(除其他严格相关的事项外)。我想提取每个名称每个星期在数据库中有多少个事件。
这很简单:
SELECT COUNT(*), name, strf("%W:%Y", time, "unixepoch") FROM events GROUP BY strf("%W:%Y", time, "unixepoch"), name ORDER BY time
我们得到了大约六千行数据
count name week:year 23............ fudge.......23:2009 etc...
但是我不想在每个星期都为每个名字排一行-我想为每个名字都排一个一行,每个星期都有一列,像这样:
Name 23:2009 24:2009 25:2009 fudge........23............6............19 fish.........1.............0............12 etc...
现在,监视过程已经运行了69周,唯一名称的数量为502。显然,我不热衷于涉及对所有列进行硬编码而对行进行硬编码的任何解决方案。我对涉及迭代的任何事情都不太了解,例如使用python的executemany(),但是我愿意在必要时接受它。SQL是明智的,该死的。
在这种情况下,一种好的方法是不要将SQL推到令人费解,难以理解和维护的地步。让SQL尽其所能,然后在Python中对查询结果进行后处理。
这是我编写的简单交叉表生成器的简化版本。完整版提供行/列/总计。
您会注意到,它具有内置的“分组依据”-原始用例用于汇总使用Python和xlrd从Excel文件获得的数据。
您提供的row_key和col_key不必像示例中那样是字符串。它们可以是元组(例如(year, week)您的情况),也可以是整数(例如,您具有字符串列名到整数排序键的映射)。
row_key
col_key
(year, week)
import sys class CrossTab(object): def __init__( self, missing=0, # what to return for an empty cell. Alternatives: '', 0.0, None, 'NULL' ): self.missing = missing self.col_key_set = set() self.cell_dict = {} self.headings_OK = False def add_item(self, row_key, col_key, value): self.col_key_set.add(col_key) try: self.cell_dict[row_key][col_key] += value except KeyError: try: self.cell_dict[row_key][col_key] = value except KeyError: self.cell_dict[row_key] = {col_key: value} def _process_headings(self): if self.headings_OK: return self.row_headings = list(sorted(self.cell_dict.iterkeys())) self.col_headings = list(sorted(self.col_key_set)) self.headings_OK = True def get_col_headings(self): self._process_headings() return self.col_headings def generate_row_info(self): self._process_headings() for row_key in self.row_headings: row_dict = self.cell_dict[row_key] row_vals = [row_dict.get(col_key, self.missing) for col_key in self.col_headings] yield row_key, row_vals def dump(self, f=None, header=None, footer='', ): if f is None: f = sys.stdout alist = self.__dict__.items() alist.sort() if header is not None: print >> f, header for attr, value in alist: print >> f, "%s: %r" % (attr, value) if footer is not None: print >> f, footer if __name__ == "__main__": data = [ ['Rob', 'Morn', 240], ['Rob', 'Aft', 300], ['Joe', 'Morn', 70], ['Joe', 'Aft', 80], ['Jill', 'Morn', 100], ['Jill', 'Aft', 150], ['Rob', 'Aft', 40], ['Rob', 'aft', 5], ['Dozy', 'Aft', 1], # Dozy doesn't show up till lunch-time ['Nemo', 'never', -1], ] NAME, TIME, AMOUNT = range(3) xlate_time = {'morn': "AM", "aft": "PM"} print ctab = CrossTab(missing=None, ) # ctab.dump(header='=== after init ===') for s in data: ctab.add_item( row_key=s[NAME], col_key= xlate_time.get(s[TIME].lower(), "XXXX"), value=s[AMOUNT]) # ctab.dump(header='=== after add_item ===') print ctab.get_col_headings() # ctab.dump(header='=== after get_col_headings ===') for x in ctab.generate_row_info(): print x
输出:
['AM', 'PM', 'XXXX'] ('Dozy', [None, 1, None]) ('Jill', [100, 150, None]) ('Joe', [70, 80, None]) ('Nemo', [None, None, -1]) ('Rob', [240, 345, None])