IT Blog

  • Blog
  • Technology
    • Technology
    • Architecture
    • CMS
    • CRM
    • Web
    • DotNET
    • Python
    • Database
    • BI
    • Program Language
  • Users
    • Login
    • Register
    • Forgot Password?
  • ENEN
    • 中文中文
    • ENEN
BI
BI

Python Data Visualization Practise

Python Code import json import requests # 3rd party import jsonpath from pyecharts.charts import Map from pyecharts import options as opts from src.covid19_data import nameMap url = 'https://api.inews.qq.com/newsqa/v1/automation/foreign/country/ranklist' resp = requests.post(url).text data = json.loads(resp) # get contry names and confirmed cases $:outer {}, ..: fuzzy match + key name = jsonpath.jsonpath(data, "$..name") # ['美国', '巴西', '印度', '俄罗斯', ... ] confirm = jsonpath.jsonpath(data, "$..confirm") pairs = list(zip(name, confirm)) # [('美国', 5321520), ('巴西', 3109630), ('印度', 2329638), ...] # 2. data visualization map plotting - size,title,color,data map_ = Map(opts.InitOpts(width='1200px', height='600px')) .add(series_name="Global Covid-19 Confirmed Cases", data_pair=pairs, # data entry - country : confirmed maptype="world", # world map name_map=nameMap, # country name mapping is_map_symbol_show=False) # 不显示标记点 # set series options map_.set_series_opts(label_opts=opts.LabelOpts(is_show=False)) map_.set_global_opts(title_opts=opts.TitleOpts(title="Global Covid-19 Pandemic (Chinese)"), visualmap_opts=opts.VisualMapOpts(max_=6000000, is_piecewise=True)) map_.render('../result/covid19.html') covid19_data nameMap = { 'Singapore Rep.': '新加坡', 'Dominican Rep.': '多米尼加', 'Palestine': '巴勒斯坦', 'Bahamas': '巴哈马', 'Timor-Leste': '东帝汶', 'Afghanistan': '阿富汗', 'Guinea-Bissau': '几内亚比绍', "Côte d'Ivoire": '科特迪瓦', 'Siachen Glacier': '锡亚琴冰川', "Br. Indian Ocean Ter.": '英属印度洋领土', 'Angola': '安哥拉', 'Albania': '阿尔巴尼亚', 'United Arab Emirates': '阿联酋', 'Argentina': '阿根廷', 'Armenia': '亚美尼亚', 'French Southern and Antarctic Lands': '法属南半球和南极领地', 'Australia': '澳大利亚', 'Austria': '奥地利', 'Azerbaijan': '阿塞拜疆', 'Burundi': '布隆迪', 'Belgium': '比利时', 'Benin': '贝宁', 'Burkina Faso': '布基纳法索', 'Bangladesh': '孟加拉国', 'Bulgaria': '保加利亚', 'The Bahamas': '巴哈马', 'Bosnia and Herz.': '波斯尼亚和黑塞哥维那', 'Belarus': '白俄罗斯', 'Belize': '伯利兹', 'Bermuda': '百慕大', 'Bolivia': '玻利维亚', 'Brazil': '巴西', 'Brunei': '文莱', 'Bhutan': '不丹', 'Botswana': '博茨瓦纳', 'Central African Rep.': '中非共和国', 'Canada': '加拿大', 'Switzerland': '瑞士', 'Chile': '智利', 'China': '中国', 'Ivory Coast': '象牙海岸', 'Cameroon': '喀麦隆', 'Dem. Rep. Congo': '刚果(金)', 'Congo': '刚果(布)', 'Colombia': '哥伦比亚', 'Costa Rica': '哥斯达黎加', 'Cuba': '古巴', 'N. Cyprus': '北塞浦路斯', 'Cyprus': '塞浦路斯', 'Czech Rep.': '捷克', 'Germany': '德国', 'Djibouti': '吉布提', 'Denmark': '丹麦', 'Algeria': '阿尔及利亚',…

2020-08-12 0 Comments 994 Views 0 Like IT Team Read more
BI

Feature points sampling method validating reports

