Analyzing OngoingWorlds posts

July 15, 2017

The previous post used Scrapy to extract post data from the website OngoingWorlds. Here are a few conclusions from that spider crawl:

I collected the game ID, post ID and date/time for each post from the play-by-email roleplaying community OngoingWorlds into an Sqlite3 database. Even with this very limited dataset, some interesting queries can be run:

Most popular games (by number of posts)

SELECT game_id, COUNT(*) FROM post GROUP BY game_id ORDER BY COUNT(*) DESC LIMIT 10;

Rank Game Total posts
1 Blue Dwarf 15040
2 Hero High 3453
3 2778 A.D. 2894
4 The Land of Ecilith 2276
5 The Avengers~Lower Levels 2111
6 Heroes Association 1288
7 Hunted 1265
8 Circle of Nine 1176
9 Fairy Tail ZERO 1125
10 MLP fans! 1118

Most popular games (per hour of the day)

SELECT
  game_id AS GameID,
    (
    SELECT strftime("%H", timestamp) AS Hour
    FROM post AS inner
    WHERE inner.game_id = outer.game_id
    GROUP BY Hour
    ORDER BY COUNT(*) DESC
    LIMIT 1) AS MostPopularHour,
  COUNT(*) TotalPosts
  FROM post AS outer
  GROUP BY GameID
  ORDER BY MostPopularHour, TotalPosts DESC

For easier viewing I exported the result to a CSV spreadsheet, as follows:
sqlite3 -header -csv results.db 'SELECT game_id AS GameID, (SELECT strftime("%H", timestamp) AS Hour FROM post AS inner WHERE inner.game_id = outer.game_id GROUP BY Hour ORDER BY COUNT(*) DESC LIMIT 1) AS MostPopularHour, COUNT(*) TotalPosts FROM post AS outer GROUP BY GameID ORDER BY MostPopularHour, TotalPosts DESC' > results-agg.csv

Hour of the day (CEST) Game
0 The Elite Club
1 The Hotel
2 Hero High
3 The Avengers~Lower Levels
4 The Land of Ecilith
5 Redwill Home for Unusual and Odd Children
6 Cure
7 Advanced
8 Battle of the Bands
9 Vale of Shadows
10 Pokemon Azure
11 The open trail
12 BRAIN GAMES: Raido Ravens
13 Bloody Gifts
14 Circle of Nine
15 Academy for Super Humans
16 MLP fans!
17 Gakuen Statalia
18 The Verse – Other Adventures in the Firefly Universe
19 Magic Agents
20 Blue Dwarf
21 Another West
22 Heroes Association
23 Day After, Hero’s Past

Most popular posting hours (CEST)

flattr this!

Previous post: