You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 

170 KiB

Statistics for Nanna's email of 2019-02-13

Hi Herbert,

you would be a lifesaver if you could give me some updated information about the following (regarding the Stimmen database):

  • what's the latest breakdown of age, gender, most active language distribution in all the data, for the prediction data, and for the recording data?
  • How many individual users are there in the corpus of picture task recordings?
  • How many users have made recordings in more than one language? (and if possible a list with the device ids)
  • it would be great to have the breakdown of amounts of quiz taking over the months - so how many in september 2017, october 2017, and so on up until september 2018

Any luck with any of these would be a massive help. I only need the data from 19 September 2017 onwards,

Nanna

In [1]:
# Enable portforwording from 3307 locally to 3306 on the stimmen database machine
# ssh -L 3307:127.0.0.1:3306 stimmen.housing.rug.nl

import pandas
import MySQLdb
from collections import defaultdict, Counter
from ipy_table import make_table, set_row_style
from IPython.display import display

from getpass import getpass

if 'mysql_password' not in globals():
    mysql_password = getpass()

db = MySQLdb.connect(host='127.0.0.1', port=3307, user='stimmen', passwd=mysql_password, db='stimmen', charset='utf8')
········
In [2]:
# Kind reminded of the table names
print('\n'.join(
    table
    for table in pandas.read_sql('''show tables;''', db)['Tables_in_stimmen']
    if table.startswith('core_')
))
core_freespeechresult
core_freespeechtheme
core_language
core_picturegameitem
core_picturegameresult
core_predictionquizresult
core_predictionquizresultquestionanswer
core_surveyanswer
core_surveyquestion
core_surveyquestioncategory
core_surveyresult
core_surveyresultquestionanswer

What's the latest breakdown of age, gender, most active language distribution in all the data, for the prediction data, and for the recording data?

In [3]:
def remove_repeating(rows, columns=[0]):
    """Empty cells which are the same as the cell above
    (in the previous row) for the specified columns, to create
    a less redunant and more readable table"""
    return [
        [
            '' if column == column_ and i in columns else column
            for i, (column, column_) in enumerate(zip(row, row_))
        ]
        for row, row_ in zip(rows, [[None] * len(rows[0])] + rows)
    ]
In [4]:
for gametype in ['prediction quiz', 'picture game', 'free speech']:
    demographics = pandas.read_sql('''
        SELECT 
            answer.question_text AS question,
            answer.answer_text AS answer,
            game.id AS game_id,
            game.survey_result_id AS survey_id
        FROM core_{gametype}result AS game
        LEFT JOIN core_surveyresultquestionanswer AS answer
            ON answer.survey_result_id = game.survey_result_id
        WHERE submitted_at > '2017-09-19'
    '''.format(
        gametype=gametype.replace(' ', '')
    ), db)
    
    table = [
        [
            question_text,
            question_answer,
            len(records)
        ]
        for question_text, answers in demographics.groupby('question')
        for question_answer, records in answers.groupby('answer')
    ]

    n_participants = len(demographics['game_id'].unique())
    n_no_survey = len(demographics[demographics['question'].isna()]['game_id'].unique())
    n_with_survey = len(demographics[~demographics['question'].isna()]['game_id'].unique())

    make_table(
        [
            [gametype, '', ''],
            ['question', 'answer', 'count'],
            ['Total participants', '', n_participants],
            ['No survey', '', n_no_survey],
            ['With survey', '', n_with_survey],
            ['', '', '']
        ] + [
            [question, 'total answers', len(answers_)]
            for question, answers_ in demographics.groupby('question')
        ] + [['', '', '']] +
        remove_repeating(sorted(table, key=lambda x: (x[0], -x[2])))
    )
    set_row_style(1, bold=True)
    display(set_row_style(0, bold=True))
prediction quiz
questionanswercount
Total participants15177
No survey11829
With survey3348
What is your age bracket?total answers3348
What is your gender?total answers3348
Which language are you the most proficient in?total answers3348
Which languages do you actively use in your life?total answers7848
What is your age bracket?11-201064
21-30748
41-50424
51-60414
31-40352
61-70239
71-8066
81-9018
0-1016
91-1007
What is your gender?female1689
male1640
other19
Which language are you the most proficient in?Frisian1740
Dutch1487
Bildts33
Stadsfrys33
English17
Other10
German5
Amelands4
Gronings3
Stellingwarfs3
Hindeloopers2
North Frisian2
Ostfriesisches Platt2
Turkish2
Arabic1
Drents1
Mandarin1
Sranan1
Terschellings1
Which languages do you actively use in your life?Dutch2867
Frisian2787
English1169
German335
City Frisian173
French126
Other93
Bildts72
Gronings31
Stellingwarfs26
Norwegian22
Amelands20
Hindeloopers13
Drents12
Arabic9
Terschellings9
Bahasa Indonesia8
Afrikaans7
Sranan7
Papiamentu6
Tarifit6
Turkish6
Mandarin Chinese5
North Frisian5
Ostfriesisches Platt5
Polish5
Niedersächsisch4
Schiermonnikoogs4
Galician3
Ostniederdeutsch3
Latgalīšu volūda2
Latviešu valoda2
Ostfälisch2
Sarnami Hindoestani2
Westfälisch2
picture game
questionanswercount
Total participants41655
No survey0
With survey41655
What is your age bracket?total answers41655
What is your gender?total answers41655
Which language are you the most proficient in?total answers41655
Which languages do you actively use in your life?total answers103303
What is your age bracket?21-3010255
11-209756
51-606126
41-505853
31-405661
61-702679
71-801017
91-100146
0-10134
81-9028
What is your gender?female24296
male17047
other312
Which language are you the most proficient in?Frisian20755
Dutch17878
English993
Other702
Stadsfrys449
Bildts419
German235
Gronings40
Amelands31
Drents28
North Frisian27
Turkish21
Galician15
Hindeloopers14
Stellingwarfs14
Arabic13
Terschellings11
Papiamentu10
Which languages do you actively use in your life?Dutch36236
Frisian32716
English17498
German5727
Other2769
City Frisian2350
French2145
Bildts903
Norwegian819
Gronings321
Stellingwarfs286
Amelands263
Drents207
Bahasa Indonesia157
North Frisian125
Tarifit125
Sranan120
Polish117
Niedersächsisch99
Terschellings85
Schiermonnikoogs39
Afrikaans30
Hindeloopers30
Mandarin Chinese26
Arabic22
Turkish21
Ostniederdeutsch19
Papiamentu14
Sarnami Hindoestani10
Westfälisch10
Latviešu valoda9
Ostfriesisches Platt5
free speech
questionanswercount
Total participants381
No survey0
With survey381
What is your age bracket?total answers381
What is your gender?total answers381
Which language are you the most proficient in?total answers381
Which languages do you actively use in your life?total answers953
What is your age bracket?11-20116
21-3086
31-4051
41-5042
51-6039
61-7018
71-8012
0-107
91-1006
81-904
What is your gender?male198
female169
other14
Which language are you the most proficient in?Dutch177
Frisian152
English17
Other14
German6
Bildts3
Stadsfrys3
Afrikaans2
North Frisian2
Amelands1
Mandarin1
Ostfriesisches Platt1
Sarnami Hindoestani1
Turkish1
Which languages do you actively use in your life?Dutch303
Frisian263
English169
German46
Other29
City Frisian24
French20
Gronings10
Bahasa Indonesia7
Norwegian7
Bildts6
Sranan6
Drents5
Polish5
Tarifit5
Afrikaans4
Amelands4
Arabic4
Hindeloopers4
Schiermonnikoogs4
Stellingwarfs4
Turkish4
Niedersächsisch3
Sarnami Hindoestani3
Terschellings3
Galician2
Mandarin Chinese2
Westfälisch2
North Frisian1
Ostfriesisches Platt1
Ostfälisch1
Ostniederdeutsch1
Papiamentu1

How many individual users are there in the corpus of picture task recordings?

The core_picturegameresult table has a survey_result_id and a session_id to distinguish users. Some sessions have multiple surveys and vice versa. So I figured the survey is the fairest way to distinguish them.

In [5]:
pandas.read_sql('''
    SELECT COUNT(DISTINCT survey_result_id), COUNT(DISTINCT session_id), COUNT(*)
    FROM core_picturegameresult
    WHERE submitted_at > "2017-09-19"
''', db)
Out[5]:
COUNT(DISTINCT survey_result_id) COUNT(DISTINCT session_id) COUNT(*)
0 1930 24695 41655

