Web Scraping Using Beautiful Soup (Python lib) and Creating a job on Ms SQL to automate updation

 Step 1 : Web scraping Wikipedia using Pythons library Beautiful Soup to pull out info of Countries in Asia & Pacific.

                

Step 2 : Tidying up the output data a bit.

                    

                                    

Step 3 : Importing the data into MS SQL and creating a Sub Procedure to update.

                                

Step 4 : Connecting Tableau to MS SQL for final Visualization

                                   

Excel Automation using Python

 We can use Python to automate most of the things that we do on Excel just like creating a macro in VBA, it is a good alternative to VBA.

I had an unclean and unstructured excel file which was copy pasted from a website. The first option I had was to try to clean it on Tableau Prep but what I later realized is that Tableau Prep can be good on data which is structured and easy to clean. But for a completely scrambled data the best options are either VBA or Python.

1. Unstructured Data

    

2. Python Code

    

    

3. Final Output

        

                    

Drop in Apparel Exports to the US during pandemic

 A Research paper published on "Workers Rights" website showed how the Covid-19 impacted garment suppliers/manufacturers with big Retailers retroactively canceling the orders that suppliers had already produced or were in process of producing.

(link to paper : https://www.workersrights.org/wp-content/uploads/2020/10/Unpaid-Billions_October-6-2020.pdf)

A comparative study of exports in 2019 and 2020 showed a total value difference of USD 9.7 billion from April through June 2020 relative to 2019.

Below are the visualizations, where I have done a monthly comparison with March as a base month for both years individually, a Y-o-Y comparison at an overall level and at country level and a final visualization on difference in exports for each country.

1. Comparing March exports with other months for each year.

                

   

from the above chart we can see that the usual trend in 2019 was an increase in month of May & June with a small drop in April. In 2020 the drop is significant in all months, with a 60% drop in May'20.

        2. Year over Year comparison;

                    

            Drop across all months with a significant low impact in March'20.

        3. Year over Year comparison exporting Country wise;

            

        4. A polygon chart showing cumulative reduction in exports in 2020 from 2019;

    

          

  Comparing difference in exports during 2019 and 2020 shows a significant drop in exports for countries irrespective of whether the country was under strict or partial lockdown leading to a conclusion that the drop was majorly due to order cancellations by retail brands.(Source : Workers Rights)

Credits : Andy Kriebel (link to video : https://www.youtube.com/watch?v=KRhQCHS32e8&t=4854s)

Dataset : https://data.world/makeovermonday/2020w43-apparel-exports-to-us

                                                                                                                                                                      

Gaming Console War : Nintendo or Sony ?

The Gaming consoles are currently in their 9th ninth generation with the early launch of Nintendo Switch in 2017 and the launch of PlayStation 5 and Xbox Series X/S in November 2020 by Sony & Microsoft respectively.

Nintendo Switch is a hybrid console that can be used either as a home console or a portable device. The concept of Switch came about as Nintendo's reaction to several quarters of financial losses into 2014,attributed to poor sales of its previous console, the Wii U and market competition from mobile gaming(Source : Wikipedia).

The launch of Nintendo Switch is considered as Blue Ocean Strategy similar to its launch of Wii in 2006 which instead of directly competing with Sony and Microsoft's traditional consoles introduced an innovative gameplay incorporating the use of motion controls.

In this analysis I have compared the handheld console and home gaming consoles units sold world over of both Sony and Nintendo(no data of Microsoft Xbox was available) and see the impact of mobile gaming as a threat to the handheld and home consoles.

In the handheld gaming console segment Nintendo has always dominated with its DS and 3DS models but with stiff competition from mobile games since 2008 they are losing their ground where they had a strong foothold.

Although Sony has discontinued its PSP Vita in 2019,Nintendo is still trying to be relevant in the handheld/portable segment with Switch(hybrid, launched 2017 and Switch Lite(launched 2019).

From below area chart one can see a slump post 2008 in the units sold for both Sony & Nintendo.

In the below chart I have plotted timelines with the launch of major home game consoles and one can clearly see a sharp spike in sales on Nintendo Wii (2006) which offered a complete different experience in terms of gaming but could not sustain for long; partly because of the financial crisis of 2009 and partly because of the "casual gamers" phenomena. As per reports it was stated that Nintendo Wii did not appeal to the hardcore gaming community and rather it was something for "casual gamers" to hook on to with its motion sensors.

We see the same spike post 2017 with Nintendo Switch, although it is to be seen how long can it sustain its run with new launches by both Sony & Microsoft in 2020.

Some trivia:

PS2 remains the all time most selling console with more than 155 mil; followed by PS4 at 112 mil.

English Premier League Champions from 2000 - 2019

 Tried to create a race chart and topped it with Tableau's Animation functionality.

Clubs based on their points in each season has been ranked from 1 to 20, so to call it a race chart is unfair as the points are restricted to each individual season and not a cumulative total.

P.S. : Credits to Abhishek Agarrwal (https://www.youtube.com/channel/UCxNzLV0gP8nuOZcSfyc0hsg)

Liverpool's Race to Title

In October 2015, Liverpool appointed Jurgen Klopp as their manager replacing Brendan Rodgers after his dismal stint. Brendan Rodgers managed Liverpool for three seasons and did not win a trophy which was a first for any manager in Liverpool.

As per Wikipedia, Liverpool's co-owner John Henry used mathematical model developed by Cambridge physicist Ian Graham to select the manager and players that would eventually win the UEFA Champions League.

That was how Jurgen Klopp was selected as a successor to Brendan Rodgers. And Jurgen Klopp did win the UEFA Champions League in 2018/19 season with the Liverpool team that he built.

For this analysis the dataset I used was not readily available, so I had to manually copy, paste and clean the Premier League standings table from https://www.premierleague.com/tables.

First up is a line chart tracking position of Liverpool from Season 2015/16 to 2019/20 since Jurgen Klopp took over.

From the above chart one can see the work and effort put by Jurgen Klopp in building the team.

Red line is for Liverpool, in his first season in-charge although he was appointed in October 2015 and not exactly at the start of the new season and with him inheriting the team and players built by his predecessor, the team finished 8th in that season and finally finishing on top of the table in the season 2019/20.

Next is a Slope chart comparing season 2018/19 and 2019/20, Liverpool were 2nd in 2018/19 and 1st in 2019/20 season.

Gender Gap in Internet accessibility

 This project is part of MakeoverMonday which is a weekly social data project (link here).

Data is about how each country stands in terms of internet accessibility both (Internet in general and Mobile ) based on gender. 

The method to calculate gender gap ratio is using the formula ((Male access - Female access)/Male Access) adopted by EIU(The Economist Intelligence Unit).

Positive value indicates that male access exceeds female access and a Negative value indicates vice versa.

In Asia, Pakistan has the highest gender gap in internet accessibility with close to 70% females having no access when compared to males.(Dark Blue indicates more male access, Dark Brown indicates more female access)

Countries like China, Kazhakstan, Philippines, Mongolia have females with more internet access when compared to males.

Below is a world map focusing majorly on Asia;

A scatter plot to show where India stands among rest of the countries;

Countries on first quadrant are the ones with more male population having access to internet than females (a higher gender gap with needle leaning more towards male).

PS : Credits to Andy Kriebel (Youtube channel link) who's channel has tonnes of resources related to Tableau and I have taken lot of inspiration from his video while working on this dataset.