Sampling method is the most efficient and popular data validation method, even it has little coverage with lots of risk. Here I would introduce an feature points sampling method. The reason to add "feature points" is to avoid generic random points that may not cover as much as possible for the report validation. Feature points sampling method takes some special points and generic points to validate a reporting result, and ensure these points have most coverage of the reporting range. Feature points contains less data, and could be validated by human calculation easily. The results of these points can validate the query logic correctness. Once the query logic being validated we could add more complex data combination as generic points and finally compare the query results and report results. Let's take a real automation report as an example. The data come from the log of BluePrism RPA. The existing ETL process has been tested already. I am going to focus on the report result against local data to verify the report result correctness. The report includes daily, weekly, monthly, and yearly. In this article I'm gonna use monthly report for validation. Here're the points used in the verification. Point StartMth EndMth LOB Process Point Note 1 2018-10 2018-10 BM hotspots feature specific lob and process_name at certain time point 2 2018-10 2018-10 BM Shipped not Activate feature different specific lob and process_name at certain time point 3 2018-10 2018-10 BM [ALL] generic same lob and time point, but all process_names 4 2018-11 2018-11 BRS Disconnection feature different specific lob and process_name…

2019-02-15 0 Comments 1619 Views 1 Like IT Team Read more
BI

Send web report by email from report page