How many users have made recordings in more than one language?

  • and if possible a list with the device ids
  • it would be great to have the breakdown of amounts of quiz taking over the months: sep-17, oct-17, ..., sep-18

There are not so many, these are the ones:

In [6]:
pandas.read_sql('''
    SELECT
        CONCAT(YEAR(result.submitted_at), '-', MONTH(result.submitted_at)) AS 'year-month',
        COUNT(*) AS '# games',
        COUNT(DISTINCT result.survey_result_id) AS '# surveys',
        GROUP_CONCAT(DISTINCT lang.name SEPARATOR ',') AS 'languages'
    FROM core_freespeechresult AS result
    LEFT JOIN core_language as lang
      ON lang.id = result.language_id
    WHERE submitted_at > "2017-09-19"
    AND result.survey_result_id IN (
        SELECT result.survey_result_id
        FROM core_freespeechresult AS result
        WHERE submitted_at > "2017-09-19"
        GROUP BY result.survey_result_id
        HAVING COUNT(DISTINCT result.language_id) > 1
    )
    GROUP BY YEAR(result.submitted_at), MONTH(result.submitted_at)
''', db)
Out[6]:
year-month # games # surveys languages
0 2017-9 7 2 English,Nederlands,Frysk,Stadsfrys
1 2017-10 7 2 Frysk,Hielpers,Nederlands
2 2017-11 10 4 Nederlands,Frysk,Français,Sranan
3 2017-12 2 1 Frysk,Nederlands
4 2018-4 2 1 Stadsfrys,Nederlands
5 2018-11 2 1 Gronings,Nederlands

The device ids:

In [7]:
pandas.read_sql('''
    SELECT
        DISTINCT survey.device_id
    FROM core_freespeechresult AS result
    LEFT JOIN core_language as lang
      ON lang.id = result.language_id
    LEFT JOIN core_surveyresult as survey
      ON survey.id = result.survey_result_id
    WHERE result.submitted_at > "2017-09-19"
    AND result.survey_result_id IN (
        SELECT result.survey_result_id
        FROM core_freespeechresult AS result
        WHERE result.submitted_at > "2017-09-19"
        GROUP BY result.survey_result_id
        HAVING COUNT(DISTINCT result.language_id) > 1
    )
''', db)
Out[7]:
device_id
0 738191E5-80E4-4D07-A15E-6B7DE881906A
1 fb28928ef1703592
2 820E203F-1B70-40A8-BD47-04681D07BC9C
3 1aa95d23fddf3827
4 F95F07AE-B524-4256-9171-F7FFA9E49156
5 C8F56303-E912-4BC3-AD51-E1CA8B425AE9
6 B653C16D-BDA8-4A47-BC6A-C3171D89AF71
7 b40fb5e73d431e6b
8 C9FD6142-A64A-4AD8-B317-3BB833A11667
9 A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D
10 8190723c74b4b0bf

The results:

In [8]:
pandas.read_sql('''
    SELECT
        survey.device_id,
        lang.name as language,
        result.*
    FROM core_freespeechresult AS result
    LEFT JOIN core_language as lang
      ON lang.id = result.language_id
    LEFT JOIN core_surveyresult as survey
      ON survey.id = result.survey_result_id
    WHERE result.submitted_at > "2017-09-19"
    AND result.survey_result_id IN (
        SELECT result.survey_result_id
        FROM core_freespeechresult AS result
        WHERE submitted_at > "2017-09-19"
        GROUP BY result.survey_result_id
        HAVING COUNT(DISTINCT result.language_id) > 1
    )
''', db)
Out[8]:
device_id language id session_id app_locale submitted_at theme_short_text theme_long_text recording language_id speech_theme_id survey_result_id theme_long_text_en theme_long_text_fy theme_long_text_nl theme_short_text_en theme_short_text_fy theme_short_text_nl
0 738191E5-80E4-4D07-A15E-6B7DE881906A Stadsfrys 159 6349CCA2-358F-4AE5-821F-543AD0B9C97A fy 2017-09-20 21:05:47 What is your word for... What is the word for "potato" in your local la... free_speech_recordings/Stadsfrys/2017-09-20/fs... 14 9 1539 What is the word for "potato" in your local la... Wat is it wurd foar "ierappel" yn dyn taal? Wat is het woord voor "aardappel" in je taal? What is your word for... Wat is dyn wurd foar... Wat is je woord voor...
1 738191E5-80E4-4D07-A15E-6B7DE881906A Stadsfrys 160 ACEF45B4-3162-4A5F-B83A-3E39BE140117 fy 2017-09-20 21:09:51 Your own story Tell us your own story here free_speech_recordings/Stadsfrys/2017-09-20/fs... 14 3 1539 Tell us your own story here Fertel dyn eigen ferhaal hjir Vertel je eigen verhaal hier Your own story Dyn eigen ferhaal Je eigen verhaal
2 738191E5-80E4-4D07-A15E-6B7DE881906A Frysk 161 ACEF45B4-3162-4A5F-B83A-3E39BE140117 fy 2017-09-20 21:15:01 Read a text 3 Then the sun began to shine, and the traveler ... free_speech_recordings/Frysk/2017-09-20/fs_rea... 1 8 1539 Then the sun began to shine, and the traveler ... Dêrnei begûn de sinne fûl te skinen en fuorten... Daarna begon de zon krachtig te stralen, en hi... Read a text 3 Foarlêze 3 Voorlezen 3
3 fb28928ef1703592 Frysk 221 androidSessionIdfb28928ef170359258404890608067 nl 2017-09-23 15:05:05 What is your word for... What is the word for "potato" in your local la... free_speech_recordings/Frysk/2017-09-23/fs_wha... 1 9 2951 What is the word for "potato" in your local la... Wat is it wurd foar "ierappel" yn dyn taal? Wat is het woord voor "aardappel" in je taal? What is your word for... Wat is dyn wurd foar... Wat is je woord voor...
4 fb28928ef1703592 Frysk 222 androidSessionIdfb28928ef170359258618421150746 nl 2017-09-23 15:08:40 Growing up Tell us a story about where you grew up? free_speech_recordings/Frysk/2017-09-23/fs_gro... 1 5 2951 Tell us a story about where you grew up? Fertel wat oer wêrst wei komst Vertel iets over waar je bent opgegroeid? Growing up Opgroeie Opgroeien
5 fb28928ef1703592 Nederlands 223 androidSessionIdfb28928ef170359258714896361595 nl 2017-09-23 15:10:16 Your own story Tell us your own story here free_speech_recordings/Nederlands/2017-09-23/f... 3 3 2951 Tell us your own story here Fertel dyn eigen ferhaal hjir Vertel je eigen verhaal hier Your own story Dyn eigen ferhaal Je eigen verhaal
6 fb28928ef1703592 English 224 androidSessionIdfb28928ef170359259137367204559 nl 2017-09-23 15:17:19 Your own story Tell us your own story here free_speech_recordings/English/2017-09-23/fs_y... 2 3 2951 Tell us your own story here Fertel dyn eigen ferhaal hjir Vertel je eigen verhaal hier Your own story Dyn eigen ferhaal Je eigen verhaal
7 820E203F-1B70-40A8-BD47-04681D07BC9C Nederlands 333 28F45D4F-71F4-45A3-A9A2-B9813A16EC13 nl 2017-10-12 08:49:53 What is your word for... What is the word for "potato" in your local la... free_speech_recordings/Nederlands/2017-10-12/f... 3 9 5477 What is the word for "potato" in your local la... Wat is it wurd foar "ierappel" yn dyn taal? Wat is het woord voor "aardappel" in je taal? What is your word for... Wat is dyn wurd foar... Wat is je woord voor...
8 820E203F-1B70-40A8-BD47-04681D07BC9C Nederlands 334 28F45D4F-71F4-45A3-A9A2-B9813A16EC13 nl 2017-10-12 08:50:49 What would you do with a million euros? What would you do with a million euros? free_speech_recordings/Nederlands/2017-10-12/f... 3 4 5477 What would you do with a million euros? Wat soesto dwaan mei €1.000.000? Wat zou je doen met €1.000.000? What would you do with a million euros? Wat soesto dwaan mei €1.000.000? Wat zou je doen met €1.000.000?
9 820E203F-1B70-40A8-BD47-04681D07BC9C Frysk 335 28F45D4F-71F4-45A3-A9A2-B9813A16EC13 nl 2017-10-12 08:51:51 Your own story Tell us your own story here free_speech_recordings/Frysk/2017-10-12/fs_you... 1 3 5477 Tell us your own story here Fertel dyn eigen ferhaal hjir Vertel je eigen verhaal hier Your own story Dyn eigen ferhaal Je eigen verhaal
10 1aa95d23fddf3827 Frysk 373 androidSessionId1aa95d23fddf3827591709392581151 fy 2017-10-26 11:10:42 What is your word for... What is the word for "potato" in your local la... free_speech_recordings/Frysk/2017-10-26/fs_wha... 1 9 6135 What is the word for "potato" in your local la... Wat is it wurd foar "ierappel" yn dyn taal? Wat is het woord voor "aardappel" in je taal? What is your word for... Wat is dyn wurd foar... Wat is je woord voor...
11 1aa95d23fddf3827 Frysk 374 androidSessionId1aa95d23fddf3827591856351721095 fy 2017-10-26 11:13:08 Read a text 3 Then the sun began to shine, and the traveler ... free_speech_recordings/Frysk/2017-10-26/fs_rea... 1 8 6135 Then the sun began to shine, and the traveler ... Dêrnei begûn de sinne fûl te skinen en fuorten... Daarna begon de zon krachtig te stralen, en hi... Read a text 3 Foarlêze 3 Voorlezen 3
12 1aa95d23fddf3827 Hielpers 375 androidSessionId1aa95d23fddf3827592248608182404 fy 2017-10-26 11:19:40 Read a text 3 Then the sun began to shine, and the traveler ... free_speech_recordings/Hielpers/2017-10-26/fs_... 9 8 6135 Then the sun began to shine, and the traveler ... Dêrnei begûn de sinne fûl te skinen en fuorten... Daarna begon de zon krachtig te stralen, en hi... Read a text 3 Foarlêze 3 Voorlezen 3
13 1aa95d23fddf3827 Frysk 376 androidSessionId1aa95d23fddf3827592854579719673 fy 2017-10-26 11:29:47 Growing up Tell us a story about where you grew up? free_speech_recordings/Frysk/2017-10-26/fs_gro... 1 5 6135 Tell us a story about where you grew up? Fertel wat oer wêrst wei komst Vertel iets over waar je bent opgegroeid? Growing up Opgroeie Opgroeien
14 F95F07AE-B524-4256-9171-F7FFA9E49156 Frysk 388 70BBF265-BB3B-4B53-8C1E-9B6CA179906C fy 2017-11-05 11:44:03 Growing up Tell us a story about where you grew up? free_speech_recordings/Frysk/2017-11-05/fs_gro... 1 5 6255 Tell us a story about where you grew up? Fertel wat oer wêrst wei komst Vertel iets over waar je bent opgegroeid? Growing up Opgroeie Opgroeien
15 F95F07AE-B524-4256-9171-F7FFA9E49156 Frysk 389 70BBF265-BB3B-4B53-8C1E-9B6CA179906C fy 2017-11-05 11:48:01 Read a text 2 The North Wind blew as hard as he could, but t... free_speech_recordings/Frysk/2017-11-05/fs_rea... 1 7 6255 The North Wind blew as hard as he could, but t... De noardewyn begûn út alle macht te blazen, ma... De noordenwind begon toen uit alle macht te bl... Read a text 2 Foarlêze 2 Voorlezen 2
16 F95F07AE-B524-4256-9171-F7FFA9E49156 Frysk 390 70BBF265-BB3B-4B53-8C1E-9B6CA179906C fy 2017-11-05 11:48:56 What is your word for... What is the word for "potato" in your local la... free_speech_recordings/Frysk/2017-11-05/fs_wha... 1 9 6255 What is the word for "potato" in your local la... Wat is it wurd foar "ierappel" yn dyn taal? Wat is het woord voor "aardappel" in je taal? What is your word for... Wat is dyn wurd foar... Wat is je woord voor...
17 F95F07AE-B524-4256-9171-F7FFA9E49156 Sranan 391 70BBF265-BB3B-4B53-8C1E-9B6CA179906C fy 2017-11-05 11:52:51 Growing up Tell us a story about where you grew up? free_speech_recordings/Sranan/2017-11-05/fs_gr... 16 5 6255 Tell us a story about where you grew up? Fertel wat oer wêrst wei komst Vertel iets over waar je bent opgegroeid? Growing up Opgroeie Opgroeien
18 C8F56303-E912-4BC3-AD51-E1CA8B425AE9 Frysk 410 0D2F2AD3-177A-4991-AD5F-55CAF1BFDE35 nl 2017-11-16 13:15:06 What is your word for... What is the word for "potato" in your local la... free_speech_recordings/Frysk/2017-11-16/fs_wha... 1 9 6445 What is the word for "potato" in your local la... Wat is it wurd foar "ierappel" yn dyn taal? Wat is het woord voor "aardappel" in je taal? What is your word for... Wat is dyn wurd foar... Wat is je woord voor...
19 B653C16D-BDA8-4A47-BC6A-C3171D89AF71 Frysk 411 07E90580-694C-4F9C-A82B-D431D88F6508 nl 2017-11-16 13:16:32 What is your word for... What is the word for "potato" in your local la... free_speech_recordings/Frysk/2017-11-16/fs_wha... 1 9 6450 What is the word for "potato" in your local la... Wat is it wurd foar "ierappel" yn dyn taal? Wat is het woord voor "aardappel" in je taal? What is your word for... Wat is dyn wurd foar... Wat is je woord voor...
20 B653C16D-BDA8-4A47-BC6A-C3171D89AF71 Nederlands 412 4CB961F7-432A-43EE-B748-41CB18DA6ACC nl 2017-11-16 13:20:38 Joke Tell us a joke! free_speech_recordings/Nederlands/2017-11-16/f... 3 2 6450 Tell us a joke! Fertel in mopke! Vertel een mop! Joke Mopke Mop
21 C8F56303-E912-4BC3-AD51-E1CA8B425AE9 Nederlands 414 0D2F2AD3-177A-4991-AD5F-55CAF1BFDE35 nl 2017-11-16 13:21:42 What is your word for... What is the word for "potato" in your local la... free_speech_recordings/Nederlands/2017-11-16/f... 3 9 6445 What is the word for "potato" in your local la... Wat is it wurd foar "ierappel" yn dyn taal? Wat is het woord voor "aardappel" in je taal? What is your word for... Wat is dyn wurd foar... Wat is je woord voor...
22 b40fb5e73d431e6b Frysk 426 androidSessionIdb40fb5e73d431e6b136597419729297 nl 2017-11-22 13:14:36 What is your word for... What is the word for "potato" in your local la... free_speech_recordings/Frysk/2017-11-22/fs_wha... 1 9 6496 What is the word for "potato" in your local la... Wat is it wurd foar "ierappel" yn dyn taal? Wat is het woord voor "aardappel" in je taal? What is your word for... Wat is dyn wurd foar... Wat is je woord voor...
23 b40fb5e73d431e6b Français 428 androidSessionIdb40fb5e73d431e6b136976454194882 nl 2017-11-22 13:20:56 Your own story Tell us your own story here free_speech_recordings/Français/2017-11-22/fs_... 22 3 6496 Tell us your own story here Fertel dyn eigen ferhaal hjir Vertel je eigen verhaal hier Your own story Dyn eigen ferhaal Je eigen verhaal
24 C9FD6142-A64A-4AD8-B317-3BB833A11667 Nederlands 452 9A976F40-ADF7-4F72-9C2F-9BD4F1AF4DF5 nl 2017-12-12 10:36:40 What would you do with a million euros? What would you do with a million euros? free_speech_recordings/Nederlands/2017-12-12/f... 3 4 6892 What would you do with a million euros? Wat soesto dwaan mei €1.000.000? Wat zou je doen met €1.000.000? What would you do with a million euros? Wat soesto dwaan mei €1.000.000? Wat zou je doen met €1.000.000?
25 C9FD6142-A64A-4AD8-B317-3BB833A11667 Frysk 453 372127E4-AFFE-4BF5-BE7A-FF9D981F4D2E nl 2017-12-12 10:42:33 What is your word for... What is the word for "potato" in your local la... free_speech_recordings/Frysk/2017-12-12/fs_wha... 1 9 6892 What is the word for "potato" in your local la... Wat is it wurd foar "ierappel" yn dyn taal? Wat is het woord voor "aardappel" in je taal? What is your word for... Wat is dyn wurd foar... Wat is je woord voor...
26 A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D Nederlands 476 63B8E61A-433A-433A-924F-0B2D9D6F0353 fy 2018-04-08 17:37:12 What is your word for... What is the word for "potato" in your local la... free_speech_recordings/Nederlands/2018-04-08/f... 3 9 7372 What is the word for "potato" in your local la... Wat is it wurd foar "ierappel" yn dyn taal? Wat is het woord voor "aardappel" in je taal? What is your word for... Wat is dyn wurd foar... Wat is je woord voor...
27 A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D Stadsfrys 477 63B8E61A-433A-433A-924F-0B2D9D6F0353 fy 2018-04-08 17:46:49 What would you do with a million euros? What would you do with a million euros? free_speech_recordings/Stadsfrys/2018-04-08/fs... 14 4 7372 What would you do with a million euros? Wat soesto dwaan mei €1.000.000? Wat zou je doen met €1.000.000? What would you do with a million euros? Wat soesto dwaan mei €1.000.000? Wat zou je doen met €1.000.000?
28 8190723c74b4b0bf Nederlands 500 androidSessionId8190723c74b4b0bf57887798554061 nl 2018-11-18 11:03:50 Growing up Tell us a story about where you grew up? free_speech_recordings/Nederlands/2018-11-18/f... 3 5 8193 Tell us a story about where you grew up? Fertel wat oer wêrst wei komst Vertel iets over waar je bent opgegroeid? Growing up Opgroeie Opgroeien
29 8190723c74b4b0bf Gronings 501 androidSessionId8190723c74b4b0bf58065148631545 nl 2018-11-18 11:06:50 Growing up Tell us a story about where you grew up? free_speech_recordings/Gronings/2018-11-18/fs_... 4 5 8193 Tell us a story about where you grew up? Fertel wat oer wêrst wei komst Vertel iets over waar je bent opgegroeid? Growing up Opgroeie Opgroeien
In [9]:
allcounts = dict()
tables = ['core_freespeechresult', 'core_picturegameresult', 'core_predictionquizresult', 'core_surveyresult']
for table in tables:
    counts = pandas.read_sql('''
        SELECT
            CONCAT(YEAR(result.submitted_at), '-', MONTH(result.submitted_at)) AS 'year-month',
            COUNT(*) AS '# games'
        FROM {table} AS result
        WHERE submitted_at > "2017-09-19"
        GROUP BY YEAR(result.submitted_at), MONTH(result.submitted_at)
    '''.format(table=table), db)
    allcounts[table] = dict(zip(
        ['-'.join('{:02d}'.format(int(d)) for d in date.split('-')) for date in counts['year-month']],
        counts['# games']
    ))
    
