warning: Creating default object from empty value in /var/www/drupal/modules/taxonomy/taxonomy.pages.inc on line 33.


Perl6 Data Visualizations

The following are a few examples of some quick queries and visualizations we made to show how to use the Perl6 IRC data.

(1) Posts by hour of day over the years

SQL code:

SELECT YEAR(`date_of_entry`),HOUR(`time_of_entry`),COUNT(HOUR(`time_of_entry`))
FROM `perl6_irc`

(2) Perl6 IRC Posts by hour

SQL Code:

SELECT HOUR( `time_of_entry` ) , COUNT( HOUR( `time_of_entry` ) )
FROM `perl6_irc`

(3) Perl6 Developer IRC Posts Over Time

SQL Code:

SELECT `send_user` , YEAR( `date_of_entry` ) , MONTH( `date_of_entry` ) , `date_of_entry` , COUNT(
FROM `perl6_irc`
WHERE `send_user` = 'pmichaud'
OR `send_user` = 'audreyt'
OR `send_user` = 'TimToady'
OR `send_user` = 'jnthn'
GROUP BY 1 , 2, 3

Bitcoin-dev, Ubuntu, Perl6, Django, Puppet IRC logs are updated

Thanks to the work of my two summer research assistants Evan Ashwell & Greg Batchelor, the IRC channels for #bitcoin-dev, perl6, #ubuntu, #django, and puppet (#gen, #dev, and #razor) have been updated.

Things to know:

  • These IRC chats are only available on the FLOSSmole MySQL database server (how to get access) and not as flat files. Why? Well, they started out as flat files, so we don't want to just re-host flat archives. The original flat files are available for Puppet (puppetlogs.com), Bitcoin-dev (bitcoinstats.com), Ubuntu (Ubuntu Logs), Perl6 (Perl6 logs), and Django (Django IRC logs)
  • The data model is one day = one datasource id
  • The chat logs have been divided into the following columns (some logs have fewer columns):
    • datasource_id
    • line_num
    • line_message
    • type
    • send_user
    • date_of_entry
    • time_of_entry
    • unix_time
    • last_updated
  • An example row looks like the following:
    • 61835
    • 42
    • ah thanks. I'll search.
    • message
    • arubi
    • 2016-05-28
    • 21:38:00
    • l1464471492.0
    • 2016-06-02 13:03:3
Syndicate content