170 KiB
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 | ||
question | answer | count |
Total participants | 15177 | |
No survey | 11829 | |
With survey | 3348 | |
What is your age bracket? | total answers | 3348 |
What is your gender? | total answers | 3348 |
Which language are you the most proficient in? | total answers | 3348 |
Which languages do you actively use in your life? | total answers | 7848 |
What is your age bracket? | 11-20 | 1064 |
21-30 | 748 | |
41-50 | 424 | |
51-60 | 414 | |
31-40 | 352 | |
61-70 | 239 | |
71-80 | 66 | |
81-90 | 18 | |
0-10 | 16 | |
91-100 | 7 | |
What is your gender? | female | 1689 |
male | 1640 | |
other | 19 | |
Which language are you the most proficient in? | Frisian | 1740 |
Dutch | 1487 | |
Bildts | 33 | |
Stadsfrys | 33 | |
English | 17 | |
Other | 10 | |
German | 5 | |
Amelands | 4 | |
Gronings | 3 | |
Stellingwarfs | 3 | |
Hindeloopers | 2 | |
North Frisian | 2 | |
Ostfriesisches Platt | 2 | |
Turkish | 2 | |
Arabic | 1 | |
Drents | 1 | |
Mandarin | 1 | |
Sranan | 1 | |
Terschellings | 1 | |
Which languages do you actively use in your life? | Dutch | 2867 |
Frisian | 2787 | |
English | 1169 | |
German | 335 | |
City Frisian | 173 | |
French | 126 | |
Other | 93 | |
Bildts | 72 | |
Gronings | 31 | |
Stellingwarfs | 26 | |
Norwegian | 22 | |
Amelands | 20 | |
Hindeloopers | 13 | |
Drents | 12 | |
Arabic | 9 | |
Terschellings | 9 | |
Bahasa Indonesia | 8 | |
Afrikaans | 7 | |
Sranan | 7 | |
Papiamentu | 6 | |
Tarifit | 6 | |
Turkish | 6 | |
Mandarin Chinese | 5 | |
North Frisian | 5 | |
Ostfriesisches Platt | 5 | |
Polish | 5 | |
Niedersächsisch | 4 | |
Schiermonnikoogs | 4 | |
Galician | 3 | |
Ostniederdeutsch | 3 | |
Latgalīšu volūda | 2 | |
Latviešu valoda | 2 | |
Ostfälisch | 2 | |
Sarnami Hindoestani | 2 | |
Westfälisch | 2 |
picture game | ||
question | answer | count |
Total participants | 41655 | |
No survey | 0 | |
With survey | 41655 | |
What is your age bracket? | total answers | 41655 |
What is your gender? | total answers | 41655 |
Which language are you the most proficient in? | total answers | 41655 |
Which languages do you actively use in your life? | total answers | 103303 |
What is your age bracket? | 21-30 | 10255 |
11-20 | 9756 | |
51-60 | 6126 | |
41-50 | 5853 | |
31-40 | 5661 | |
61-70 | 2679 | |
71-80 | 1017 | |
91-100 | 146 | |
0-10 | 134 | |
81-90 | 28 | |
What is your gender? | female | 24296 |
male | 17047 | |
other | 312 | |
Which language are you the most proficient in? | Frisian | 20755 |
Dutch | 17878 | |
English | 993 | |
Other | 702 | |
Stadsfrys | 449 | |
Bildts | 419 | |
German | 235 | |
Gronings | 40 | |
Amelands | 31 | |
Drents | 28 | |
North Frisian | 27 | |
Turkish | 21 | |
Galician | 15 | |
Hindeloopers | 14 | |
Stellingwarfs | 14 | |
Arabic | 13 | |
Terschellings | 11 | |
Papiamentu | 10 | |
Which languages do you actively use in your life? | Dutch | 36236 |
Frisian | 32716 | |
English | 17498 | |
German | 5727 | |
Other | 2769 | |
City Frisian | 2350 | |
French | 2145 | |
Bildts | 903 | |
Norwegian | 819 | |
Gronings | 321 | |
Stellingwarfs | 286 | |
Amelands | 263 | |
Drents | 207 | |
Bahasa Indonesia | 157 | |
North Frisian | 125 | |
Tarifit | 125 | |
Sranan | 120 | |
Polish | 117 | |
Niedersächsisch | 99 | |
Terschellings | 85 | |
Schiermonnikoogs | 39 | |
Afrikaans | 30 | |
Hindeloopers | 30 | |
Mandarin Chinese | 26 | |
Arabic | 22 | |
Turkish | 21 | |
Ostniederdeutsch | 19 | |
Papiamentu | 14 | |
Sarnami Hindoestani | 10 | |
Westfälisch | 10 | |
Latviešu valoda | 9 | |
Ostfriesisches Platt | 5 |
free speech | ||
question | answer | count |
Total participants | 381 | |
No survey | 0 | |
With survey | 381 | |
What is your age bracket? | total answers | 381 |
What is your gender? | total answers | 381 |
Which language are you the most proficient in? | total answers | 381 |
Which languages do you actively use in your life? | total answers | 953 |
What is your age bracket? | 11-20 | 116 |
21-30 | 86 | |
31-40 | 51 | |
41-50 | 42 | |
51-60 | 39 | |
61-70 | 18 | |
71-80 | 12 | |
0-10 | 7 | |
91-100 | 6 | |
81-90 | 4 | |
What is your gender? | male | 198 |
female | 169 | |
other | 14 | |
Which language are you the most proficient in? | Dutch | 177 |
Frisian | 152 | |
English | 17 | |
Other | 14 | |
German | 6 | |
Bildts | 3 | |
Stadsfrys | 3 | |
Afrikaans | 2 | |
North Frisian | 2 | |
Amelands | 1 | |
Mandarin | 1 | |
Ostfriesisches Platt | 1 | |
Sarnami Hindoestani | 1 | |
Turkish | 1 | |
Which languages do you actively use in your life? | Dutch | 303 |
Frisian | 263 | |
English | 169 | |
German | 46 | |
Other | 29 | |
City Frisian | 24 | |
French | 20 | |
Gronings | 10 | |
Bahasa Indonesia | 7 | |
Norwegian | 7 | |
Bildts | 6 | |
Sranan | 6 | |
Drents | 5 | |
Polish | 5 | |
Tarifit | 5 | |
Afrikaans | 4 | |
Amelands | 4 | |
Arabic | 4 | |
Hindeloopers | 4 | |
Schiermonnikoogs | 4 | |
Stellingwarfs | 4 | |
Turkish | 4 | |
Niedersächsisch | 3 | |
Sarnami Hindoestani | 3 | |
Terschellings | 3 | |
Galician | 2 | |
Mandarin Chinese | 2 | |
Westfälisch | 2 | |
North Frisian | 1 | |
Ostfriesisches Platt | 1 | |
Ostfälisch | 1 | |
Ostniederdeutsch | 1 | |
Papiamentu | 1 |
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_freespeechresult | core_picturegameresult | core_predictionquizresult | core_surveyresult | |
2017-09 | 172 | 21416 | 8837 | 4413 |
2017-10 | 78 | 9286 | 2364 | 1307 |
2017-11 | 51 | 3586 | 701 | 464 |
2017-12 | 32 | 1642 | 997 | 466 |
2018-01 | 2 | 387 | 101 | 46 |
2018-02 | 1 | 147 | 83 | 28 |
2018-03 | 0 | 304 | 139 | 53 |
2018-04 | 12 | 1851 | 524 | 294 |
2018-05 | 0 | 563 | 327 | 127 |
2018-06 | 3 | 268 | 173 | 112 |
2018-07 | 0 | 118 | 148 | 97 |
2018-08 | 1 | 194 | 215 | 114 |
2018-09 | 0 | 116 | 117 | 47 |
2018-10 | 7 | 200 | 107 | 90 |
2018-11 | 15 | 736 | 67 | 59 |
2018-12 | 2 | 73 | 66 | 27 |
2019-01 | 5 | 624 | 137 | 100 |
2019-02 | 0 | 63 | 49 | 24 |
2019-03 | 0 | 81 | 25 | 13 |
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_freespeechresult | 2019-01-30T19:40:03.000000000 | 435 |
core_picturegameresult | 2019-03-09T00:59:58.000000000 | 47672 |
core_predictionquizresult | 2019-03-13T04:21:56.000000000 | 15522 |
core_surveyresult | 2019-03-12T14:48:58.000000000 | 8321 |