Dhanny Indrakusuma

[dhannywi]

Tableau Dashboard: COVID-19 impact on consumer expenditure

The analysis was created using data from U.S. Bureau of Labor Statistics on Consumer Expenditure Survey. The dashboard visualized the spending patterns from 2018 to 2020 and shows how the pandemic have changed the way Americans spend prior to and after the COVID-19 pandemic.

[dhannywi]

Hogwarts Acceptance Form

One of the projects for the FreeCodeCamp's Responsive Web Design certification was building a survey form.

As a Harry Potter fan, I reimagined the project as "What if Hogwarts embraced technology and send their potential student a QR code that links to a web form along with their acceptance letter? What would that form look like?"

Instead of students sending their owls with their notice of acceptance, this form will act as their virtual owl.

» view demo

» view code

[dhannywi]

Music Festival Analysis

Music festivals makes up a large chunk of the $20B global concert industry. Within the last decade, music festivals have grown into a major moneymaker in a competitive industry that sees hundreds of such events each year in the U.S. There are the big ones—Coachella, Lollapalooza, Outside Lands, Governors Ball—with big-ticket prices, multiple stages, camping options and nearly endless lists of performers. 

As music festivals have taken off in popularity, these kids of experiences have become a key feature of cultural life in America. Since I currently live in Austin, TX, home to two of the biggest music festivals - South by Southwest (SXSW) and Austin City Limits (ACL), I decided to analyze the database of a music festival for my personal project.

Music festivals make most of their money through income generated from ticket sales, sponsorship, advertising, concession fees and merchandise.  For the festival producer, gathering this data and insights can help improve the sustainability of the festival and provide potential cost reduction benefits. On the revenue side, the insights could lead to better attendee experiences and drive more returning consumers: reducing churn and thereby increasing the lifetime value of festival-goers.

Dataset

I used the class_music_festival database, which consisted of 6 tables. Below are the different tables and a brief description of them. You can find the full dataset on my GitHub.

bands -  contains data of bands that performed in the music festival,  including the band’s name and fee.

venues -  contains data of venues used for the music festival,  including the venue’s name and capacity.

performances -  contains data of the music festival’s performances,  including the start and end time as well as band and venue for the performance.

tickets -  contains data of tickets sold, including price and ticket number as well as the performance and person related to the ticket.

purchases - contains purchase data such as date of purchase and customer associated with the purchase.

people -  stores customer’s information.

Objective and  Goals

In this project, I aim to address the festival organizer’s questions below:

The festival organizer wanted to reward the top spender and the first 10 people that purchased tickets. We need to find out the names of the customers.

What is the name of the band performing at the AMD venue that had the highest fee?

Which bands performed at their biggest venue (more than 2000 capacity)?

How many tickets were sold for the performance that started at 2011-09-18 20:30:00?

Which performance and venue had the highest revenue?

Which performance and band were the most / least profitable for the festival?

Was there any venue that was oversold?

 What was the total revenue from ticket sales each month?

What was the average purchase total each month?

Before getting started with analyses, I first tried understanding the ERM (Entity Relationship Model) of this database also known as Schema. I have re-created the Entity Relationship Diagram for the music festival database below:

I have used PostgreSQL for the analysis below. You can view the full code on my GitHub.

The organizers had announced a giveaway for the first 10 customers. They wanted to know the attendee’s names to announce the winners.

SELECT people.name AS customer_name FROM people WHERE people.id IN (SELECT purchases.person_id FROM purchases ORDER BY purchases.date ASC LIMIT 10);

What is the name of the person who spent the most (and how much did they spend)?

SELECT people.id, people.name, SUM(price) as total_spent FROM people JOIN purchases ON purchases.person_id = people.id JOIN tickets ON tickets.purchase_id = purchases.id GROUP BY people.id ORDER BY total_spent DESC LIMIT 1;

What is the name of the band performing at the AMD venue that had the highest fee?

SELECT bands.name FROM performances JOIN venues ON venues.id = performances.venue_id JOIN bands ON bands.id = performances.band_id WHERE venues.name = 'AMD' ORDER BY fee DESC LIMIT 1;

Which bands performed at the venue with more than 2000 capacity?

SELECT DISTINCT bands.name AS band_name FROM bands WHERE bands.id IN (SELECT performances.band_id FROM performances WHERE venue_id = (SELECT venues.id FROM venues WHERE venues.capacity > 2000));

How many tickets were sold for the performance that started at 2011-09-18 20:30:00?