dates = sorted({
     date for dates in allcounts.values() for date in dates
})
make_table([[''] + tables] + [
    [date] + [allcounts[table].get(date, 0) for table in tables]
    for date in dates
])
Out[9]:
core_freespeechresultcore_picturegameresultcore_predictionquizresultcore_surveyresult
2017-091722141688374413
2017-1078928623641307
2017-11513586701464
2017-12321642997466
2018-01238710146
2018-0211478328
2018-03030413953
2018-04121851524294
2018-050563327127
2018-063268173112
2018-07011814897
2018-081194215114
2018-09011611747
2018-10720010790
2018-11157366759
2018-122736627
2019-015624137100
2019-020634924
2019-030812513
In [10]:
make_table([
    [
        table,
        pandas.read_sql('''
            SELECT MAX(submitted_at) FROM {table}
        '''.format(table=table), db).values[0, 0],
        pandas.read_sql('''
            SELECT COUNT(*) FROM {table}
        '''.format(table=table), db).values[0, 0]
    ]
    for table in pandas.read_sql('''
        SHOW TABLES
    ''', db).values[:, 0]
    if 'submitted_at' in set(pandas.read_sql('''
            DESCRIBE {table}
        '''.format(table=table), db)['Field'])
])
Out[10]:
core_freespeechresult2019-01-30T19:40:03.000000000435
core_picturegameresult2019-03-09T00:59:58.00000000047672
core_predictionquizresult2019-03-13T04:21:56.00000000015522
core_surveyresult2019-03-12T14:48:58.0000000008321