Part 1: Analyze Twitch Data with SQL¶

Part 2: Visualize Twitch Data with Matplotlib

In this project we'll do some simple analysis of data from Twitch with SQL. Though probably not ideal for this workflow, the choice of sql over pandas is made for the sake of learning to use sql within jupyter.

Setup:¶

Imports¶

import pandas as pd
import sqlite3 as sql

%load_ext sql
%config SqlMagic.displaylimit=50

Read in data¶

chat = pd.read_csv('chat.csv')
stream = pd.read_csv('video_play.csv')

Create database and populate with csv data¶

%sql sqlite:///twitch.db
%sql PERSIST chat
%sql PERSIST stream

 * sqlite:///twitch.db
 * sqlite:///twitch.db

'Persisted stream'

Get a feel for the data¶

%sql SELECT * FROM stream LIMIT 20

 * sqlite:///twitch.db
Done.

%sql SELECT * FROM chat LIMIT 20

 * sqlite:///twitch.db
Done.

%sql SELECT DISTINCT game FROM stream

 * sqlite:///twitch.db
Done.

%sql SELECT DISTINCT channel FROM stream

 * sqlite:///twitch.db
Done.

Analysis¶

What are the most popular games? We'll save the result to use in visualization in Part 2.

%%sql popular_games <<
SELECT game, COUNT(*) AS views
FROM stream
GROUP BY 1 
ORDER BY 2 DESC
LIMIT 10

 * sqlite:///twitch.db
Done.
Returning data to local variable popular_games

popular_games

Where are all the League of Legends viewers from? Again we'll save the result for future use in visualization.

%%sql lol_view_locs <<
SELECT country, COUNT(*) AS lol_views
FROM stream
WHERE game IS 'League of Legends'
AND country <> 'None'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

 * sqlite:///twitch.db
Done.
Returning data to local variable lol_view_locs

lol_view_locs

What devices are being used to play content?

%%sql
SELECT player, COUNT(*) AS num_streams
FROM stream
GROUP BY 1
ORDER BY 2 DESC

 * sqlite:///twitch.db
Done.

Let's add some genre information and count views by genre.

%%sql
SELECT
 CASE
  WHEN game = 'Dota 2'
      THEN 'MOBA'
  WHEN game = 'League of Legends' 
      THEN 'MOBA'
  WHEN game = 'Heroes of the Storm'
      THEN 'MOBA'
  WHEN game = 'Counter-Strike: Global Offensive'
      THEN 'FPS'
  WHEN game = 'DayZ'
      THEN 'Survival'
  WHEN game = 'ARK: Survival Evolved'
      THEN 'Survival'
  ELSE 'Unknown'
 END AS 'genre',
  COUNT(*) as views
FROM stream
GROUP BY 1
ORDER BY 2 DESC

 * sqlite:///twitch.db
Done.

How do views change over the course of the day? We'll also save this result set to use in the visualizition portion of the project.

%%sql views_by_hour <<
SELECT STRFTIME('%H', time) AS hour, COUNT(*) AS views
FROM stream
GROUP BY 1

 * sqlite:///twitch.db
Done.
Returning data to local variable views_by_hour

views_by_hour

Join the tables¶

%%sql
SELECT *
FROM stream
JOIN chat
ON stream.device_id = chat.device_id

 * sqlite:///twitch.db
Done.

index	time	device_id	login	channel	country	player	game	stream_format	subscriber
0	2015-01-01 18:33:52	40ffc2fa6534cf760becbdbf5311e31ad069e46e	085c1eb7b587bfe654f0df7b4ba7f4fc4013636c	frank	US	iphone_t	League of Legends	None	None
1	2015-01-01 23:35:33	9a8cc2b7162b99c0a0f501dc9a5ec4f68586a760	5ad49a7b408ce452140b180dd6efb57a9f4d22c7	george	US	site	DayZ	chunked	0
2	2015-01-01 04:39:38	2f9c3f9ee1033b71a3819564243f20ef3bec0183	5b9a43e68f019185f55615d0b83019dee4b5d06f	frank	US	site	League of Legends	chunked	0
3	2015-01-01 11:15:30	0cda8226ba2583424c80c3c1c22c1256b080ad17	02c7797faa4d8a3ff4b0c14ee1764b6817b53d0b	estelle	CH	site	Dota 2	high	0
4	2015-01-01 11:28:19	e3288ca5e3153aa85e32f64cdd994b7666968dcf	b920c228acbcbebee26d9c79f6eb73b73a9480c7	morty	FR	site	Heroes of the Storm	medium	0
5	2015-01-01 23:27:36	343fe2bfd58595d5c18602d420ecf6f9d694d5a8	7814f661a54349ff5eee84f9d6f476918c9b7270	george	US	iphone_t	DayZ	None	None
6	2015-01-01 21:09:23	80a0c7d1abb6a5a0060e18202b77bef831e08ca5	eb158cab3f606d3894a32e20bddbfd2d589095a9	frank	US	site	League of Legends	high	1
7	2015-01-01 19:14:27	1e342e5e4e228f617449029054b3bb19c5224528	2aaf6a414bc3dc923b04f986de7ba1b8101c6698	frank	CA	site	League of Legends	high	0
8	2015-01-01 13:51:04	272cffbb1a9a33ad3bb48a2ee9ae5cbcac5ca22e	401716920e3435b5e3eec9fc4ccd6a44c7af38f5	kramer	TR	site	Counter-Strike: Global Offensive	chunked	0
9	2015-01-01 22:00:14	593ed161c456eeeb9e18b8005786d42abc1a7373	ef24dc49ceb4bcd3cccb0fa862d8a150ecf935e1	frank	US	site	League of Legends	medium	0
10	2015-01-01 19:10:58	16679ceba8bf5fb10f253e51f9a286c11b011e02	3b8deb77fb36b6147c27cbdb82a925aaf26b629b	jerry	RU	embed	Dota 2	chunked	0
11	2015-01-01 22:59:56	1a247324beabdb85b2b8a97472f28f6cf9026fe1	c485d2914068876ebd6cf3ee6812dc939abae8d7	frank	US	android	League of Legends	None	None
12	2015-01-01 22:10:46	20bde4b4418df3ce2701869233e12b1b4a30da88	7abc32d6617b4a13b78dd2710f22fe8a2305f0fd	george	US	ipad_t	DayZ	None	None
13	2015-01-01 13:46:00	21d94696c9b0f4f9eafc785a2d9d1be1982244b1	c2de0a4d1258e2fc093017d5a793a625af9a0d44	estelle	US	embed	Dota 2	high	0
14	2015-01-01 10:07:45	5e3a498f6c671a42edfa3772c87c1c3dc918a511	ffec857d76dcf8cfee7f3a96028365f33964c823	morty	None	ipad_t	Heroes of the Storm	None	None
15	2015-01-01 21:04:20	2e7bcee08428c8ba8c5ce7d44a930dfee7f34db2	4fac13dc5b1cc9d3b18ad0ff62da2adb635375fd	frank	US	site	League of Legends	high	0
16	2015-01-01 12:41:15	88b8d83a7ac64d30f2b3f54cc9d9c2a15bb2f666	9f2d3dcb56169dcdc0933d21004ce0f2e40b0df0	estelle	TR	site	Dota 2	high	0
17	2015-01-01 21:30:35	53fc815c5fb6e8641dc8a60cf2cc480198a2ba17	122ab65809db2c8057c998d0664b2f54f5f6aa10	frank	US	android	League of Legends	None	None
18	2015-01-01 22:03:58	f74aae10baab29ab434519adb4ad4f64f6d90727	3b954baa601d4cb22610dc4727543c697acbd4d6	frank	US	iphone_t	League of Legends	None	None
19	2015-01-01 14:47:24	47d380927c426e589be566d4ae967cbb6f9d2d13	40b89a7097d30ecf1fe4591442f541ed500c5b8b	helen	CA	iphone_t	Hearthstone: Heroes of Warcraft	None	None

index	time	device_id	login	channel	country	player	game
0	2015-01-01 18:45:50	70e2b95b5ac0d4c227e46966658d16b3e044996e	5c2f5c1f19a7738e16ed0be551d865e8a8fce71d	jerry	BY	None	Dota 2
1	2015-01-01 01:16:57	f2b9065b55fd80d6aa653ce989b489f4ec5198be	0d77740e4fb5ce77d94f9f6c8ef1f762990d0344	elaine	HK	None	Devil May Cry 4: Special Edition
2	2015-01-01 16:22:10	d448ba963d7e1023dd1b0a40b95d4f6611750692	77ab14c1fb815e1c369ba0cf7d4c56b4fe489997	frank	GB	iphone_t	League of Legends
3	2015-01-01 03:58:13	8d6823dc52b400b50aebf269bf1f03a36d19eeaa	91cb88c0743761589273fc5e800e7743ece46494	frank	US	iphone_t	League of Legends
4	2015-01-01 11:47:35	16c1e39594d62358d27ae604ad43a071f0d86bc4	51a9234f83d656607cfd7f26690c12d2ffbce353	estelle	DE	None	Dota 2
5	2015-01-01 17:59:51	6fcc75522de37833a0fb21fba4965aad3b63ea57	f628d1cb946ea2e8cffc0b327bc9d77775b8d3c0	jerry	RU	None	Dota 2
6	2015-01-01 02:24:33	dea94b3030025d837dd841fbfd479e775987f65d	9dbbcf6c7792074771c4c7284807041eac467ad5	elaine	TW	None	Gaming Talk Shows
7	2015-01-01 18:26:34	671bee0f3d66077876d9bc231990597292392cc2	51c286a41daa8e060275f622f2b8436bee9fab91	jerry	UA	None	Dota 2
8	2015-01-01 13:13:18	8b31d5ebd1f4f41d4365ae4a471c1686dd256745	06decad1d9565150791e183da017f47123433a4c	estelle	GB	ipad_t	Dota 2
9	2015-01-01 20:20:55	f2ebb129e6930e608f2ed3f5fb52bc4d533c4891	4679f8113aa157ba76fc6db5878d7ee625e88d55	frank	CA	None	League of Legends
10	2015-01-01 22:47:24	faf5c126f52f52f4b23c057c31f35fb0cdfbcdf3	cf2c292a9cc31cc398428ec82a872e620d3237df	george	None	None	DayZ
11	2015-01-01 17:26:27	536bacfa1cd0b39f77eb167689f64d144464ca12	c7f232fb8ab54e78cff1e953d38d09f40b01ce81	frank	None	None	League of Legends
12	2015-01-01 17:38:00	c9ba7595a583e5964fb74a9fb95f07790362cc35	9b4d863c2b0ab0c4d1e8d33665dab469f15e87b0	newman	US	None	The Binding of Isaac: Rebirth
13	2015-01-01 11:53:47	dcffedc3f5a46a895d70d70d5c9c9abfdcb7c33d	8464a5359d8df9d6636355b391b8748d856bc61b	elaine	TW	None	Gaming Talk Shows
14	2015-01-01 11:23:57	c480007a2b82f6fb0cb94c2b9b445ef99d8d68e7	e17102753876688a04b17142128a16259eaf8374	estelle	US	None	Dota 2
15	2015-01-01 06:01:09	698db5a0e9c4025fe2a1cd094d0cdda647831339	770a8dc070d9db7ab582bc8a27e436fa8aadeb0a	susan	UA	None	World of Tanks
16	2015-01-01 21:51:07	dcd963bb1f416e843781bd5e350c23ffc757201d	eb05316cffb53b70eb5cb8c185557d23a31d2b93	newman	US	None	The Binding of Isaac: Rebirth
17	2015-01-01 13:52:27	7361fc61d20d509e14d1bceb095e950a4a53be8d	62c856bc9a1965df787fefa56ff46c8f2316e3a0	kramer	DE	None	Counter-Strike: Global Offensive
18	2015-01-01 01:20:08	a8f2b43217deffd17fc5589f0933f90224a9f59e	75e990cf18fc10e69d6d765c555cf07543689f4c	frank	US	ipad_t	League of Legends
19	2015-01-01 09:45:44	53210c06e61e8fa4efcb88ac7bb83163c2d98500	1a18a5fd4f0972a7850e15acbdf93695d2747611	morty	DE	android	Heroes of the Storm

game	views
League of Legends	193533
Dota 2	85608
Counter-Strike: Global Offensive	54438
DayZ	38004
Heroes of the Storm	35310
The Binding of Isaac: Rebirth	29467
Gaming Talk Shows	28115
World of Tanks	15932
Hearthstone: Heroes of Warcraft	14399
Agar.io	11480

country	lol_views
US	85606
CA	13034
DE	10835
GB	6964
TR	4412
AU	3911
SE	3533
NL	3213
DK	2909
GR	2885

player	num_streams
site	246115
iphone_t	100689
android	93508
ipad_t	53646
embed	19819
xbox_one	4863
home	3479
frontpage	1567
amazon	1155
xbox360	985
roku	233
chromecast	149
facebook	83
nvidia shield	3
ouya	3
android_pip	2

hour	views
00	15411
01	14407
02	24141
03	16205
04	15098
05	6265
06	1483
07	8505
08	11223
09	9863
10	11584
11	33645
12	50261
13	43390
14	26219
15	26707
16	25191
17	28350
18	28863
19	28374
20	29816
21	29399
22	22062
23	19837

game
League of Legends
DayZ
Dota 2
Heroes of the Storm
Counter-Strike: Global Offensive
Hearthstone: Heroes of Warcraft
The Binding of Isaac: Rebirth
Agar.io
Gaming Talk Shows
None
Rocket League
World of Tanks
ARK: Survival Evolved
SpeedRunners
Breaking Point
Duck Game
Devil May Cry 4: Special Edition
Block N Load
Fallout 3
Batman: Arkham Knight
Reign Of Kings
The Witcher 3: Wild Hunt
The Elder Scrolls V: Skyrim
Super Mario Bros.
H1Z1
The Last of Us
Depth
Mortal Kombat X
Senran Kagura: Estival Versus
The Sims 4
You Must Build A Boat
Choice Chamber
Music
Risk of Rain
Grand Theft Auto V
Besiege
Super Mario Bros. 3
Hektor
Bridge Constructor Medieval
Lucius

genre	views
MOBA	314451
Unknown	115248
FPS	54438
Survival	42162