SELECT COUNT(*) FROM tickets  JOIN performances    ON performances.id = tickets.performance_id WHERE performances.start = '2011-09-18 20:30:00';

Which performance and venue had the highest revenue?

SELECT performances.id, venues.name AS venue_name, SUM(price) as revenue FROM performances JOIN tickets ON tickets.performance_id = performances.id JOIN venues ON venues.id = performances.venue_id GROUP BY performances.id, venues.name ORDER BY revenue DESC LIMIT 1;

Which performance (and by which band) was the most profitable?

SELECT performances.id, bands.name AS band_name, (SUM(tickets.price)-SUM(DISTINCT bands.fee)) AS profit FROM performances JOIN tickets ON tickets.performance_id = performances.id JOIN bands ON bands.id = performances.band_id GROUP BY performances.id, bands.name ORDER BY profit DESC LIMIT 1;

Which band was the least profitable for the festival?

SELECT bands.id, bands.name AS band_name, (SUM(tickets.price)-SUM(DISTINCT bands.fee)) AS profit FROM bands JOIN performances ON bands.id = performances.band_id JOIN tickets ON performances.id = tickets.performance_id GROUP BY bands.id, bands.name ORDER BY profit LIMIT 1;

Which venues were oversold and what were their capacities?

SELECT performances.id, venues.name, SUM(DISTINCT capacity) AS capacity, (COUNT(DISTINCT ticketnum) - SUM(DISTINCT capacity)) AS overcapacity FROM venues JOIN performances ON venues.id = performances.venue_id JOIN tickets ON performances.id = tickets.performance_id GROUP BY performances.id, venues.name HAVING (COUNT(DISTINCT ticketnum) - SUM(DISTINCT capacity)) > 0;

What was the total revenue from ticket sales each month?

SELECT EXTRACT(MONTH FROM purchases.date) AS month, SUM(tickets.price) AS revenue FROM tickets JOIN purchases ON purchases.id = tickets.purchase_id GROUP BY month ORDER BY month;

What was the average purchase total each month?

SELECT EXTRACT(MONTH FROM purchases.date) AS month, ROUND((SUM(price::decimal)/COUNT(DISTINCT purchases.id)),2) AS avg_purchase FROM tickets JOIN purchases ON purchases.id = tickets.purchase_id GROUP BY month;

Conclusion

Looking at the analysis above, the festival organizers will need to ensure that they are not overselling tickets for the AMD venue since overcrowding may result in decrease of attendee’s enjoyment of the performance. From the analysis we have concluded that Preservation Hall Jazz Band and The Del McCoury Band and Vista Equity had the highest revenue, while the band WAX is the least profitable. The organizers can consider tracking attendee’s enjoyment of each performance, in a way that makes it possible to ask which bands/venues are the most popular.

» view project

[dhannywi]

The Beatles Hangman

Using data of Beatles lyrics from The Guardian’s datablog, I decided to add a twist to the interactive Hangman wordgame that I did for MITx: 6.00.1x “Introduction to Computer Science and Programming Using Python” course.

For “The Beatles Hangman” game, the second player will be the computer, who will be picking a word at random. The user has 8 guesses and with each guess the computer will print out the result and remaining available characters. These words are found in The Beatles’ lyrics, so we’ll see if you are a true fan!

You can find the code on my Github. Have fun playing the game!

See below for a transcript of a successful session:

Loading word list from file... 1175 words loaded. **************************************************** _ _ _ | | | | | | | |__ ___ __ _| |_| | ___ ___ | '_ \ / _ \/ _` | __| |/ _ \/ __| | |_) | __/ (_| | |_| | __/\__ \ |_.__/ \___|\__,_|\__|_|\___||___/ H A N G M A N **************************************************** Hello Beatlemaniacs, welcome to the game! I am thinking of a word that is 7 characters long. ------------ You have 8 guesses left. Available characters: abcdefghijklmnopqrstuvwxyz-.' Please guess a character: a Good guess: _ _ _ _ _ a_ ------------ You have 8 guesses left. Available characters: bcdefghijklmnopqrstuvwxyz-.' Please guess a character: e Oops! That character is not in my word: _ _ _ _ _ a_ ------------ You have 7 guesses left. Available characters: bcdfghijklmnopqrstuvwxyz-.' Please guess a character: t Good guess: _ _ _ t_ a_ ------------ You have 7 guesses left. Available characters: bcdfghijklmnopqrsuvwxyz-.' Please guess a character: m Good guess: _ _ _ tma_ ------------ You have 7 guesses left. Available characters: bcdfghijklnopqrsuvwxyz-.' Please guess a character: i Oops! That character is not in my word: _ _ _ tma_ ------------ You have 6 guesses left. Available characters: bcdfghjklnopqrsuvwxyz-.' Please guess a character: w Oops! That character is not in my word: _ _ _ tma_ ------------ You have 5 guesses left. Available characters: bcdfghjklnopqrsuvxyz-.' Please guess a character: h Oops! That character is not in my word: _ _ _ tma_ ------------ You have 4 guesses left. Available characters: bcdfgjklnopqrsuvxyz-.' Please guess a character: s Good guess: _ _ stma_ ------------ You have 4 guesses left. Available characters: bcdfgjklnopqruvxyz-.' Please guess a character: k Oops! That character is not in my word: _ _ stma_ ------------ You have 3 guesses left. Available characters: bcdfgjlnopqruvxyz-.' Please guess a character: o Good guess: _ ostma_ ------------ You have 3 guesses left. Available characters: bcdfgjlnpqruvxyz-.' Please guess a character: h Oops! You've already guessed that character: _ ostma_ ------------ You have 3 guesses left. Available characters: bcdfgjlnpqruvxyz-.' Please guess a character: p Good guess: postma_ ------------ You have 3 guesses left. Available characters: bcdfgjlnqruvxyz-.' Please guess a character: n Good guess: postman ------------ Congratulations, you are a true fan!

» view project

[dhannywi]

COVID-19 Visualization

After taking the Statistics with Python course on Coursera, I wanted to immediately apply my learnings by putting my own twist to this project.

I am using datasets from this COVID-19 Github Repository that is updated daily. Since I live in Indonesia, I am interested to know which Southeast Asian Countries have the highest infection rate.

Using matplotlib, I visualized the number of cases for each countries for the past year from February 2020 to February 2021. The graph shows us that Indonesia has the highest infection rate that is still steadily increasing while other countries’ infection rate are slowing down with the exception of Malaysia. However, we need to keep in mind that Indonesia is the 4th most populous country in the world after the United States.

Since Indonesia has a significantly larger population compared to the other five countries, it will be useful to understand Indonesia’s infection rates relative to its population. For the second visualization, I plotted a Per Capita COVID-19 Cases to see each country’s number of cases per 100,000 people. Surprisingly, Singapore emerged at the top and Indonesia is 4th when taking population size into consideration.

Looking at visualizations above, Indonesia has some of the highest COVID-19 cases compared to other Southeast Asian countries. Taking into account that Indonesia has the 4th largest population globally, it will be useful to compare Indonesia’s COVID-19 cases relative to countries with the most cases worldwide.

As seen on the visualization below, Indonesia’s total cases isn’t as significant as the top 5 countries, namely the United States, India, Brazil, Russia and Italy. It is no surprise that India and US has the highest number of cases as they have the 2nd and 3rd largest population worldwide. It is important to note that China isn’t in the top 5 countries although it has the largest population and was the first to record COVID-19 cases. This is probably due to their strict regulations around testing and mask wearing as well as an effective handling of COVID-19 cases.

Let’s look at the Per Capita COVID-19 Cases to see each country’s number of cases per 100,000 people. On both graphs, we observe a spike of cases in the US around early December. This is likely caused by the Thanksgiving holidays observed in the US on November 26th, 2020. Looking at both graphs, we can see that Indonesia’s cases are much lower than the rest of the world, but it is still steadily rising.

»View Jupyter Notebooks on Github

[dhannywi]

Page rank visualization

For Python for Everybody Specialization’s capstone project, I visualized networks and interconnections by first crawling the https://fashionista.com/ and pull a series of pages into the database, recording the links between pages. The application then ran a simplified version of the Google page rank algorithm to determine which pages are most highly connected, and then visualize the page rank and connectivity. D3 JavaScript visualization library was used to produce the visualization output.

Each nodes illustrates how densely connected each web pages are with the home page, events, beauty, shopping, contact page as the top 5 most highly connected.

» view project

[dhannywi]

Google Map from geocoded data

As part of the Databases and Visualization project for the Python for Everybody Specialization, I used the Google GeoCoding API to retrieve data, clean up some user-entered geographic locations and then use Google Maps to visualize the data.

The map shows the various locations Python for Everybody students are from, with USA, Europe and India as the top three location.

» view project

[dhannywi]

Tribute Page

I created a tribute page for Michelle Obama using HTML & CSS. The page highlighted her journey in becoming the 44th First Lady of the United States of America until the launch of her memoir “Becoming.”

» view demo

» view code