Taking report from web page function sendReport() { var dataInput = {}; reportBody = $(".dataTables_scroll"); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Main/BusinessSuite.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Libs/BootStrap/css/bootstrap.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Libs/JQueryConfirm/css/jquery-confirm.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Libs/JQueryDatatables/css/jquery.dataTables.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Css/Table/tableenhacements.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Libs/JQueryUI/jquery-ui.css" type="text/css" />'); dataInput.ReportName = $("#ddlReports option:selected").attr("data-report-name"); dataInput.ReportPage = reportBody.html().replace(/height: 0px;/g, 'height: 30px;padding:5px 0 !important;') .replace(/dataTables_scrollHeadInner"/g, 'dataTables_scrollHeadInner" style="display:none"') .replace(/<table/g, '<table style="font-size:12px !important;"') .replace(/<th/g, '<th style="align:left;"') .replace(/<div class="dataTables_scrollHeadInner".*?(<\/div>)/gm,''); var ajaxParameters = { methodUrl: "Mail/SendReport", dataInput: dataInput }; new BaseClass().AjaxCall( ajaxParameters, function () { }, function (data) { } ); return false; Stored Proc: ALTER proc [dbo].[usp_BS_SendMail]( @PRM_LAN_ID VARCHAR(100)='', @REPORT_NAME nvarchar(150) =null, @REPORT_PAGE nvarchar(max) =null ) as if coalesce(@REPORT_NAME,'')='' or coalesce(@REPORT_PAGE,'')='' return; declare @typeid int,@subject nvarchar(255); select @typeid=ReportTypeID from [dbo].[tbl_BS_Reports_Configuration] where ReportName = @REPORT_NAME insert dbo.tbl_ReportPage (ReportTypeID, ReportName,ReportPage,CreatedBy) values (@typeid,@REPORT_NAME,@REPORT_PAGE,@PRM_LAN_ID) set @subject = 'Report: ' + @REPORT_NAME; EXEC msdb.dbo.sp_send_dbmail @profile_name = null, --@recipients = '"Vat, Moh" <abc.vat@ball.ca>;"Pill, Balak" <def.pill@ball.ca>', @recipients = 'xxxxx@gmail.com', @body = @REPORT_PAGE, @body_format = 'HTML', @subject = @subject; Calling sp using (var fnReturn = new DataTable()) { fnReturn.RunStoredProcedure(filters.Conn, "[dbo].[usp_BS_SendMail]", new Dictionary<string, object>() { { "@PRM_LAN_ID", filters.Lan_ID?? "" }, { "@REPORT_NAME", filters.ReportName}, { "@REPORT_PAGE", filters.ReportPage} }); return fnReturn; } ========================== report contents: <link href="http://localhost:1282/Scripts/Libs/JQueryUI/jquery-ui.css" rel="stylesheet" type="text/css"> <link href="http://localhost:1282/Css/Table/tableenhacements.css" rel="stylesheet" type="text/css"> <link href="http://localhost:1282/Scripts/Libs/JQueryDatatables/css/jquery.dataTables.css" rel="stylesheet" type="text/css"> <link href="http://localhost:1282/Scripts/Libs/JQueryConfirm/css/jquery-confirm.css" rel="stylesheet" type="text/css"> <link href="http://localhost:1282/Scripts/Libs/BootStrap/css/bootstrap.css"…

2018-06-26 2 Comments 1351 Views 0 Like IT Team Read more
BI

Reporting notes

Problem: business requires an report in excel format to be automated in web application. Final web format: Original data format: Key points: Data must be summarized by date, week, month, year, and job name dimensions. Fact table contains dozen millions of records for several years; The reporting requires to rotate the data by 90 degrees to display summarized data Multiple data presentations in a single column, witch includes integer number, decimal, and percentage. Solution: Get data from data source OpenQuery for linked server does not accept variables, therefore we have to use dynamic query statement. set @openquery = ' SELECT [Date], [Day], JobName, Volume_to_Work, Volume_Worked, Volume_RPC FROM OPENQUERY([S1],'' SELECT cast(a.Date as Date) [Date], datename(dw,date) as [Day], a.JobName, a.Volume_to_Work, a.Volume_Worked, isnull(b.Volume_RPC,0) as Volume_RPC FROM ( –VOLUME select BACKUPDATE date,JobName,sum(VOLUME_SENT) as Volume_to_Work,sum(VOLUME_WORKED) as Volume_Worked from TBL_METRICS_RAPPORTVOLUME where JobName in ('''''+ replace( case @JOB_NAME_LIST when 'All|' then 'Ucm_Arics_ON_Preview|Ucm_Caprice_QC_Preview|UCM_OB_E_PREVIEW|UCM_OB_F_PREVIEW' else @JOB_NAME_LIST end ,'|',''''',''''') +''''' ) and year(BACKUPDATE) = '+cast(@REPORT_YEAR as varchar)+' group by JobName,BACKUPDATE ) a left join ( –RPC select Backupdate,session,count(session) as Volume_RPC from TBL_ACCOUNTS_WORKED a left join ( select RPC,Result from TBL_REFERENCE_DIALERCODES where site = ''''Internal'''' ) b on a.RELEASECODE= b.RESULT where session in ('''''+ replace( case @JOB_NAME_LIST when 'All|' then 'Ucm_Arics_ON_Preview|Ucm_Caprice_QC_Preview|UCM_OB_E_PREVIEW|UCM_OB_F_PREVIEW' else @JOB_NAME_LIST end ,'|',''''',''''') +''''' ) and year(Backupdate) = '+cast(@REPORT_YEAR as varchar)+' and b.RPC = 1 group by Backupdate,session ) b on a.Date = b.Backupdate and a.JobName = b.session where datename(dw,date) <> ''''Sunday'''''' )' create table #campaign ([Date] date,[Day] varchar(20),JobName varchar(255),Volume_to_work int,Volume_worked int,Volume_RPC int) insert #campaign exec(@openquery) generated openquery look like: SELECT [Date], [Day], JobName, Volume_to_Work, Volume_Worked, Volume_RPC FROM OPENQUERY([BREPP1],' SELECT cast(a.Date as Date)…

2017-07-17 0 Comments 950 Views 0 Like IT Team Read more
BI

Power BI First Taste

Power BI is the latest Microsoft BI tool. It was originated from Excel plug-ins, e.g. Power Query, Power Pivot, Power View, and rolled out last year (2015.7). Therefore, it is very similar to working on Excel. It is visualized self-service business intelligence tool for everyone.    Power BI provides 4 components. Power BI services     – you can create a real time online dashboard that can be accessed by anyone from everywhere in the world or by anyone in your organization. Power BI desktop      – run power BI on your desktop Power BI Mobile        – run power BI app on your mobile device Power BI Embedded – allow you to embed the interactive visuals into your app using REST APIs and Power BI SDK. Power BI accept lots of data sources, such as database, files, web page, cloud service…   Power BI allows you to quickly create interactive, responsive, pretty dashboard easily. Here is the sample dashboard.       Power BI provides lots of visualization styles that you can choose.     Power BI is a powerful and the simplest BI tool in current market. Everyone, especially Excel users can use it easily and build powerful, attractive dashboard. Can't wait to try ...  2,924 total views,  5 views today

2016-12-07 0 Comments 1132 Views 0 Like IT Team Read more
Chinese (Simplified) Chinese (Simplified) Chinese (Traditional) Chinese (Traditional) English English French French German German Japanese Japanese Korean Korean Russian Russian
Newest Hotspots Random
Newest Hotspots Random
Rich editor not working Making web page scroll down automatically Getting data from Dapper result All Unicode Chars How to keep and display contact form 7 data Common Regular Expressions
Add activity log for WordPress Controlling posts order in plugin WP Tab Widget Undo product custom field value in Ultimate Product Catalog Social media WordPress Data Model Emojis and icons for HTML
Categories
  • Architecture
  • BI
  • C#
  • CSS
  • Database
  • DotNET
  • Hosting
  • HTML
  • JavaScript
  • PHP
  • Program Language
  • Python
  • Security
  • SEO
  • Technology
  • Web
  • Wordpress

COPYRIGHT © 2021 Hostlike IT Blog. All rights reserved.

This site is supported by Hostlike.com