Data Visualization Using Power BI, Orange and Excel

by

Dr. Shirshendu Roy

NOTION PRESS

NOTION PRESS India. Singapore. Malaysia. Published by Notion Press 2021 Copyright © 2021 All Rights Reserved. ISBN-10: 1638324999 ISBN-13: 978-1638324997 This book has been published with all reasonable efforts taken to make the material errorfree after the consent of the author. No part of this book shall be used, reproduced in any manner whatsoever without written permission from the author, except in the case of brief quotations embodied in critical articles and reviews. The Author of this book is solely responsible and liable for its content including but not limited to the views, representations, descriptions, statements, information, opinions and references [“Content”]. The Content of this book shall not constitute or be construed or deemed to reflect the opinion or expression of the Publisher or Editor. Neither the Publisher nor Editor endorse or approve the Content of this book or guarantee the reliability, accuracy or completeness of the Content published herein and do not make any representations or warranties of any kind, express or implied, including but not limited to the implied warranties of merchantability, fitness for a particular purpose. The Publisher and Editor shall not be liable whatsoever for any errors, omissions, whether such errors or omissions result from negligence, accident, or any other cause or claims for loss or damages of any kind, including without limitation, indirect or consequential loss or damage arising out of use, inability to use, or about the reliability, accuracy or sufficiency of the information contained in this book.

This book is dedicated to my teacher Sri Harendra Kumar Biswas

Table of Contents Data Science ....................................................... 1 Introduction ..................................................... 1 Data Science Areas ......................................... 3 Machine Learning ........................................... 9 Business Intelligence and data science ......... 12 Data Science Applications ............................ 14 Data Science Life Cycle................................ 15 Data Scientist ................................................ 20 Statistics Basics............................................... 24 Visualization .................................................... 34 Introduction ................................................... 34 Importance of Visualization .......................... 35 Visualization Elements ................................. 36 Quantitative Message .................................... 37 Key Terms ..................................................... 39 Data Visualization Perspective ..................... 41 Common Visuals ............................................. 49 Introduction ................................................... 49 Gauge Charts ................................................. 49 Area Chart ..................................................... 50 Bar Chart ....................................................... 52 Box-and-whisker Plots .................................. 54 Bubble Cloud ................................................ 56 Bullet Graph .................................................. 57 Cartogram ..................................................... 59 Dependency Wheel ....................................... 60 Dot Distribution Map .................................... 61 Donut Chart ................................................... 63

Dumbbell Chart ............................................. 66 Error bar Chart .............................................. 67 Funnel Chart.................................................. 68 Gantt Chart .................................................... 69 Heat Map ....................................................... 70 Highlight Table ............................................. 72 Histogram ...................................................... 72 Item Chart ..................................................... 73 Matrix Diagram ............................................. 74 Mosaic Chart ................................................. 75 Mekko Chart ................................................. 77 Network Diagram.......................................... 78 OHLC Chart .................................................. 80 Pareto Chart .................................................. 81 Pie Chart........................................................ 84 Polar / Radar Chart........................................ 87 Pyramid Chart ............................................... 89 Quadrant Chart .............................................. 90 Radial Bar Chart ........................................... 90 Radial Tree .................................................... 91 Range Chart .................................................. 92 Scatter Plot .................................................... 94 Seat Map ....................................................... 94 Streamgraph .................................................. 96 Stacked Bar Chart ......................................... 96 Step Line Chart ............................................. 98 Sunburst Chart .............................................. 99 Surface Chart .............................................. 100 Timeline Chart ............................................ 102 Tree Map ..................................................... 102 Venn Diagram ............................................. 104 Waterfall Chart............................................ 106 Wedge Stack Graph .................................... 107

ii

Wind Barbs ................................................. 108 Word Cloud ................................................. 109 VISUALIZATION TOOLS ......................... 110 Introduction ................................................. 110 Data Visualization Tools: ........................... 110 Excel ............................................................ 110 Tableau Desktop ......................................... 111 Microsoft Power BI ..................................... 112 Qlikview ...................................................... 113 Orange ........................................................ 114 FusionCharts............................................... 115 Highcharts .................................................. 116 Xplenty ....................................................... 117 Google Charts.............................................. 118 KNIME ....................................................... 119 Zoho Analytics ............................................ 120 VISUALIZATION EXAMPLES ................. 122 Introduction ................................................. 122 Visuals Using Excel .................................... 122 Visuals Using Power BI .............................. 137 Visuals Using Orange ................................. 142 Reference ....................................................... 145

