def testToJSon(self): # The json of the initial data we load to the table. init_data_json = ("{cols:" "[{id:'a',label:'A',type:'number'}," "{id:'b',label:'b',type:'string'}," "{id:'c',label:'c',type:'boolean'}]," "rows:[" "{c:[{v:1},,{v:null}]}," "{c:[,{v:'z'},{v:true}]}" "]}") table = DataTable([("a", "number", "A"), "b", ("c", "boolean")], [[1], [None, "z", True]]) self.assertEqual(2, table.NumberOfRows()) self.assertEqual(init_data_json, table.ToJSon()) table.AppendData([[-1, "w", False]]) self.assertEqual(3, table.NumberOfRows()) self.assertEqual( init_data_json[:-2] + ",{c:[{v:-1},{v:'w'},{v:false}]}]}", table.ToJSon()) cols_json = ("{cols:" "[{id:'t',label:'T',type:'timeofday'}," "{id:'d',label:'d',type:'date'}," "{id:'dt',label:'dt',type:'datetime'}],") table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({date(1, 2, 3): [time(1, 2, 3)]}) self.assertEqual(1, table.NumberOfRows()) self.assertEqual( cols_json + "rows:[{c:[{v:[1,2,3]},{v:new Date(1,1,3)},{v:null}]}]}", table.ToJSon(columns_order=["t", "d", "dt"])) table.LoadData({ date(2, 3, 4): [(time(2, 3, 4), "time 2 3 4"), datetime(1, 2, 3, 4, 5, 6)], date(3, 4, 5): [] }) self.assertEqual(2, table.NumberOfRows()) self.assertEqual((cols_json + "rows:[" "{c:[{v:[2,3,4],f:'time 2 3 4'},{v:new Date(2,2,4)}," "{v:new Date(1,1,3,4,5,6)}]}," "{c:[,{v:new Date(3,3,5)},{v:null}]}]}"), table.ToJSon(columns_order=["t", "d", "dt"])) json = ("{cols:[{id:\"a'\",label:\"a'\",type:'string'}," "{id:'b',label:\"bb'\",type:'number'}]," "rows:[{c:[{v:'a1'},{v:1}]},{c:[{v:'a2'},{v:2}]}," "{c:[{v:'a3'},{v:3}]}]}") table = DataTable({"a'": ("b", "number", "bb'", {})}, { "a1": 1, "a2": 2, "a3": 3 }) self.assertEqual(3, table.NumberOfRows()) self.assertEqual(json, table.ToJSon())
def sql_to_data_table(table_name, data_types=None) -> DataTable: """Convert a SQL table to a google table. Parameters ---------- table_name : str The name of the table. data_types : str The DataTable type. """ if data_types is None: data_types = {} table_data = pd.read_sql_query(f'select * from {table_name};', database.connection) columns = {} date_columns = [] for column in table_data.columns: data_type = data_types.get(column) if data_type == 'date': date_columns.append(column) elif data_type is None: if table_data.dtypes[column] == np.int64: data_type = 'number' else: data_type = 'string' columns[column] = (data_type, column) table = DataTable(columns) for column in date_columns: table_data[column] = pd.to_datetime(table_data[column]).fillna(date.today()) table.LoadData(table_data.to_dict('index').values()) return table
def testToCsv(self): init_data_csv = "\r\n".join( ["A,\"b\"\"\",c", "1,,", ",zz'top,true", ""]) table = DataTable([("a", "number", "A"), "b\"", ("c", "boolean")], [[(1, "$1")], [None, "zz'top", True]]) self.assertEqual(init_data_csv, table.ToCsv()) table.AppendData([[-1, "w", False]]) init_data_csv = "%s%s\r\n" % (init_data_csv, "-1,w,false") self.assertEqual(init_data_csv, table.ToCsv()) init_data_csv = "\r\n".join([ "T,d,dt", "01:02:03,1901-02-03,", "\"time \"\"2 3 4\"\"\",1902-03-04,1901-02-03 04:05:06", ",1903-04-05,", "" ]) table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({ date(1901, 2, 3): [time(1, 2, 3)], date(1902, 3, 4): [(time(2, 3, 4), 'time "2 3 4"'), datetime(1901, 2, 3, 4, 5, 6)], date(1903, 4, 5): [] }) self.assertEqual(init_data_csv, table.ToCsv(columns_order=["t", "d", "dt"]))
def testToCsv(self): init_data_csv = "\n".join( ['"A", "b", "c"', '1, "", ""', '"", "zz\'top", true']) table = DataTable([("a", "number", "A"), "b", ("c", "boolean")], [[(1, "$1")], [None, "zz'top", True]]) self.assertEqual(init_data_csv, table.ToCsv()) table.AppendData([[-1, "w", False]]) init_data_csv = "%s\n%s" % (init_data_csv, '-1, "w", false') self.assertEquals(init_data_csv, table.ToCsv()) init_data_csv = "\n".join([ '"T", "d", "dt"', '"[1,2,3]", "new Date(1,1,3)", ""', '"time ""2 3 4""", "new Date(2,2,4)", "new Date(1,1,3,4,5,6)"', '"", "new Date(3,3,5)", ""' ]) table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({ date(1, 2, 3): [time(1, 2, 3)], date(2, 3, 4): [(time(2, 3, 4), 'time "2 3 4"'), datetime(1, 2, 3, 4, 5, 6)], date(3, 4, 5): [] }) self.assertEqual(init_data_csv, table.ToCsv(columns_order=["t", "d", "dt"]))
def testToTsvExcel(self): table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({ date(1901, 2, 3): [time(1, 2, 3)], date(1902, 3, 4): [(time(2, 3, 4), 'time "2 3 4"'), datetime(1901, 2, 3, 4, 5, 6)], date(1903, 4, 5): [] }) self.assertEqual(table.ToCsv().replace(",", "\t").encode("UTF-16LE"), table.ToTsvExcel())
def get(self, request, *args, **kwargs): if not request.is_ajax(): raise SuspiciousOperation( 'This endpoint should only be called from JavaScript.') search_terms = self.get_search_terms(request.session) qs = get_queryset_filtered_by_search_terms(self.get_pixels_queryset(), search_terms=search_terms) dt = DataTable(self.get_headers()) dt.LoadData(qs.values(*self.get_columns())) return HttpResponse(dt.ToJSon(), content_type='application/json')
def testToTsvExcel(self): table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({ date(1901, 2, 3): [time(1, 2, 3)], date(1902, 3, 4): [(time(2, 3, 4), 'time "2 3 4"'), datetime(1901, 2, 3, 4, 5, 6)], date(1903, 4, 5): [] }) csv_string = table.ToCsv() if not isinstance(csv_string, six.text_type): csv_string = csv_string.decode("utf-8") self.assertEqual( csv_string.replace(",", "\t").encode("UTF-16LE"), table.ToTsvExcel())
def testToHtml(self): html_table_header = "<html><body><table border=\"1\">" html_table_footer = "</table></body></html>" init_data_html = html_table_header + ( "<thead><tr>" "<th>A<</th><th>b></th><th>c</th>" "</tr></thead>" "<tbody>" "<tr><td>$1</td><td></td><td></td></tr>" "<tr><td></td><td><z></td><td>true</td></tr>" "</tbody>") + html_table_footer table = DataTable([("a", "number", "A<"), "b>", ("c", "boolean")], [[(1, "$1")], [None, "<z>", True]]) self.assertEqual(init_data_html.replace("\n", ""), table.ToHtml()) init_data_html = html_table_header + ( "<thead><tr>" "<th>T</th><th>d</th><th>dt</th>" "</tr></thead>" "<tbody>" "<tr><td>01:02:03</td><td>0001-02-03</td><td></td></tr>" "<tr><td>time 2 3 4</td><td>0002-03-04</td>" "<td>0001-02-03 04:05:06</td></tr>" "<tr><td></td><td>0003-04-05</td><td></td></tr>" "</tbody>") + html_table_footer table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({ date(1, 2, 3): [time(1, 2, 3)], date(2, 3, 4): [(time(2, 3, 4), "time 2 3 4"), datetime(1, 2, 3, 4, 5, 6)], date(3, 4, 5): [] }) self.assertEqual(init_data_html.replace("\n", ""), table.ToHtml(columns_order=["t", "d", "dt"]))
def testToHtml(self): html_table_header = "<html><body><table border='1'>" html_table_footer = "</table></body></html>" init_data_html = html_table_header + ( "<thead><tr>" "<th>A</th><th>b</th><th>c</th>" "</tr></thead>" "<tbody>" "<tr><td>'$1'</td><td></td><td></td></tr>" "<tr><td></td><td>'<z>'</td><td>true</td></tr>" "</tbody>") + html_table_footer table = DataTable([("a", "number", "A"), "b", ("c", "boolean")], [[(1, "$1")], [None, "<z>", True]]) self.assertEqual(init_data_html.replace("\n", ""), table.ToHtml()) init_data_html = html_table_header + ( "<thead><tr>" "<th>T</th><th>d</th><th>dt</th>" "</tr></thead>" "<tbody>" "<tr><td>[1,2,3]</td><td>new Date(1,1,3)</td><td></td></tr>" "<tr><td>'time 2 3 4'</td><td>new Date(2,2,4)</td>" "<td>new Date(1,1,3,4,5,6)</td></tr>" "<tr><td></td><td>new Date(3,3,5)</td><td></td></tr>" "</tbody>") + html_table_footer table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({ date(1, 2, 3): [time(1, 2, 3)], date(2, 3, 4): [(time(2, 3, 4), "time 2 3 4"), datetime(1, 2, 3, 4, 5, 6)], date(3, 4, 5): [] }) self.assertEqual(init_data_html.replace("\n", ""), table.ToHtml(columns_order=["t", "d", "dt"]))
# Replace null values with -1 to indicate not specified. for column in ['Husband', 'Wife', 'Father', 'Mother']: data[column][data[column].copy().isnull()] = -1 columns = {} for column in data.columns: # if 'date' in column: # data_type = 'date' if data.dtypes[column] in ['O']: data_type = 'string' else: data_type = 'number' columns[column] = (data_type, column) table = DataTable(columns) table.LoadData(data.to_dict('index').values()) timelines = sql_to_data_table('stays', {'Start': 'date', 'End': 'date'}) locations = sql_to_data_table('locations') with open('table.js', 'w') as output_file: code = f"""function loadFamilyData() {{ {table.ToJSCode('familyTable')} return familyTable; }} function loadTimelines() {{
def testToJSon(self): json_obj = { "cols": [{ "id": "a", "label": "A", "type": "number" }, { "id": "b", "label": "b", "type": "string" }, { "id": "c", "label": "c", "type": "boolean" }], "rows": [{ "c": [{ "v": 1 }, None, None] }, { "c": [None, { "v": "z" }, { "v": True }] }, { "c": [None, { "v": "\\u05d0" }, None] }, { "c": [None, { "v": "\\u05d1" }, None] }] } table = DataTable([("a", "number", "A"), "b", ("c", "boolean")], [[1], [None, "z", True], [None, "\\u05d0"], [None, "\\u05d1".encode("utf-8")]]) self.assertEqual(4, table.NumberOfRows()) self.assertEqual( json.dumps(json_obj, separators=(",", ":"), ensure_ascii=False).encode("utf-8"), table.ToJSon()) table.AppendData([[-1, "w", False]]) self.assertEqual(5, table.NumberOfRows()) json_obj["rows"].append({"c": [{"v": -1}, {"v": "w"}, {"v": False}]}) self.assertEqual( json.dumps(json_obj, separators=(",", ":"), ensure_ascii=False).encode("utf-8"), table.ToJSon()) json_obj = { "cols": [{ "id": "t", "label": "T", "type": "timeofday" }, { "id": "d", "label": "d", "type": "date" }, { "id": "dt", "label": "dt", "type": "datetime" }], "rows": [{ "c": [{ "v": [1, 2, 3] }, { "v": "Date(1,1,3)" }, None] }] } table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({date(1, 2, 3): [time(1, 2, 3)]}) self.assertEqual(1, table.NumberOfRows()) self.assertEqual(json.dumps(json_obj, separators=(",", ":")), table.ToJSon(columns_order=["t", "d", "dt"])) json_obj["rows"] = [{ "c": [{ "v": [2, 3, 4], "f": "time 2 3 4" }, { "v": "Date(2,2,4)" }, { "v": "Date(1,1,3,4,5,6,555)" }] }, { "c": [None, { "v": "Date(3,3,5)" }, None] }] table.LoadData({ date(2, 3, 4): [(time(2, 3, 4), "time 2 3 4"), datetime(1, 2, 3, 4, 5, 6, 555000)], date(3, 4, 5): [] }) self.assertEqual(2, table.NumberOfRows()) self.assertEqual(json.dumps(json_obj, separators=(",", ":")), table.ToJSon(columns_order=["t", "d", "dt"])) json_obj = { "cols": [{ "id": "a\"", "label": "a\"", "type": "string" }, { "id": "b", "label": "bb\"", "type": "number" }], "rows": [{ "c": [{ "v": "a1" }, { "v": 1 }] }, { "c": [{ "v": "a2" }, { "v": 2 }] }, { "c": [{ "v": "a3" }, { "v": 3 }] }] } table = DataTable({"a\"": ("b", "number", "bb\"", {})}, { "a1": 1, "a2": 2, "a3": 3 }) self.assertEqual(3, table.NumberOfRows()) self.assertEqual(json.dumps(json_obj, separators=(",", ":")), table.ToJSon())
def get(self): question = self.request.get("question") if question != '': form = {"q": question} form_data = urllib.urlencode(form) resp = urlfetch.fetch(host + "ask", payload=form_data, method="POST", follow_redirects=False) if resp.status_code == 200: logging.debug("OK") logging.debug(result.content) else: logging.debug("FAILED ON QUESTION") pass country = 'US' indicator = 'TM.TAX.MRCH.SM.AR.ZS' pass else: country = self.request.get("country") indicator = self.request.get("indicator") pass start = self.request.get("start") if start == '': start = '2000' end = self.request.get("end") if end == '': end = '2012' if indicator == '': indicator = 'TM.TAX.MRCH.SM.AR.ZS' pass data = {} country_code = country.lower() indicator = indicator.upper() url = "http://api.worldbank.org/countries/" + country_code + "/indicators/" + indicator + "?" + \ "date=" + start + ":" + end + "&" + "format=" + "json" resp = urlfetch.fetch(url, method="GET", follow_redirects=True) if resp.status_code == 200: logging.debug(resp.status_code) try: data = json.loads(resp.content) except: logging.info(resp.content) pass else: logging.debug(resp.status_code) logging.debug(resp.content) pass rows = {} old_viz_data = [] viz_data = [] countries = {} try: for row in data[1]: key = row['country']['id'] countries[key] = row['country']['value'] try: rows[row['date']][key] = row['value'] except: rows[row['date']] = {} rows[row['date']][key] = row['value'] pass for yr in rows.keys(): viz_row = {"date": date(int(yr), 1, 1)} for k in rows[yr].keys(): try: viz_row[k] = float(rows[yr][k]) except: viz_row[k] = None pass pass viz_data.append(viz_row) except: pass chart_data = {} chart_data['cols'] = [{ 'id': 'date', 'label': 'Date', 'type': 'number' }, { 'id': 'value', 'label': 'Value', 'type': 'number' }] chart_data['rows'] = rows pass indicator_value = data[1][0]['indicator']['value'] viz_desc = {"date": ("date", "Date")} order = ["date"] for k in countries.keys(): viz_desc[k] = ("number", countries[k]) order.append(k) data_table = DataTable(viz_desc) data_table.LoadData(viz_data) template_values = { 'question': SafeString(question), 'start': start, 'end': end, 'country': country, 'indicator': indicator, 'data': data_table.ToJSon(columns_order=order, order_by="date"), 'title': SafeString(indicator_value) } if self.request.path == '/': path = os.path.join(os.path.dirname(__file__), 'templates/index.html') else: path = os.path.join(os.path.dirname(__file__), 'templates' + self.request.path) pass self.response.out.write(template.render(path, template_values)) return