Youtube | Game

YOUTUBE DATA ANALYSIS

OBJECTIVE

Extract data from Youtube API, transfer data to SQL and visualize the analysis in webapp using Streamlit.

Quick Links :-

VIEW GITHUB

Tools Used :-

Pandas, Streamlit, Plotly, SQL

INTRODUCTION

YouTube is an American online video-sharing platform owned by Google. Accessible worldwide, YouTube was launched on February 14, 2005, by Steve Chen, Chad Hurley, and Jawed Karim, three former employees of PayPal

Why this Dataset:- This is a real-world dataset that fetched from Youtube API. Being a youtube I wanted to analyze realtime data of various youtube channels in my category.

Overall Goal:- Visualize and analyze patterns of youtube channels in vlogging category and get familiar with SQL.

EXTRACTION

The JSON file is then traversed to fetch the data and put in a CSV file and also insert into SQL tables.

EXPLORATION

Through exploring the data we collected data from the Api and created the following tables in SQL:

Channel
Videos
Comments

Data is inserted into SQL table using the insert tab in the webapp.

The insertion is done using Channel ID. The channel Id is inserted in the input files and used the following SQL QUERY to insert the data.

VISUALIZATION

For the home page, we can get a list of channel from the SQL table.

query = "select channel_name from channel"

Get the channel details of the selected channel.

query = f"SELECT channel_id,channel_name, channel_subscribers, channel_views, channel_videos, channel_description FROM channel WHERE channel_name = '{channel}'"

Channel Details

Channel Videos

For the analysis we have 10 question. We use SQL queries to fetch the data

1. What are the names of all the videos and their corresponding channels?

SELECT v.video_name as Video, c.channel_name as Channel FROM video v JOIN channel c ON v.channel_id = c.channel_id;

2. Which channels have the most number of videos, and how many videos do they have?

SELECT c.channel_name as Channel, COUNT(v.video_id) as Video_Count FROM channel c INNER JOIN video v ON c.channel_id = v.channel_id GROUP BY c.channel_id, Channel ORDER BY Video_Count DESC;

3. What are the top 10 most viewed videos and their respective channels?

SELECT v.video_name as Video, c.channel_name as Channel, v.view_count as Views FROM video v INNER JOIN channel c ON v.channel_id = c.channel_id ORDER BY v.view_count DESC limit 10;

4. How many comments were made on each video, and what are their corresponding video names?

SELECT v.video_name Title, COUNT(c.comment_id) AS Comments FROM video v LEFT JOIN comment c ON v.video_id = c.video_id GROUP BY v.video_name ORDER BY Comments DESC;

5. Which videos have the highest number of likes, and what are their corresponding channel names?

SELECT v.video_name as Video, ch.channel_name as Channel, v.like_count as Likes FROM video v JOIN channel ch ON v.channel_id = ch.channel_id ORDER BY Likes DESC LIMIT 10;

6. What is the total number of likes and dislikes for each video, and what are their corresponding video names?

SELECT v.video_name as Video, SUM(v.like_count) AS Likes, SUM(v.dislike_count) AS Dislikes FROM video v GROUP BY v.video_name;

7. What is the total number of views for each channel, and what are their corresponding channel names?

SELECT channel_name as Channel,channel_views as Views from channel;

8. What are the names of all the channels that have published videos in the year 2022?

SELECT DISTINCT c.channel_name as Channel FROM channel c JOIN video v ON c.channel_id = v.channel_id WHERE YEAR(v.published_date) = 2022;

9. What is the average duration of all videos in each channel, and what are their corresponding channel names?

SELECT c.channel_name as Channel, CONCAT(FLOOR(AVG(v.duration) / 60), ' m ', ROUND(MOD(AVG(v.duration), 60)), ' s') AS Average_Duration FROM channel c JOIN video v ON c.channel_id = v.channel_id GROUP BY c.channel_name;

10. Which videos have the highest number of comments, and what are their corresponding channel names?

SELECT v.video_name as Video, c.channel_name as Channel, COUNT(co.comment_id) AS Comments FROM video v JOIN channel c ON v.channel_id = c.channel_id JOIN comment co ON v.video_id = co.video_id GROUP BY v.video_name, c.channel_name ORDER BY Comments DESC;

WHAT I LEARNED

Extract data from Youtube API.
Insert data to SQL.
Use SQL queries to answer analysis question.