Posts

McHealthy

Image
  Ever wondered what is the most healthy dish on McDonalds India menu. This visualization helps you quickly choose a healthy item based on factors like total sugar, total fat and cholesterol. Read text on visualization to know more. Click on image to enlarge

Centralized Project Tracker

Image
While working on a recent project I have to come up with a way to schedule and maintain tasks and timelines. Very first chart that came to mind was Gantt Chart.  A Gantt chart is a type of bar chart that illustrates a project schedule, named after its popularizer, Henry Gantt, who designed such a chart around the years 1910–1915. Modern Gantt charts also show the dependency relationships between activities and the current schedule status. As we all do regarding any project management activity, I tried to implement it in excel first. But there's no default option to create Gantt chart. With some calculations and lots of conditional formatting I was able to create one but refrained from using it because of two reasons:-  It's not a centralized solution where anyone from team can lookup status of the project. Every time there's some change in schedule you have to update the tracker and then share it with everyone, who might in turn share it with someone else without keeping ev

Who turned the lights off?

Image
  I was browsing through Alteryx Community and found an interesting dataset about power outage caused by animals. Dataset contains around 400 rows recording different news articles around the globe. It also contains which animal caused the outage and in what country. For some records, specific animal name was not specified and instead it says "Animal". This was a challenge  as to get actual count on specific animal I have to extract animal name from news article. I used some text mining feature in Alteryx to achieve this. End result is a graph depicting  power outage by different animals around the world. Note:- I would not make any inference from this data as it was probably not a good sample of all the incidents around the world and was  heavily   biased   on  incidents concerning  to United States . But still a fun analysis. Click on picture to enlarge:- Tool Used:- Tableau & Alteryx

SQL Bars?

Image
While browsing through some SQL puzzles, I came across one where it asked to create a bar chart inside SQL server. Initial thought - It will require some sort of text manipulation. But before that I need to structure the data in proper format. Input Table:- The problem is divided into 3 parts:- Get regions and their total quantities. Because total quantities would be huge, it will not be possible to plot them as bars. Therefore, convert those quantities into percentage of total value. Create bars for percentage of total value against each region. SQL script with cte as ( select Region, sum(Order_Quantity) as Quantity, sum(SUM(Order_Quantity)) over () as [Total Quantity], round( ( CAST( sum(Order_Quantity) as float ) / cast (sum(sum(Order_Quantity)) over () as float ) ) * 100 ,0) as [% Total] from superstore group by Region ) select Region, [% Total], REPLICATE('|',[% Total]) as [Bars] from cte Output Table:- It was a fun little exercise to think outside the box and how you ca

Movie-Lookup

Image
Ever wonder which movie should I watch this weekend?  You login to your favorite streaming service just to find yourself struck in vicious loop of  checking it's rating on IMDB and then searching for some more before deciding on that one movie which you might find interesting. I thought wouldn't it be great to have a dashboard with movies rated and you can apply some criteria to find what movie you are looking for. I searched for IMDB data and found it on kaggle website. This data is used to create and train recommendation systems, but for me a simple dashboard was enough to find movies I might enjoy on my weekend.  

Choked

Image
 India has 9 of the 10 most polluted cities in the world.  As per a study based on 2016 data, at least 140 million people in India breathe air that is 10 times or more over the WHO safe limit. Air pollution contributes to the premature deaths of 2 million Indians every year.  Emissions come from vehicles and industry, whereas in rural areas, much of the pollution stems from biomass burning for cooking and keeping warm. In autumn and spring months, large scale crop residue burning in agriculture fields – a cheaper alternative to mechanical tilling – is a major source of smoke, smog and particulate pollution. Click on graph below which shows monthly average Air Quality Index (AQI) across various Indian cities from 2015 - 2020. It also shows month with highest average AQI for that city. Tool Used:- Power BI Data:- kaggle.com

Growth of Indian Stock Market

Image
  Thinking about investing in share market? The graph shows annual growth of NIFTY 50 since 2000. The NIFTY 50 is a benchmark Indian stock market index that represents the weighted average of 50 of the largest Indian companies listed on the National Stock Exchange. Tool Used:- Tableau Data:- https://en.wikipedia.org/wiki/NIFTY_50