def export_csv_pivot(request, entidad=1, ano=str(date.today().year)): consumos = Consumo.objects.filter(entidad__pk=entidad, ano=ano) from collections import namedtuple LineaDetalle = namedtuple('LineaDetalle',[u'Año', "Mes", 'Local_o_Vehiculo', "Consumo", "Valor"]) df = DataFrame() for c in consumos: if c.content_type.id == 16: denominacion = Local.objects.get(pk=c.object_id).denominacion else: denominacion = Vehiculo.objects.get(pk=c.object_id).denominacion df.insert(LineaDetalle(c.ano, c.mes, denominacion.encode("utf-8"), c.medida.denominacion.encode("utf-8"), c.valor)._asdict()) pt = df.pivot("Valor", ['Local_o_Vehiculo','Consumo'], ['Mes']) # get the response object, this can be used as a stream. response = HttpResponse(mimetype='text/csv') # force download. response['Content-Disposition'] = 'attachment;filename=export.csv' response.write(pt) return response
def test2(self): R = { 'A': [ -8.0, -7.0, -3.0, -2.0, -1.0, 1.0, 2.0, 3.0, 4.0, 9.0, -10.0, -9.0, -6.0, -5.0, -4.0, 0.0, 5.0, 6.0, 7.0, 8.0 ], 'B': [ 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0 ] } a = [ 4, 8, 1, 5, -7, -5, 9, 7, -8, -10, -1, -4, 3, 0., -2, 6, 2, -9, -3, -6 ] b = [1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2] df = DataFrame() for A, B in zip(a, b): df.insert({'A': A, 'B': B}) df.sort(['B', 'A']) for d, r in zip(df['A'], R['A']): self.assertAlmostEqual(d, r) for d, r in zip(df['B'], R['B']): self.assertAlmostEqual(d, r)
def test4(self): """method='full', aggregate=count, invalid row""" R = """\ count(id) member Name=name1, Name=name1, Name=name2, Name=name2, Total Year=2010 Year=2011 Year=2010 Year=2011 ====================================================================== N 0 1 -- 0 1 Y 1 0 -- 1 2 ====================================================================== Total 1 1 -- 1 3 """ df = DataFrame() df.insert({'id': 0, 'Name': 'name1', 'Year': 2010, 'member': 'Y'}) df.insert({'id': 1, 'Name': 'name1', 'Year': 2011, 'member': 'N'}) df.insert({'id': 2, 'Name': 'name2', 'Year': 2011, 'member': 'Y'}) my_pivot = df.pivot('id', rows=['member'], cols=['Name', 'Year'], aggregate='count', method='full') self.assertEqual(R, str(my_pivot))
def test3(self): """method='full', aggregate=count, invalid row""" R = """\ count(id) Name Year member=N member=Y Total ========================================== name1 2010 0 1 1 name1 2011 1 0 1 name2 2010 -- -- -- name2 2011 0 1 1 ========================================== Total 1 2 3 """ df = DataFrame() df.insert({'id': 0, 'Name': 'name1', 'Year': 2010, 'member': 'Y'}) df.insert({'id': 1, 'Name': 'name1', 'Year': 2011, 'member': 'N'}) df.insert({'id': 2, 'Name': 'name2', 'Year': 2011, 'member': 'Y'}) my_pivot = df.pivot('id', rows=['Name', 'Year'], cols=['member'], aggregate='count', method='full') self.assertEqual(R, str(my_pivot))
def test1(self): df = DataFrame() with self.assertRaises(Exception) as cm: df.insert([1, 2, 3, 4]) self.assertEqual(str(cm.exception), 'row must be mappable type')
def test4(self): df = DataFrame() df.insert([('GROUP', 'AA'), ('VAL', 1)]) with self.assertRaises(Exception) as cm: df.validate(lambda x: x in ['AA', 'AB', 'LAB']) self.assertEqual(str(cm.exception), 'criteria must be mappable type')
def test1(self): df=DataFrame() with self.assertRaises(Exception) as cm: df.insert([1,2,3,4]) self.assertEqual(str(cm.exception), 'row must be mappable type')
def test3(self): df = DataFrame() df.insert({'A': 1, 'B': 2}) with self.assertRaises(Exception) as cm: df.insert({'A': 1, 'B': 2, 'C': 3}) self.assertEqual(str(cm.exception), 'row must have the same keys as the table')
def test4(self): df=DataFrame() df.insert([('GROUP','AA'),('VAL',1)]) with self.assertRaises(Exception) as cm: df.validate(lambda x: x in ['AA', 'AB', 'LAB']) self.assertEqual(str(cm.exception), 'criteria must be mappable type')
def test3(self): df=DataFrame() df.insert({'A':1, 'B':2}) with self.assertRaises(Exception) as cm: df.insert({'A':1, 'B':2, 'C':3}) self.assertEqual(str(cm.exception), 'row must have the same keys as the table')
def test3(self): tupa = ('a1','a2','a3') tupb = ('a1','b2','b3') df=DataFrame() df.insert([(('a1','a2','a3'),34), (('a1','b2','b3'),1)]) df.insert([(('a1','a2','a3'),34), (('a1','b2','b3'),2)]) df.insert([(('a1','a2','a3'),42), (('a1','b2','b3'),3)]) namea,nameb = df.keys() self.assertEqual(namea, tupa) self.assertEqual(nameb, tupb)
def test0(self): df=DataFrame() conditionsDict=DictSet({'A':[10,20,40,80], 'B':[100,800], 'rep':range(10)}) for A,B,rep in conditionsDict.unique_combinations(): df.insert({'A':A, 'B':B,'rep':rep}) for d,r in zip(df['A'],_rep_generator([10,20,40,80],4,20)): self.assertAlmostEqual(d,r) for d,r in zip(df['B'],_rep_generator([100,800],8,10)): self.assertAlmostEqual(d,r) for d,r in zip(df['rep'],_rep_generator(range(10),8,1)): self.assertAlmostEqual(d,r)
def test0(self): df=DataFrame() conditionsDict=DictSet({'A':[10,20,40,80], 'B':[100,800], 'rep':list(range(10))}) for A,B,rep in conditionsDict.unique_combinations(): df.insert({'A':A, 'B':B,'rep':rep}) for d,r in zip(df['A'],_rep_generator([10,20,40,80],4,20)): self.assertAlmostEqual(d,r) for d,r in zip(df['B'],_rep_generator([100,800],8,10)): self.assertAlmostEqual(d,r) for d,r in zip(df['rep'],_rep_generator(list(range(10)),8,1)): self.assertAlmostEqual(d,r)
def test2(self): R={'A': [-8.0, -7.0, -3.0, -2.0, -1.0, 1.0, 2.0, 3.0, 4.0, 9.0, -10.0, -9.0, -6.0, -5.0, -4.0, 0.0, 5.0, 6.0, 7.0, 8.0], 'B': [1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0]} a=[4, 8, 1, 5, -7, -5, 9, 7, -8, -10, -1, -4, 3, 0., -2, 6, 2, -9, -3, -6] b=[1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2] df=DataFrame() for A,B in zip(a,b): df.insert({'A':A, 'B':B}) df.sort(['B','A']) for d,r in zip(df['A'],R['A']): self.assertAlmostEqual(d,r) for d,r in zip(df['B'],R['B']): self.assertAlmostEqual(d,r)
def test1(self): """method='valid', aggregate=count, invalid row""" R = """\ count(id) Name Year member=N member=Y Total ========================================== name1 2010 0 1 1 name1 2011 1 0 1 name2 2011 0 1 1 ========================================== Total 1 2 3 """ df = DataFrame() df.insert({'id':0,'Name':'name1','Year':2010,'member':'Y'}) df.insert({'id':1,'Name':'name1','Year':2011,'member':'N'}) df.insert({'id':2,'Name':'name2','Year':2011,'member':'Y'}) my_pivot = df.pivot('id',rows = ['Name','Year'], cols = ['member'], aggregate='count') self.assertEqual(R,str(my_pivot))
def test2(self): """method='valid', aggregate=count, invalid col""" R = """\ count(id) member Name=name1, Name=name1, Name=name2, Total Year=2010 Year=2011 Year=2011 ======================================================== N 0 1 0 1 Y 1 0 1 2 ======================================================== Total 1 1 1 3 """ df = DataFrame() df.insert({'id':0,'Name':'name1','Year':2010,'member':'Y'}) df.insert({'id':1,'Name':'name1','Year':2011,'member':'N'}) df.insert({'id':2,'Name':'name2','Year':2011,'member':'Y'}) my_pivot = df.pivot('id',rows = ['member'], cols = ['Name','Year'], aggregate='count') self.assertEqual(R,str(my_pivot))
def test4(self): """method='full', aggregate=tolist, invalid col""" R = """\ tolist(id) member Name=name1, Name=name1, Name=name2, Name=name2, Year=2010 Year=2011 Year=2010 Year=2011 ================================================================== N [None, None] [1.0, 1.0] [None, None] [None, None] Y [0.0, 0.0] [None, None] [None, None] [2.0, 2.0] """ df = DataFrame() df.insert({'id':0,'Name':'name1','Year':2010,'member':'Y','rep':1}) df.insert({'id':1,'Name':'name1','Year':2011,'member':'N','rep':1}) df.insert({'id':2,'Name':'name2','Year':2011,'member':'Y','rep':1}) df.insert({'id':0,'Name':'name1','Year':2010,'member':'Y','rep':2}) df.insert({'id':1,'Name':'name1','Year':2011,'member':'N','rep':2}) df.insert({'id':2,'Name':'name2','Year':2011,'member':'Y','rep':2}) my_pivot = df.pivot('id',rows = ['member'], cols = ['Name','Year'], aggregate='tolist', method='full') ## print(my_pivot) self.assertEqual(R,str(my_pivot))
def test3(self): """method='full', aggregate=tolist, invalid row""" R = """\ tolist(id) Name Year member=N member=Y ========================================== name1 2010 [None, None] [0.0, 0.0] name1 2011 [1.0, 1.0] [None, None] name2 2010 [None, None] [None, None] name2 2011 [None, None] [2.0, 2.0] """ df = DataFrame() df.insert({'id':0,'Name':'name1','Year':2010,'member':'Y','rep':1}) df.insert({'id':1,'Name':'name1','Year':2011,'member':'N','rep':1}) df.insert({'id':2,'Name':'name2','Year':2011,'member':'Y','rep':1}) df.insert({'id':0,'Name':'name1','Year':2010,'member':'Y','rep':2}) df.insert({'id':1,'Name':'name1','Year':2011,'member':'N','rep':2}) df.insert({'id':2,'Name':'name2','Year':2011,'member':'Y','rep':2}) my_pivot = df.pivot('id',rows = ['Name','Year'], cols = ['member'], aggregate='tolist', method='full') ## print(my_pivot) self.assertEqual(R,str(my_pivot))
def test3(self): R = """c b{L@^hsa aj} a(1%32@) =========================== 1 a 34 2 b 34 3 c 42 4 d 34 5 e 45 6 f 34 """ df=DataFrame() df.PRINTQUERIES = True df.insert({'a(1%32@)':34,'b{L@^hsa aj}':'a','c':1}) df.insert({'a(1%32@)':34,'b{L@^hsa aj}':'b','c':2}) df.insert({'a(1%32@)':42,'b{L@^hsa aj}':'c','c':3}) df.insert({'a(1%32@)':34,'b{L@^hsa aj}':'d','c':4}) df.insert({'a(1%32@)':45,'b{L@^hsa aj}':'e','c':5}) df.insert({'a(1%32@)':34,'b{L@^hsa aj}':'f','c':6}) self.assertEqual(R, str(df))
def test3(self): """method='full', aggregate=tolist, invalid row""" R = """\ tolist(id) Name Year member=N member=Y ========================================== name1 2010 [None, None] [0.0, 0.0] name1 2011 [1.0, 1.0] [None, None] name2 2010 [None, None] [None, None] name2 2011 [None, None] [2.0, 2.0] """ df = DataFrame() df.insert({ 'id': 0, 'Name': 'name1', 'Year': 2010, 'member': 'Y', 'rep': 1 }) df.insert({ 'id': 1, 'Name': 'name1', 'Year': 2011, 'member': 'N', 'rep': 1 }) df.insert({ 'id': 2, 'Name': 'name2', 'Year': 2011, 'member': 'Y', 'rep': 1 }) df.insert({ 'id': 0, 'Name': 'name1', 'Year': 2010, 'member': 'Y', 'rep': 2 }) df.insert({ 'id': 1, 'Name': 'name1', 'Year': 2011, 'member': 'N', 'rep': 2 }) df.insert({ 'id': 2, 'Name': 'name2', 'Year': 2011, 'member': 'Y', 'rep': 2 }) my_pivot = df.pivot('id', rows=['Name', 'Year'], cols=['member'], aggregate='tolist', method='full') ## print(my_pivot) self.assertEqual(R, str(my_pivot))
import pylab import numpy as np from pyvttbl import DataFrame from sdt_metrics import SDT, HI,MI,CR,FA, bppd, c, loglinear_bppd # # Make up data # df = DataFrame() # more or less a dictionary with numpy arrays P = 10 # number of positive events N = 10 # number of negative events for hi in _xrange(P+1): for fa in _xrange(N+1): df.insert([(HI,hi),(MI,P-hi),(CR,N-fa),(FA,fa)]) # calculate some metrics df['bppd'] = bppd(df[HI],df[MI],df[CR],df[FA]) df['c'] = c(df[HI],df[MI],df[CR],df[FA]) df['loglinear_bddp'] = loglinear_bppd(df[HI],df[MI],df[CR],df[FA]) df['residuals'] = df['loglinear_bddp']-df['bppd'] # exclude boundary conditions from this second table df2 = df.where('HI != 0 and MI != 0 and CR != 0 and FA != 0') # # perform analyses # df.scatter_matrix(['bppd','loglinear_bddp','c'], diagonal='kde') df.scatter_plot('c', 'bppd', trend='linear')
def test4(self): df = DataFrame() df.insert([('A', 1.23), ('B', 2), ('C', 'A')]) self.assertEqual(df.types(), ['real', 'integer', 'text'])
def pivot(self, r, **attr): """ Method to generate contingency tables @param r: the S3Request instance @param attr: controller attributes for the request @todo: warn if dimension/fact doesn't belong to table @todo: support component fields @todo: support automatic generation of dimension classes @todo: optimize inner loops @todo: make facts inline-expandable """ db = current.db T = current.T response = current.response resource = self.resource table = resource.table pkey = table._id.name # Get the fields fields = [] if self.rows: fields.extend([f for f in self.rows if f not in fields]) if self.cols: fields.extend([f for f in self.cols if f not in fields]) if self.fact and self.fact not in fields: fields.append(self.fact) list_fields = list(fields) lfields, join = self.get_list_fields(table, list_fields) lfields = Storage([(f.fieldname, f) for f in lfields]) # Get the items items = self.sqltable(list_fields, as_list=True, start=None, limit=None) count = None if items: # Generate the data frame aggregate = self.aggregate == "list" and "group_concat" or \ self.aggregate from pyvttbl import DataFrame df = DataFrame() fmap = Storage() item_list = [] for row in items: try: frow = self.__extract(row, fields, resource, lfields, fmap) except KeyError: e = sys.exc_info()[1] if hasattr(e, "message"): e = e.message r.error(400, e, next=r.url(vars=[])) frow = self.__expand(frow) for item in frow: if aggregate in ("group_concat", "count"): i = Storage(item) if self.fact != pkey: del i[pkey] # De-duplicate if i not in item_list: item_list.append(i) df.insert(aggregate == "count" and i or item) else: df.insert(item) # In group_concat, always group primary keys, and then use # fmap to link individual fact values to their corresponding # records if aggregate == "group_concat" and fmap: fact = pkey else: # Otherwise group the fact value, and delete fmap fact = self.fact fmap = None # Pivoting try: pt = df.pivot(fact, self.rows, self.cols, aggregate=aggregate) except: r.error(400, "Could not generate contingency table", next=r.url(vars=[])) if aggregate == "count" or pt.grand_tot is None: count = len(item_list) # Generate output table items = S3ContingencyTable(r, self.resource, pt, rows=self.rows, cols=self.cols, fact=self.fact, lfields=lfields, fmap=fmap, count=count, _id="list", _class="dataTable display") else: # No items to analyze items = self.crud_string(self.tablename, "msg_no_match") return dict(items=items)
import math from pyvttbl import DataFrame import sdt_metrics from sdt_metrics import SDT, HI, MI, CR, FA # something to put data in df = DataFrame() # make up data P = 10 N = 10 for hi in xrange(P + 1): for fa in xrange(N + 1): df.insert([(HI, hi), (MI, N - hi), (CR, N - fa), (FA, fa)]) metrics = [ 'aprime', 'amzs', 'bpp', 'bph', 'bppd', 'bmz', 'b', 'dprime', 'beta', 'c', 'accuracy', 'mcc', 'precision', 'recall', 'f1', 'ppv', 'npv', 'fdr', 'sensitivity', 'specificity', 'mutual_info', 'loglinear_bppd', 'loglinear_dprime', 'loglinear_beta', 'loglinear_c' ] for metric in metrics: func = getattr(sdt_metrics, metric) df[metric] = func(df[HI], df[MI], df[CR], df[FA]) df['log(beta)'] = np.log(df['beta']) df['log(bmz)'] = np.log(df['bmz']) df['sgn(bph)log(abs(bph)+1)'] = np.log(np.abs(df['bph']) + 1)
# figure out pid and independent variable conditions pid = daq.etc['pid'] trial = epoch / 10 scenario = daq.etc['scen_order'][trial] section = epoch % 10 # pack pid and IV conditions into OrderedDict row = OrderedDict([('pid', pid), ('trial', trial), ('scenario', scenario), ('section', section)]) for (dv, indx) in dvs: vec = daq[dv][indx,fslice].flatten() row['%s_mean'%dv] = np.mean(vec) row['%s_min'%dv] = np.min(vec) row['%s_max'%dv] = np.max(vec) row['%s_range'%dv] = row['%s_max'%dv] - row['%s_min'%dv] row['%s_amean'%dv] = np.mean(np.abs(vec)) row['%s_sd'%dv] = np.std(vec) row['%s_rms'%dv] = np.linalg.norm(vec)/np.sqrt(len(vec)) # insert the row into the dataframe df.insert(row) df.write(fname='collaborated_ts.csv') print('\nDone.\n\ncollaborating timeseries measureas took %.1f s'%(time.time()-t0))
for (epoch, fslice) in daq.etc['epochs'].items(): # figure out pid and independent variable conditions pid = daq.etc['pid'] trial = epoch / 10 scenario = daq.etc['scen_order'][trial] section = epoch % 10 # pack pid and IV conditions into OrderedDict row = OrderedDict([('pid', pid), ('trial', trial), ('scenario', scenario), ('section', section)]) for (dv, indx) in dvs: vec = daq[dv][indx, fslice].flatten() row['%s_mean' % dv] = np.mean(vec) row['%s_min' % dv] = np.min(vec) row['%s_max' % dv] = np.max(vec) row['%s_range' % dv] = row['%s_max' % dv] - row['%s_min' % dv] row['%s_amean' % dv] = np.mean(np.abs(vec)) row['%s_sd' % dv] = np.std(vec) row['%s_rms' % dv] = np.linalg.norm(vec) / np.sqrt(len(vec)) # insert the row into the dataframe df.insert(row) df.write(fname='collaborated_ts.csv') print('\nDone.\n\ncollaborating timeseries measureas took %.1f s' % (time.time() - t0))
def apply_method(self, r, **attr): db = current.db response = current.response if not PYVTTBL: r.error(501, "Function not available on this server") if r.interactive: # Get rows, cols, fact and aggregate from URL if "row" in r.get_vars: rowname = r.get_vars["row"] self.rows = [rowname] else: r.error(400, "Missing row parameter") if "col" in r.get_vars: colname = r.get_vars["col"] self.cols = [colname] else: r.error(400, "Missing col parameter") if "fact" in r.get_vars: self.fact = r.get_vars["fact"] else: r.error(400, "Missing fact parameter") if "aggregate" in r.get_vars: self.aggregate = r.get_vars["aggregate"] else: self.aggregate = "group_concat" # Get the fields fields = [] if self.rows: fields.extend([f for f in self.rows if f not in fields]) if self.cols: fields.extend([f for f in self.cols if f not in fields]) if self.fact and self.fact not in fields: fields.append(self.fact) list_fields = list(fields) lfields, join = self.get_list_fields(self.resource.table, list_fields) lfields = Storage([(f.fieldname, f) for f in lfields]) # Get the items items = self.sqltable(list_fields, as_list=True) # Map the items into a data frame df = DataFrame() for row in items: frow = Storage() for field in fields: lfield = lfields[field] tname = lfield.tname fname = lfield.fname if fname in row: value = row[fname] elif tname in row and fname in row[tname]: value = row[tname][fname] else: value = None frow[field] = value df.insert(frow) # Pivot table try: pt = df.pivot(self.fact, self.rows, self.cols, aggregate=self.aggregate) except: r.error(400, "Could not generate contingency table", next=r.url(method="")) items = S3ContingencyTable(pt, rows=self.rows, cols=self.cols, fact=self.fact, lfields=lfields, _id="list", _class="dataTable display") output = dict(items=items) else: r.error(501, self.manager.ERROR.BAD_FORMAT) response.s3.no_sspag = True response.view = self._view(r, "list.html") return output
def test4(self): df=DataFrame() df.insert([('A',1.23), ('B',2), ('C','A')]) self.assertEqual(df.types(), ['real', 'integer', 'text'])
def test4(self): """method='full', aggregate=tolist, invalid col""" R = """\ tolist(id) member Name=name1, Name=name1, Name=name2, Name=name2, Year=2010 Year=2011 Year=2010 Year=2011 ================================================================== N [None, None] [1.0, 1.0] [None, None] [None, None] Y [0.0, 0.0] [None, None] [None, None] [2.0, 2.0] """ df = DataFrame() df.insert({ 'id': 0, 'Name': 'name1', 'Year': 2010, 'member': 'Y', 'rep': 1 }) df.insert({ 'id': 1, 'Name': 'name1', 'Year': 2011, 'member': 'N', 'rep': 1 }) df.insert({ 'id': 2, 'Name': 'name2', 'Year': 2011, 'member': 'Y', 'rep': 1 }) df.insert({ 'id': 0, 'Name': 'name1', 'Year': 2010, 'member': 'Y', 'rep': 2 }) df.insert({ 'id': 1, 'Name': 'name1', 'Year': 2011, 'member': 'N', 'rep': 2 }) df.insert({ 'id': 2, 'Name': 'name2', 'Year': 2011, 'member': 'Y', 'rep': 2 }) my_pivot = df.pivot('id', rows=['member'], cols=['Name', 'Year'], aggregate='tolist', method='full') ## print(my_pivot) self.assertEqual(R, str(my_pivot))