Acknowledgement Writing a book is always tough task and I thought of putting effort to make this area of the Data Science more interesting and understandable for the students and the practitioners . During my M. Tech days at Indian Statistical Institute (ISI), data visualization was part of our statistics curriculum and was well taught by our professors. Their lucid explanation and applicationoriented teaching helped me in understanding the concept and its application of data visualizations techniques in the resolving the practical problems. Soon after I started using this concept while working in my analytics assignments. I am thankful to Dr. Prasun Kumar Das of Indian Statistical Institute(ISI) Kolkata, for giving me the opportunities to work with him in practical assignments, which developed my knowledge further. I also gathered much practical knowledge from the seminar on Exploratory Data Analysis, organized by Dr. Arup Ranjan Mukhopadhyay of ISI, Kolkata. My special thanks to Mr. Bibhuti Bhusan Das of HCL Technologies, Mr. Satyajit Dutta Roy of Tata Interactive Systems, Mr. Santojit Ghosh of NRI Fintech, for providing me opportunity to lead the analytics projects in my tenure in the respective iv

organizations. I am also thankful to all my students, specially Mr. Sujoy Samaddar and the participants of different workshops for their constant encouragement, feedback and appreciations. Any difficult tasks became easy if you have visionary parents and a motherly sister. They left everything to grownup me. After the untimely sad demise of my father, my mother stood beside me and I truly have no idea where I’d be if she hadn’t supported me or became the father figure what I desperately needed at that age. After my mother left us, my sister Swapna took the charge of the family kept me going in my struggling days and also always motivated me in chasing my dreams. I wish to thank my sister Ratna for making my mathematics foundations strong and Sutapa for all the support she provided when needed; specifically, that Sixty Rupees for my engineering entrance fee which changed my life. Finally, I wish to appreciate the encouragement and support from my wife Shreya and my little angel SIGMA. Writing a book is a tedious task and needs a huge effort. They were kind enough to share their time to pursue my dream. Thanks a lot!

Preface With the continually changing business scenarios, the role of information in business has become very crucial. The availability of a large volume and complexity of data has made the extraction of meaningful inferences from data. With the increased availability of tools and technologies, in most cases, informed decisions are taken and based on the data analysis outcome. Almost all the sectors are using data science for either resolving critical issues or generating revenues. In a nutshell, in the future, it is becoming a crucial area for future professionals. There are different areas of data science. Data visualization is one such area, and Wikipedia defines visualization as “Data visualization is an interdisciplinary field that deals with the graphic representation of data. It is a particularly efficient way of communicating when the data is numerous and have different types”. Data visualization is also simple to understand, even by a layman without prior subject knowledge. I thought to write this book to discuss the different techniques used for data visualization, their uses, tools for generating the visuals, and some examples to create a couple of visuals using Power BI, Orange and Excel. These are useful and essential for preparing reporting dashboard and report vi

writing. This book will provide a basic idea of data visualization and need rigorous reading and practice to become an expert. I am quite sure this book will help university students and the professionals working or intended to work in this field. Dr. Shirshendu Roy 17th February 2021

Chapter 1 DATA SCIENCE INTRODUCTION Data science is the study of information by processing structured or unstructured data to extract meaningful conclusion and inference out of it. The objective is to turn these into valuable input(s) for research work, creating business strategies etc. 

In many times data science is also used for pattern recognition. Outcome of this can help organizations to focus in costs, increase efficiencies, recognize new market opportunities and increase the organization's competitive advantage.



Data science is a multidisciplinary approach. It is a blend of data handling, algorithm development, and technology application to solve complex problems.



Data Science look a problem from many angles. This is primarily used for decision making and prediction purpose. It uses predictive analytics, prescriptive analytics (predictive plus

decision science) algorithms.

and

machine

learning

In a nutshell, it is the best combination of mathematics & statistics, computer science and domain expertise, which uses machine learning, cluster analysis, data mining and visualization for extracting meaningful information out of the available data. The relationship between three major knowledge areas associated with Data Science is represented in Figure No- 1.

Figure No-1: Data Science Trilogy There is strong relationship between Big Data and Data Science. The characteristics of Big Data are;  

2

Volume: The size of the data Velocity: The latency of data processing relative to the growing demand for interactivity



Variety and Complexity: The diversity of sources, formats, quality, structures.

The detail relationship of the above parameters can be represented as interrelation diagram in the Figure No-2.

Figure No-2: Relationship between three parameters

DATA SCIENCE AREAS There are ten major focus areas of Data Science. These areas are; 1. 2. 3. 4. 5.

Data Engineering and Data Warehousing Data Mining and Statistical Analysis Cloud and Distributed Computing Database Management and Architecture Business Intelligence and Strategy