102 KiB
102 KiB
In [2]:
# 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 [10]:
pandas.read_sql(''' DESCRIBE core_predictionquizresultquestionanswer ''', db)
Out[10]:
| Field | Type | Null | Key | Default | Extra | |
|---|---|---|---|---|---|---|
| 0 | id | int(11) | NO | PRI | None | auto_increment |
| 1 | question_text | varchar(255) | NO | None | ||
| 2 | answer_text | varchar(255) | NO | None | ||
| 3 | prediction_quiz_id | int(11) | NO | MUL | None |
In [13]:
pronunciations
Out[13]:
defaultdict(list,
{'"armen" (*lichaamsdeel)': ['a____n (ɑRmən)',
'a____n (ɑɾmən)',
'a____s (ɑɾms)',
'e____n (e:ɾmən)',
'e____n (ɛɾmən)',
'e____s (ɛ:ɾms)',
'g___ (giən)',
'j____n (jɛɾmən)',
'j____s (jɛɾms)',
'j____s (jɛɾməns)',
's_____ (sa:tədi)',
'____s (kɪ:s)'],
'"avond"': ['a__nd (a:vənt)',
'a___n (ɑʋən)',
'i___n (ɪwən)',
'i___nd (ɪ.ənt)',
'j__n (jun)',
'j__ne (junə)',
'j___n (joun)',
'j___ne (jaunə)',
'j___ne (jɔ:nə)',
'o___nd (ɒ:bn̩t)',
's____n (snɪwn)',
'____s (tsi:s)'],
'"bij" (*insect)': ['b__st (bwɑst)',
'b___ (be:i)',
'b___ (bi:)',
'b___ (bæi)',
'b___ (bɛi)',
'b____ (bɛiə)',
'h___k (hork)',
'h______r (hy:nəxauər)',
'h_______r (hɵnəɣowər)',
'i____ (imə)',
'i_______r (i:nəfandər)',
's____n (snɪwn)',
'‘n lid d__r (da:ɾ)'],
'"blad" (aan een boom)': ['b___ (blet)',
'b___ (blɔd)',
'b___ (blɛ:t)',
'b___ (blɑt)'],
'"borst" (*lichaamsdeel)': ['a____n (ɑRmən)',
'b__st (bost)',
'b__st (bwɑst)',
'b__st (bɔɾst)',
'b__st (bɵst)',
'b__st (bɒst)',
'b__st (bɪast)',
'b__st (bɶst)',
'b___t (bɔʃt)'],
'"dag"': ['d__i (dɛɪ)',
'd__i (dɑɪ)',
'd___ (dɑ:x)',
'd___ (dɑx)',
'h___l (hɪəl)'],
'"deurtje"': ['d__i (dɑɪ)',
'd____e (do:ɾtjə)',
'd____e (doətjə)',
'd____e (dwɑɾkə)',
'd____e (dø:ɾtjə)',
'd____e (dɔ:ɾkə)',
'd____e (dɵ:ɾkə)',
'd____e (dɵʁkə)',
'd____e (dɶɾkə)',
'd_____ (dɵ:tin)',
'd_____ (dɵ:tsi)',
'd_____ (dɶɾti)',
'____s (keɪs)',
'____s (kɪ:s)',
'‘n lid d__r (da:ɾ)'],
'"geel"': ['___l (ɣe:l)',
'___l (ɣiəl)',
'___l (ɡe:l)',
'___l (ɡeɪl)',
'___l (ɡɪəl)'],
'"gegaan"': ['d____e (doətjə)',
'g___ (ga:n)',
'g___ (giən)',
'g___ (giənt)',
'g___ (gonən)',
'g___ (gɔ:n)',
'g___ (gɒnt)',
'g___ (xɔn)',
'g___ (xəxa:n)',
'g___ (xɑ:n)',
'g___ (ɣiən)',
'____s (keɪs)'],
'"gezet"': ['a____n (ɑRmən)',
'sp___s (spɾɵts)',
'___n (sɛdn)',
'___n (sɛtn)',
'___t (sɛt)',
'___t (sɒt)',
'___t (sʌt)',
'___t (xəsɛt)',
'___t (ɡəsət)'],
'"heel"': ['e____n (e:ɾmən)',
'h___l (he:l)',
'h___l (heɪl)',
'h___l (hi:l)',
'h___l (hø:l)',
'h___l (hɪəl)'],
'"kaas"': ['k___e (kʌtjə)',
'k___s (ka:s)',
'____e (kezə)',
'____s (keɪs)',
'____s (kɪ:s)',
'____s (kɪ:əs)',
'____s (seɪs)',
'____s (si:s)',
'____s (tsi:s)'],
'"koken"': ['k___ (ko:kən)',
'k___ (kouʔn)',
'k___e (kɔkjə)',
'k___e (kɒ:kjə)',
'k___e (kʌtjə)',
'k____e (kojtsə)',
'k____e (koukə)',
's___e (sɪədə)',
's___e (sɪəɾə)',
'ts___e (tsɪədə)',
't___e (tʊskə)',
'___l (ɣiəl)'],
'"oog"': ['o___ (o:x)',
'o___ (oɣə)',
'___ch (aɪx)',
'___ch (eɪx)',
'___ch (ɛ.ɪx)',
'___ch (ɛ:x)',
'___ch (ɪ.əx)'],
'"sprak (toe)"': ['sp__k (spɾi:k)',
'sp__k (spɾɑk)',
'sp___e (spɾɛktə)',
'sp___e (spɾɑktə)',
'sp___k (spRɑk)',
'sp___k (spɾeɪk)',
'sp___k (spɾøk)',
'sp___s (spɾɵts)',
'____s (tsi:s)'],
'"tand"': ['b__st (bɒst)',
'b___ (blɛ:t)',
'k___e (ki:zə)',
't___d (tɑnt)',
't___e (ta:nə)',
't___e (tʊskə)',
't___k (tɵsk)',
't___n (tɑ:n)',
't___s (tos)',
't___sk (tosk)'],
'"trein"': ['t____n (tRɛɪn)',
't____n (tɾeɪn)',
't____n (tɾɔɪn)',
't____n (tɾɛɪn)',
't____n (tɾɑɪn)',
'‘n lid d__r (da:ɾ)'],
'"vis"': ['t____n (tɾeɪn)', '___s (fɪs)', '___sk (fisk)'],
'"zaterdag"': ['d__i (dɑɪ)',
's____n (snjoun)',
's____n (snɵən)',
's____n (snɪwn)',
's_____ (sa:tədex)',
's_____ (sa:tədi)',
's_____ (sa:tədɑx)',
's_____ (snɪət)',
's_____ (sɑ:təɾjə)',
'____s (tsi:s)'],
'avond': ['a____n (ɑʋən)',
'a____nd (a:vənt)',
'i____n (ɪwən)',
'i____nd (ɪ.ənt)',
'j___ne (jɔ:nə)',
'j____n (joun)',
'j____n (jun)',
'j____ne (jaunə)',
'j____ne (junə)',
'o____nd (ɒ:bn̩t)'],
'bij': ['b____ (be:i)',
'b____ (bi:)',
'b____ (bæi)',
'b____ (bɛi)',
'b____ (bɛiə)',
'h_____k (hork)',
'h_________r (hy:nəxauər)',
'h_________r (hɵnəɣowər)',
'i____ (imə)',
'i__________r (i:nəfandər)'],
'bij (*insect)': ['b____ (be:i)',
'b____ (bi:)',
'b____ (bæi)',
'b____ (bɛi)',
'b____ (bɛiə)',
'h_____k (hork)',
'h_________r (hy:nəxauər)',
'h_________r (hɵnəɣowər)',
'i____ (imə)',
'i__________r (i:nəfandər)'],
'blad': ['b___ (blet)',
'b___ (blɔd)',
'b___ (blɛ:t)',
'b___ (blɑt)'],
'blad (aan een boom)': ['b___ (blet)',
'b___ (blɔd)',
'b___ (blɛ:t)',
'b___ (blɑt)'],
'borst': ['b__st (bost)',
'b__st (bwɑst)',
'b__st (bɔɾst)',
'b__st (bɵst)',
'b__st (bɒst)',
'b__st (bɪast)',
'b__st (bɶst)',
'b___t (bɔʃt)'],
'borst (*lichaamsdeel)': ['b__st (bost)',
'b__st (bwɑst)',
'b__st (bɔɾst)',
'b__st (bɵst)',
'b__st (bɒst)',
'b__st (bɪast)',
'b__st (bɶst)',
'b___t (bɔʃt)'],
'dag': ['d__i (dɛɪ)', 'd__i (dɑɪ)', 'd___ (dɑ:x)', 'd___ (dɑx)'],
'deurtje': ['d____e (do:ɾtjə)',
'd____e (doətjə)',
'd____e (dwɑɾkə)',
'd____e (dø:ɾtjə)',
'd____e (dɔ:ɾkə)',
'd____e (dɵ:ɾkə)',
'd____e (dɵʁkə)',
'd____e (dɶɾkə)',
'd_____ (dɵ:tin)',
'd_____ (dɵ:tsi)',
'd_____ (dɶɾti)',
'‘n lid d__r (da:ɾ)'],
'geel': ['___l (ɣe:l)',
'___l (ɣiəl)',
'___l (ɡe:l)',
'___l (ɡeɪl)',
'___l (ɡɪəl)'],
'heel': ['h___l (he:l)',
'h___l (heɪl)',
'h___l (hi:l)',
'h___l (hø:l)',
'h___l (hɪəl)'],
'ik ben gegaan': ['g___ (ga:n)',
'g___ (giən)',
'g___ (giənt)',
'g___ (gonən)',
'g___ (gɔ:n)',
'g___ (gɒnt)',
'g___ (xɔn)',
'g___ (xəxa:n)',
'g___ (xɑ:n)',
'g___ (ɣiən)'],
'ik heb gezet': ['___n (sɛdn)',
'___n (sɛtn)',
'___t (sɛt)',
'___t (sɒt)',
'___t (sʌt)',
'___t (xəsɛt)',
'___t (ɡəsət)'],
'ik sprak': ['sp__e (spɾɛktə)',
'sp__e (spɾɑktə)',
'sp__k (spRɑk)',
'sp__k (spɾeɪk)',
'sp__k (spɾi:k)',
'sp__k (spɾøk)',
'sp__k (spɾɑk)',
'sp__s (spɾɵts)'],
'ik sprak (toe)': ['sp__e (spɾɛktə)',
'sp__e (spɾɑktə)',
'sp__k (spRɑk)',
'sp__k (spɾeɪk)',
'sp__k (spɾi:k)',
'sp__k (spɾøk)',
'sp__k (spɾɑk)',
'sp__s (spɾɵts)'],
'kaas': ['k___s (ka:s)',
'____e (kezə)',
'____s (keɪs)',
'____s (kɪ:s)',
'____s (kɪ:əs)',
'____s (seɪs)',
'____s (si:s)',
'____s (tsi:s)'],
'koken': ['k__e (kʌtjə)',
'k___ (ko:kən)',
'k___ (kouʔn)',
'k___e (kojtsə)',
'k___e (koukə)',
'k___e (kɔkjə)',
'k___e (kɒ:kjə)',
's__e (sɪədə)',
's__e (sɪəɾə)',
'ts__e (tsɪədə)'],
'oog': ['o___ (o:x)',
'o___ (oɣə)',
'___ch (aɪx)',
'___ch (eɪx)',
'___ch (ɛ.ɪx)',
'___ch (ɛ:x)',
'___ch (ɪ.əx)'],
'tand': ['k___e (ki:zə)',
't__sk (tosk)',
't___d (tɑnt)',
't___e (ta:nə)',
't___e (tʊskə)',
't___k (tɵsk)',
't___n (tɑ:n)',
't___s (tos)'],
'trein': ['t___n (tRɛɪn)',
't___n (tɾeɪn)',
't___n (tɾɔɪn)',
't___n (tɾɛɪn)',
't___n (tɾɑɪn)'],
'vis': ['___s (fɪs)', '___sk (fisk)'],
'zaterdag': ['s____n (snjoun)',
's____n (snɵən)',
's____n (snɪwn)',
's_____ (sa:tədex)',
's_____ (sa:tədi)',
's_____ (sa:tədɑx)',
's_____ (snɪət)',
's_____ (sɑ:təɾjə)'],
'__armen': ['a____n (ɑRmən)',
'a____n (ɑɾmən)',
'a____s (ɑɾms)',
'e____n (e:ɾmən)',
'e____n (ɛɾmən)',
'e____s (ɛ:ɾms)',
'j____n (jɛɾmən)',
'j____s (jɛɾms)',
'j____s (jɛɾməns)'],
'__armen (*lichaamsdeel)': ['a____n (ɑRmən)',
'a____n (ɑɾmən)',
'a____s (ɑɾms)',
'e____n (e:ɾmən)',
'e____n (ɛɾmən)',
'e____s (ɛ:ɾms)',
'j____n (jɛɾmən)',
'j____s (jɛɾms)',
'j____s (jɛɾməns)']})
In [12]:
from collections import defaultdict pronunciations = defaultdict(list) for _, row in pandas.read_sql(''' SELECT answer.question_text AS question, answer.answer_text AS answer FROM core_predictionquizresult AS quiz JOIN core_predictionquizresultquestionanswer AS answer ON answer.prediction_quiz_id = quiz.id WHERE quiz.submitted_at > '2017-09-19' GROUP BY answer.question_text, answer.answer_text ''', db).iterrows(): pronunciations[row['question']].append(row['answer'])
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 |