p253591
/
stimmenfryslan
1
0
Fork 0
Code Issues Pull Requests Releases Wiki Activity
stimmenfryslan/notebooks/Dangling pronunciations.ipynb

2021 lines
102 KiB
Plaintext
Raw Permalink Normal View History

readme
2019-03-19 13:33:38 +01:00
{
"cells": [
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"········\n"
]
}
],
"source": [
"# Enable portforwording from 3307 locally to 3306 on the stimmen database machine\n",
"# ssh -L 3307:127.0.0.1:3306 stimmen.housing.rug.nl\n",
"\n",
"import pandas\n",
"import MySQLdb\n",
"from collections import defaultdict, Counter\n",
"from ipy_table import make_table, set_row_style\n",
"from IPython.display import display\n",
"\n",
"from getpass import getpass\n",
"\n",
"if 'mysql_password' not in globals():\n",
" mysql_password = getpass()\n",
"\n",
"db = MySQLdb.connect(\n",
" host='127.0.0.1', port=3307,\n",
" user='stimmen', passwd=mysql_password,\n",
" db='stimmen', charset='utf8'\n",
")"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Field</th>\n",
" <th>Type</th>\n",
" <th>Null</th>\n",
" <th>Key</th>\n",
" <th>Default</th>\n",
" <th>Extra</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>id</td>\n",
" <td>int(11)</td>\n",
" <td>NO</td>\n",
" <td>PRI</td>\n",
" <td>None</td>\n",
" <td>auto_increment</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>question_text</td>\n",
" <td>varchar(255)</td>\n",
" <td>NO</td>\n",
" <td></td>\n",
" <td>None</td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>answer_text</td>\n",
" <td>varchar(255)</td>\n",
" <td>NO</td>\n",
" <td></td>\n",
" <td>None</td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>prediction_quiz_id</td>\n",
" <td>int(11)</td>\n",
" <td>NO</td>\n",
" <td>MUL</td>\n",
" <td>None</td>\n",
" <td></td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Field Type Null Key Default Extra\n",
"0 id int(11) NO PRI None auto_increment\n",
"1 question_text varchar(255) NO None \n",
"2 answer_text varchar(255) NO None \n",
"3 prediction_quiz_id int(11) NO MUL None "
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pandas.read_sql('''\n",
" DESCRIBE core_predictionquizresultquestionanswer\n",
"''', db)"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"scrolled": false
},
"outputs": [
{
"data": {
"text/plain": [
"defaultdict(list,\n",
" {'\"armen\" (*lichaamsdeel)': ['a____n (ɑRmən)',\n",
" 'a____n (ɑɾmən)',\n",
" 'a____s (ɑɾms)',\n",
" 'e____n (e:ɾmən)',\n",
" 'e____n (ɛɾmən)',\n",
" 'e____s (ɛ:ɾms)',\n",
" 'g___ (giən)',\n",
" 'j____n (jɛɾmən)',\n",
" 'j____s (jɛɾms)',\n",
" 'j____s (jɛɾməns)',\n",
" 's_____ (sa:tədi)',\n",
" '____s (kɪ:s)'],\n",
" '\"avond\"': ['a__nd (a:vənt)',\n",
" 'a___n (ɑʋən)',\n",
" 'i___n (ɪwən)',\n",
" 'i___nd (ɪ.ənt)',\n",
" 'j__n (jun)',\n",
" 'j__ne (junə)',\n",
" 'j___n (joun)',\n",
" 'j___ne (jaunə)',\n",
" 'j___ne (jɔ:nə)',\n",
" 'o___nd (ɒ:bn̩t)',\n",
" 's____n (snɪwn)',\n",
" '____s (tsi:s)'],\n",
" '\"bij\" (*insect)': ['b__st (bwɑst)',\n",
" 'b___ (be:i)',\n",
" 'b___ (bi:)',\n",
" 'b___ (bæi)',\n",
" 'b___ (bɛi)',\n",
" 'b____ (bɛiə)',\n",
" 'h___k (hork)',\n",
" 'h______r (hy:nəxauər)',\n",
" 'h_______r (hɵnəɣowər)',\n",
" 'i____ (imə)',\n",
" 'i_______r (i:nəfandər)',\n",
" 's____n (snɪwn)',\n",
" '‘n lid d__r (da:ɾ)'],\n",
" '\"blad\" (aan een boom)': ['b___ (blet)',\n",
" 'b___ (blɔd)',\n",
" 'b___ (blɛ:t)',\n",
" 'b___ (blɑt)'],\n",
" '\"borst\" (*lichaamsdeel)': ['a____n (ɑRmən)',\n",
" 'b__st (bost)',\n",
" 'b__st (bwɑst)',\n",
" 'b__st (bɔɾst)',\n",
" 'b__st (bɵst)',\n",
" 'b__st (bɒst)',\n",
" 'b__st (bɪast)',\n",
" 'b__st (bɶst)',\n",
" 'b___t (bɔʃt)'],\n",
" '\"dag\"': ['d__i (dɛɪ)',\n",
" 'd__i (dɑɪ)',\n",
" 'd___ (dɑ:x)',\n",
" 'd___ (dɑx)',\n",
" 'h___l (hɪəl)'],\n",
" '\"deurtje\"': ['d__i (dɑɪ)',\n",
" 'd____e (do:ɾtjə)',\n",
" 'd____e (doətjə)',\n",
" 'd____e (dwɑɾkə)',\n",
" 'd____e (dø:ɾtjə)',\n",
" 'd____e (dɔ:ɾkə)',\n",
" 'd____e (dɵ:ɾkə)',\n",
" 'd____e (dɵʁkə)',\n",
" 'd____e (dɶɾkə)',\n",
" 'd_____ (dɵ:tin)',\n",
" 'd_____ (dɵ:tsi)',\n",
" 'd_____ (dɶɾti)',\n",
" '____s (keɪs)',\n",
" '____s (kɪ:s)',\n",
" '‘n lid d__r (da:ɾ)'],\n",
" '\"geel\"': ['___l (ɣe:l)',\n",
" '___l (ɣiəl)',\n",
" '___l (ɡe:l)',\n",
" '___l (ɡeɪl)',\n",
" '___l (ɡɪəl)'],\n",
" '\"gegaan\"': ['d____e (doətjə)',\n",
" 'g___ (ga:n)',\n",
" 'g___ (giən)',\n",
" 'g___ (giənt)',\n",
" 'g___ (gonən)',\n",
" 'g___ (gɔ:n)',\n",
" 'g___ (gɒnt)',\n",
" 'g___ (xɔn)',\n",
" 'g___ (xəxa:n)',\n",
" 'g___ (xɑ:n)',\n",
" 'g___ (ɣiən)',\n",
" '____s (keɪs)'],\n",
" '\"gezet\"': ['a____n (ɑRmən)',\n",
" 'sp___s (spɾɵts)',\n",
" '___n (sɛdn)',\n",
" '___n (sɛtn)',\n",
" '___t (sɛt)',\n",
" '___t (sɒt)',\n",
" '___t (sʌt)',\n",
" '___t (xəsɛt)',\n",
" '___t (ɡəsət)'],\n",
" '\"heel\"': ['e____n (e:ɾmən)',\n",
" 'h___l (he:l)',\n",
" 'h___l (heɪl)',\n",
" 'h___l (hi:l)',\n",
" 'h___l (hø:l)',\n",
" 'h___l (hɪəl)'],\n",
" '\"kaas\"': ['k___e (kʌtjə)',\n",
" 'k___s (ka:s)',\n",
" '____e (kezə)',\n",
" '____s (keɪs)',\n",
" '____s (kɪ:s)',\n",
" '____s (kɪ:əs)',\n",
" '____s (seɪs)',\n",
" '____s (si:s)',\n",
" '____s (tsi:s)'],\n",
" '\"koken\"': ['k___ (ko:kən)',\n",
" 'k___ (kouʔn)',\n",
" 'k___e (kɔkjə)',\n",
" 'k___e (kɒ:kjə)',\n",
" 'k___e (kʌtjə)',\n",
" 'k____e (kojtsə)',\n",
" 'k____e (koukə)',\n",
" 's___e (sɪədə)',\n",
" 's___e (sɪəɾə)',\n",
" 'ts___e (tsɪədə)',\n",
" 't___e (tʊskə)',\n",
" '___l (ɣiəl)'],\n",
" '\"oog\"': ['o___ (o:x)',\n",
" 'o___ (oɣə)',\n",
" '___ch (aɪx)',\n",
" '___ch (eɪx)',\n",
" '___ch (ɛ.ɪx)',\n",
" '___ch (ɛ:x)',\n",
" '___ch (ɪ.əx)'],\n",
" '\"sprak (toe)\"': ['sp__k (spɾi:k)',\n",
" 'sp__k (spɾɑk)',\n",
" 'sp___e (spɾɛktə)',\n",
" 'sp___e (spɾɑktə)',\n",
" 'sp___k (spRɑk)',\n",
" 'sp___k (spɾeɪk)',\n",
" 'sp___k (spɾøk)',\n",
" 'sp___s (spɾɵts)',\n",
" '____s (tsi:s)'],\n",
" '\"tand\"': ['b__st (bɒst)',\n",
" 'b___ (blɛ:t)',\n",
" 'k___e (ki:zə)',\n",
" 't___d (tɑnt)',\n",
" 't___e (ta:nə)',\n",
" 't___e (tʊskə)',\n",
" 't___k (tɵsk)',\n",
" 't___n (tɑ:n)',\n",
" 't___s (tos)',\n",
" 't___sk (tosk)'],\n",
" '\"trein\"': ['t____n (tRɛɪn)',\n",
" 't____n (tɾeɪn)',\n",
" 't____n (tɾɔɪn)',\n",
" 't____n (tɾɛɪn)',\n",
" 't____n (tɾɑɪn)',\n",
" '‘n lid d__r (da:ɾ)'],\n",
" '\"vis\"': ['t____n (tɾeɪn)', '___s (fɪs)', '___sk (fisk)'],\n",
" '\"zaterdag\"': ['d__i (dɑɪ)',\n",
" 's____n (snjoun)',\n",
" 's____n (snɵən)',\n",
" 's____n (snɪwn)',\n",
" 's_____ (sa:tədex)',\n",
" 's_____ (sa:tədi)',\n",
" 's_____ (sa:tədɑx)',\n",
" 's_____ (snɪət)',\n",
" 's_____ (sɑ:təɾjə)',\n",
" '____s (tsi:s)'],\n",
" 'avond': ['a____n (ɑʋən)',\n",
" 'a____nd (a:vənt)',\n",
" 'i____n (ɪwən)',\n",
" 'i____nd (ɪ.ənt)',\n",
" 'j___ne (jɔ:nə)',\n",
" 'j____n (joun)',\n",
" 'j____n (jun)',\n",
" 'j____ne (jaunə)',\n",
" 'j____ne (junə)',\n",
" 'o____nd (ɒ:bn̩t)'],\n",
" 'bij': ['b____ (be:i)',\n",
" 'b____ (bi:)',\n",
" 'b____ (bæi)',\n",
" 'b____ (bɛi)',\n",
" 'b____ (bɛiə)',\n",
" 'h_____k (hork)',\n",
" 'h_________r (hy:nəxauər)',\n",
" 'h_________r (hɵnəɣowər)',\n",
" 'i____ (imə)',\n",
" 'i__________r (i:nəfandər)'],\n",
" 'bij (*insect)': ['b____ (be:i)',\n",
" 'b____ (bi:)',\n",
" 'b____ (bæi)',\n",
" 'b____ (bɛi)',\n",
" 'b____ (bɛiə)',\n",
" 'h_____k (hork)',\n",
" 'h_________r (hy:nəxauər)',\n",
" 'h_________r (hɵnəɣowər)',\n",
" 'i____ (imə)',\n",
" 'i__________r (i:nəfandər)'],\n",
" 'blad': ['b___ (blet)',\n",
" 'b___ (blɔd)',\n",
" 'b___ (blɛ:t)',\n",
" 'b___ (blɑt)'],\n",
" 'blad (aan een boom)': ['b___ (blet)',\n",
" 'b___ (blɔd)',\n",
" 'b___ (blɛ:t)',\n",
" 'b___ (blɑt)'],\n",
" 'borst': ['b__st (bost)',\n",
" 'b__st (bwɑst)',\n",
" 'b__st (bɔɾst)',\n",
" 'b__st (bɵst)',\n",
" 'b__st (bɒst)',\n",
" 'b__st (bɪast)',\n",
" 'b__st (bɶst)',\n",
" 'b___t (bɔʃt)'],\n",
" 'borst (*lichaamsdeel)': ['b__st (bost)',\n",
" 'b__st (bwɑst)',\n",
" 'b__st (bɔɾst)',\n",
" 'b__st (bɵst)',\n",
" 'b__st (bɒst)',\n",
" 'b__st (bɪast)',\n",
" 'b__st (bɶst)',\n",
" 'b___t (bɔʃt)'],\n",
" 'dag': ['d__i (dɛɪ)', 'd__i (dɑɪ)', 'd___ (dɑ:x)', 'd___ (dɑx)'],\n",
" 'deurtje': ['d____e (do:ɾtjə)',\n",
" 'd____e (doətjə)',\n",
" 'd____e (dwɑɾkə)',\n",
" 'd____e (dø:ɾtjə)',\n",
" 'd____e (dɔ:ɾkə)',\n",
" 'd____e (dɵ:ɾkə)',\n",
" 'd____e (dɵʁkə)',\n",
" 'd____e (dɶɾkə)',\n",
" 'd_____ (dɵ:tin)',\n",
" 'd_____ (dɵ:tsi)',\n",
" 'd_____ (dɶɾti)',\n",
" '‘n lid d__r (da:ɾ)'],\n",
" 'geel': ['___l (ɣe:l)',\n",
" '___l (ɣiəl)',\n",
" '___l (ɡe:l)',\n",
" '___l (ɡeɪl)',\n",
" '___l (ɡɪəl)'],\n",
" 'heel': ['h___l (he:l)',\n",
" 'h___l (heɪl)',\n",
" 'h___l (hi:l)',\n",
" 'h___l (hø:l)',\n",
" 'h___l (hɪəl)'],\n",
" 'ik ben gegaan': ['g___ (ga:n)',\n",
" 'g___ (giən)',\n",
" 'g___ (giənt)',\n",
" 'g___ (gonən)',\n",
" 'g___ (gɔ:n)',\n",
" 'g___ (gɒnt)',\n",
" 'g___ (xɔn)',\n",
" 'g___ (xəxa:n)',\n",
" 'g___ (xɑ:n)',\n",
" 'g___ (ɣiən)'],\n",
" 'ik heb gezet': ['___n (sɛdn)',\n",
" '___n (sɛtn)',\n",
" '___t (sɛt)',\n",
" '___t (sɒt)',\n",
" '___t (sʌt)',\n",
" '___t (xəsɛt)',\n",
" '___t (ɡəsət)'],\n",
" 'ik sprak': ['sp__e (spɾɛktə)',\n",
" 'sp__e (spɾɑktə)',\n",
" 'sp__k (spRɑk)',\n",
" 'sp__k (spɾeɪk)',\n",
" 'sp__k (spɾi:k)',\n",
" 'sp__k (spɾøk)',\n",
" 'sp__k (spɾɑk)',\n",
" 'sp__s (spɾɵts)'],\n",
" 'ik sprak (toe)': ['sp__e (spɾɛktə)',\n",
" 'sp__e (spɾɑktə)',\n",
" 'sp__k (spRɑk)',\n",
" 'sp__k (spɾeɪk)',\n",
" 'sp__k (spɾi:k)',\n",
" 'sp__k (spɾøk)',\n",
" 'sp__k (spɾɑk)',\n",
" 'sp__s (spɾɵts)'],\n",
" 'kaas': ['k___s (ka:s)',\n",
" '____e (kezə)',\n",
" '____s (keɪs)',\n",
" '____s (kɪ:s)',\n",
" '____s (kɪ:əs)',\n",
" '____s (seɪs)',\n",
" '____s (si:s)',\n",
" '____s (tsi:s)'],\n",
" 'koken': ['k__e (kʌtjə)',\n",
" 'k___ (ko:kən)',\n",
" 'k___ (kouʔn)',\n",
" 'k___e (kojtsə)',\n",
" 'k___e (koukə)',\n",
" 'k___e (kɔkjə)',\n",
" 'k___e (kɒ:kjə)',\n",
" 's__e (sɪədə)',\n",
" 's__e (sɪəɾə)',\n",
" 'ts__e (tsɪədə)'],\n",
" 'oog': ['o___ (o:x)',\n",
" 'o___ (oɣə)',\n",
" '___ch (aɪx)',\n",
" '___ch (eɪx)',\n",
" '___ch (ɛ.ɪx)',\n",
" '___ch (ɛ:x)',\n",
" '___ch (ɪ.əx)'],\n",
" 'tand': ['k___e (ki:zə)',\n",
" 't__sk (tosk)',\n",
" 't___d (tɑnt)',\n",
" 't___e (ta:nə)',\n",
" 't___e (tʊskə)',\n",
" 't___k (tɵsk)',\n",
" 't___n (tɑ:n)',\n",
" 't___s (tos)'],\n",
" 'trein': ['t___n (tRɛɪn)',\n",
" 't___n (tɾeɪn)',\n",
" 't___n (tɾɔɪn)',\n",
" 't___n (tɾɛɪn)',\n",
" 't___n (tɾɑɪn)'],\n",
" 'vis': ['___s (fɪs)', '___sk (fisk)'],\n",
" 'zaterdag': ['s____n (snjoun)',\n",
" 's____n (snɵən)',\n",
" 's____n (snɪwn)',\n",
" 's_____ (sa:tədex)',\n",
" 's_____ (sa:tədi)',\n",
" 's_____ (sa:tədɑx)',\n",
" 's_____ (snɪət)',\n",
" 's_____ (sɑ:təɾjə)'],\n",
" '__armen': ['a____n (ɑRmən)',\n",
" 'a____n (ɑɾmən)',\n",
" 'a____s (ɑɾms)',\n",
" 'e____n (e:ɾmən)',\n",
" 'e____n (ɛɾmən)',\n",
" 'e____s (ɛ:ɾms)',\n",
" 'j____n (jɛɾmən)',\n",
" 'j____s (jɛɾms)',\n",
" 'j____s (jɛɾməns)'],\n",
" '__armen (*lichaamsdeel)': ['a____n (ɑRmən)',\n",
" 'a____n (ɑɾmən)',\n",
" 'a____s (ɑɾms)',\n",
" 'e____n (e:ɾmən)',\n",
" 'e____n (ɛɾmən)',\n",
" 'e____s (ɛ:ɾms)',\n",
" 'j____n (jɛɾmən)',\n",
" 'j____s (jɛɾms)',\n",
" 'j____s (jɛɾməns)']})"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pronunciations"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"from collections import defaultdict\n",
"\n",
"pronunciations = defaultdict(list)\n",
"\n",
"\n",
"for _, row in pandas.read_sql('''\n",
" SELECT \n",
" answer.question_text AS question,\n",
" answer.answer_text AS answer\n",
" FROM core_predictionquizresult AS quiz\n",
" JOIN core_predictionquizresultquestionanswer AS answer\n",
" ON answer.prediction_quiz_id = quiz.id\n",
" WHERE quiz.submitted_at > '2017-09-19'\n",
" GROUP BY answer.question_text, answer.answer_text\n",
"''', db).iterrows():\n",
" pronunciations[row['question']].append(row['answer'])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### How many individual users are there in the corpus of picture task recordings?\n",
"\n",
"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."
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>COUNT(DISTINCT survey_result_id)</th>\n",
" <th>COUNT(DISTINCT session_id)</th>\n",
" <th>COUNT(*)</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>1930</td>\n",
" <td>24695</td>\n",
" <td>41655</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" COUNT(DISTINCT survey_result_id) COUNT(DISTINCT session_id) COUNT(*)\n",
"0 1930 24695 41655"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pandas.read_sql('''\n",
" SELECT COUNT(DISTINCT survey_result_id), COUNT(DISTINCT session_id), COUNT(*)\n",
" FROM core_picturegameresult\n",
" WHERE submitted_at > \"2017-09-19\"\n",
"''', db)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### How many users have made recordings in more than one language?\n",
"\n",
"* and if possible a list with the device ids\n",
"* it would be great to have the breakdown of amounts of quiz taking over the months: sep-17, oct-17, ..., sep-18\n",
"\n",
"There are not so many, these are the ones:"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>year-month</th>\n",
" <th># games</th>\n",
" <th># surveys</th>\n",
" <th>languages</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2017-9</td>\n",
" <td>7</td>\n",
" <td>2</td>\n",
" <td>English,Nederlands,Frysk,Stadsfrys</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2017-10</td>\n",
" <td>7</td>\n",
" <td>2</td>\n",
" <td>Frysk,Hielpers,Nederlands</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2017-11</td>\n",
" <td>10</td>\n",
" <td>4</td>\n",
" <td>Nederlands,Frysk,Français,Sranan</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2017-12</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>Frysk,Nederlands</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2018-4</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>Stadsfrys,Nederlands</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>2018-11</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>Gronings,Nederlands</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" year-month # games # surveys languages\n",
"0 2017-9 7 2 English,Nederlands,Frysk,Stadsfrys\n",
"1 2017-10 7 2 Frysk,Hielpers,Nederlands\n",
"2 2017-11 10 4 Nederlands,Frysk,Français,Sranan\n",
"3 2017-12 2 1 Frysk,Nederlands\n",
"4 2018-4 2 1 Stadsfrys,Nederlands\n",
"5 2018-11 2 1 Gronings,Nederlands"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pandas.read_sql('''\n",
" SELECT\n",
" CONCAT(YEAR(result.submitted_at), '-', MONTH(result.submitted_at)) AS 'year-month',\n",
" COUNT(*) AS '# games',\n",
" COUNT(DISTINCT result.survey_result_id) AS '# surveys',\n",
" GROUP_CONCAT(DISTINCT lang.name SEPARATOR ',') AS 'languages'\n",
" FROM core_freespeechresult AS result\n",
" LEFT JOIN core_language as lang\n",
" ON lang.id = result.language_id\n",
" WHERE submitted_at > \"2017-09-19\"\n",
" AND result.survey_result_id IN (\n",
" SELECT result.survey_result_id\n",
" FROM core_freespeechresult AS result\n",
" WHERE submitted_at > \"2017-09-19\"\n",
" GROUP BY result.survey_result_id\n",
" HAVING COUNT(DISTINCT result.language_id) > 1\n",
" )\n",
" GROUP BY YEAR(result.submitted_at), MONTH(result.submitted_at)\n",
"''', db)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The device ids:"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>device_id</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>738191E5-80E4-4D07-A15E-6B7DE881906A</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>fb28928ef1703592</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>820E203F-1B70-40A8-BD47-04681D07BC9C</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>1aa95d23fddf3827</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>F95F07AE-B524-4256-9171-F7FFA9E49156</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>C8F56303-E912-4BC3-AD51-E1CA8B425AE9</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>B653C16D-BDA8-4A47-BC6A-C3171D89AF71</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>b40fb5e73d431e6b</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>C9FD6142-A64A-4AD8-B317-3BB833A11667</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>8190723c74b4b0bf</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" device_id\n",
"0 738191E5-80E4-4D07-A15E-6B7DE881906A\n",
"1 fb28928ef1703592\n",
"2 820E203F-1B70-40A8-BD47-04681D07BC9C\n",
"3 1aa95d23fddf3827\n",
"4 F95F07AE-B524-4256-9171-F7FFA9E49156\n",
"5 C8F56303-E912-4BC3-AD51-E1CA8B425AE9\n",
"6 B653C16D-BDA8-4A47-BC6A-C3171D89AF71\n",
"7 b40fb5e73d431e6b\n",
"8 C9FD6142-A64A-4AD8-B317-3BB833A11667\n",
"9 A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D\n",
"10 8190723c74b4b0bf"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pandas.read_sql('''\n",
" SELECT\n",
" DISTINCT survey.device_id\n",
" FROM core_freespeechresult AS result\n",
" LEFT JOIN core_language as lang\n",
" ON lang.id = result.language_id\n",
" LEFT JOIN core_surveyresult as survey\n",
" ON survey.id = result.survey_result_id\n",
" WHERE result.submitted_at > \"2017-09-19\"\n",
" AND result.survey_result_id IN (\n",
" SELECT result.survey_result_id\n",
" FROM core_freespeechresult AS result\n",
" WHERE result.submitted_at > \"2017-09-19\"\n",
" GROUP BY result.survey_result_id\n",
" HAVING COUNT(DISTINCT result.language_id) > 1\n",
" )\n",
"''', db)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The results:"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"scrolled": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>device_id</th>\n",
" <th>language</th>\n",
" <th>id</th>\n",
" <th>session_id</th>\n",
" <th>app_locale</th>\n",
" <th>submitted_at</th>\n",
" <th>theme_short_text</th>\n",
" <th>theme_long_text</th>\n",
" <th>recording</th>\n",
" <th>language_id</th>\n",
" <th>speech_theme_id</th>\n",
" <th>survey_result_id</th>\n",
" <th>theme_long_text_en</th>\n",
" <th>theme_long_text_fy</th>\n",
" <th>theme_long_text_nl</th>\n",
" <th>theme_short_text_en</th>\n",
" <th>theme_short_text_fy</th>\n",
" <th>theme_short_text_nl</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>738191E5-80E4-4D07-A15E-6B7DE881906A</td>\n",
" <td>Stadsfrys</td>\n",
" <td>159</td>\n",
" <td>6349CCA2-358F-4AE5-821F-543AD0B9C97A</td>\n",
" <td>fy</td>\n",
" <td>2017-09-20 21:05:47</td>\n",
" <td>What is your word for...</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>free_speech_recordings/Stadsfrys/2017-09-20/fs...</td>\n",
" <td>14</td>\n",
" <td>9</td>\n",
" <td>1539</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>Wat is it wurd foar \"ierappel\" yn dyn taal?</td>\n",
" <td>Wat is het woord voor \"aardappel\" in je taal?</td>\n",
" <td>What is your word for...</td>\n",
" <td>Wat is dyn wurd foar...</td>\n",
" <td>Wat is je woord voor...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>738191E5-80E4-4D07-A15E-6B7DE881906A</td>\n",
" <td>Stadsfrys</td>\n",
" <td>160</td>\n",
" <td>ACEF45B4-3162-4A5F-B83A-3E39BE140117</td>\n",
" <td>fy</td>\n",
" <td>2017-09-20 21:09:51</td>\n",
" <td>Your own story</td>\n",
" <td>Tell us your own story here</td>\n",
" <td>free_speech_recordings/Stadsfrys/2017-09-20/fs...</td>\n",
" <td>14</td>\n",
" <td>3</td>\n",
" <td>1539</td>\n",
" <td>Tell us your own story here</td>\n",
" <td>Fertel dyn eigen ferhaal hjir</td>\n",
" <td>Vertel je eigen verhaal hier</td>\n",
" <td>Your own story</td>\n",
" <td>Dyn eigen ferhaal</td>\n",
" <td>Je eigen verhaal</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>738191E5-80E4-4D07-A15E-6B7DE881906A</td>\n",
" <td>Frysk</td>\n",
" <td>161</td>\n",
" <td>ACEF45B4-3162-4A5F-B83A-3E39BE140117</td>\n",
" <td>fy</td>\n",
" <td>2017-09-20 21:15:01</td>\n",
" <td>Read a text 3</td>\n",
" <td>Then the sun began to shine, and the traveler ...</td>\n",
" <td>free_speech_recordings/Frysk/2017-09-20/fs_rea...</td>\n",
" <td>1</td>\n",
" <td>8</td>\n",
" <td>1539</td>\n",
" <td>Then the sun began to shine, and the traveler ...</td>\n",
" <td>Dêrnei begûn de sinne fûl te skinen en fuorten...</td>\n",
" <td>Daarna begon de zon krachtig te stralen, en hi...</td>\n",
" <td>Read a text 3</td>\n",
" <td>Foarlêze 3</td>\n",
" <td>Voorlezen 3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>fb28928ef1703592</td>\n",
" <td>Frysk</td>\n",
" <td>221</td>\n",
" <td>androidSessionIdfb28928ef170359258404890608067</td>\n",
" <td>nl</td>\n",
" <td>2017-09-23 15:05:05</td>\n",
" <td>What is your word for...</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>free_speech_recordings/Frysk/2017-09-23/fs_wha...</td>\n",
" <td>1</td>\n",
" <td>9</td>\n",
" <td>2951</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>Wat is it wurd foar \"ierappel\" yn dyn taal?</td>\n",
" <td>Wat is het woord voor \"aardappel\" in je taal?</td>\n",
" <td>What is your word for...</td>\n",
" <td>Wat is dyn wurd foar...</td>\n",
" <td>Wat is je woord voor...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>fb28928ef1703592</td>\n",
" <td>Frysk</td>\n",
" <td>222</td>\n",
" <td>androidSessionIdfb28928ef170359258618421150746</td>\n",
" <td>nl</td>\n",
" <td>2017-09-23 15:08:40</td>\n",
" <td>Growing up</td>\n",
" <td>Tell us a story about where you grew up?</td>\n",
" <td>free_speech_recordings/Frysk/2017-09-23/fs_gro...</td>\n",
" <td>1</td>\n",
" <td>5</td>\n",
" <td>2951</td>\n",
" <td>Tell us a story about where you grew up?</td>\n",
" <td>Fertel wat oer wêrst wei komst</td>\n",
" <td>Vertel iets over waar je bent opgegroeid?</td>\n",
" <td>Growing up</td>\n",
" <td>Opgroeie</td>\n",
" <td>Opgroeien</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>fb28928ef1703592</td>\n",
" <td>Nederlands</td>\n",
" <td>223</td>\n",
" <td>androidSessionIdfb28928ef170359258714896361595</td>\n",
" <td>nl</td>\n",
" <td>2017-09-23 15:10:16</td>\n",
" <td>Your own story</td>\n",
" <td>Tell us your own story here</td>\n",
" <td>free_speech_recordings/Nederlands/2017-09-23/f...</td>\n",
" <td>3</td>\n",
" <td>3</td>\n",
" <td>2951</td>\n",
" <td>Tell us your own story here</td>\n",
" <td>Fertel dyn eigen ferhaal hjir</td>\n",
" <td>Vertel je eigen verhaal hier</td>\n",
" <td>Your own story</td>\n",
" <td>Dyn eigen ferhaal</td>\n",
" <td>Je eigen verhaal</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>fb28928ef1703592</td>\n",
" <td>English</td>\n",
" <td>224</td>\n",
" <td>androidSessionIdfb28928ef170359259137367204559</td>\n",
" <td>nl</td>\n",
" <td>2017-09-23 15:17:19</td>\n",
" <td>Your own story</td>\n",
" <td>Tell us your own story here</td>\n",
" <td>free_speech_recordings/English/2017-09-23/fs_y...</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>2951</td>\n",
" <td>Tell us your own story here</td>\n",
" <td>Fertel dyn eigen ferhaal hjir</td>\n",
" <td>Vertel je eigen verhaal hier</td>\n",
" <td>Your own story</td>\n",
" <td>Dyn eigen ferhaal</td>\n",
" <td>Je eigen verhaal</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>820E203F-1B70-40A8-BD47-04681D07BC9C</td>\n",
" <td>Nederlands</td>\n",
" <td>333</td>\n",
" <td>28F45D4F-71F4-45A3-A9A2-B9813A16EC13</td>\n",
" <td>nl</td>\n",
" <td>2017-10-12 08:49:53</td>\n",
" <td>What is your word for...</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>free_speech_recordings/Nederlands/2017-10-12/f...</td>\n",
" <td>3</td>\n",
" <td>9</td>\n",
" <td>5477</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>Wat is it wurd foar \"ierappel\" yn dyn taal?</td>\n",
" <td>Wat is het woord voor \"aardappel\" in je taal?</td>\n",
" <td>What is your word for...</td>\n",
" <td>Wat is dyn wurd foar...</td>\n",
" <td>Wat is je woord voor...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>820E203F-1B70-40A8-BD47-04681D07BC9C</td>\n",
" <td>Nederlands</td>\n",
" <td>334</td>\n",
" <td>28F45D4F-71F4-45A3-A9A2-B9813A16EC13</td>\n",
" <td>nl</td>\n",
" <td>2017-10-12 08:50:49</td>\n",
" <td>What would you do with a million euros?</td>\n",
" <td>What would you do with a million euros?</td>\n",
" <td>free_speech_recordings/Nederlands/2017-10-12/f...</td>\n",
" <td>3</td>\n",
" <td>4</td>\n",
" <td>5477</td>\n",
" <td>What would you do with a million euros?</td>\n",
" <td>Wat soesto dwaan mei €1.000.000?</td>\n",
" <td>Wat zou je doen met €1.000.000?</td>\n",
" <td>What would you do with a million euros?</td>\n",
" <td>Wat soesto dwaan mei €1.000.000?</td>\n",
" <td>Wat zou je doen met €1.000.000?</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>820E203F-1B70-40A8-BD47-04681D07BC9C</td>\n",
" <td>Frysk</td>\n",
" <td>335</td>\n",
" <td>28F45D4F-71F4-45A3-A9A2-B9813A16EC13</td>\n",
" <td>nl</td>\n",
" <td>2017-10-12 08:51:51</td>\n",
" <td>Your own story</td>\n",
" <td>Tell us your own story here</td>\n",
" <td>free_speech_recordings/Frysk/2017-10-12/fs_you...</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>5477</td>\n",
" <td>Tell us your own story here</td>\n",
" <td>Fertel dyn eigen ferhaal hjir</td>\n",
" <td>Vertel je eigen verhaal hier</td>\n",
" <td>Your own story</td>\n",
" <td>Dyn eigen ferhaal</td>\n",
" <td>Je eigen verhaal</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>1aa95d23fddf3827</td>\n",
" <td>Frysk</td>\n",
" <td>373</td>\n",
" <td>androidSessionId1aa95d23fddf3827591709392581151</td>\n",
" <td>fy</td>\n",
" <td>2017-10-26 11:10:42</td>\n",
" <td>What is your word for...</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>free_speech_recordings/Frysk/2017-10-26/fs_wha...</td>\n",
" <td>1</td>\n",
" <td>9</td>\n",
" <td>6135</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>Wat is it wurd foar \"ierappel\" yn dyn taal?</td>\n",
" <td>Wat is het woord voor \"aardappel\" in je taal?</td>\n",
" <td>What is your word for...</td>\n",
" <td>Wat is dyn wurd foar...</td>\n",
" <td>Wat is je woord voor...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>1aa95d23fddf3827</td>\n",
" <td>Frysk</td>\n",
" <td>374</td>\n",
" <td>androidSessionId1aa95d23fddf3827591856351721095</td>\n",
" <td>fy</td>\n",
" <td>2017-10-26 11:13:08</td>\n",
" <td>Read a text 3</td>\n",
" <td>Then the sun began to shine, and the traveler ...</td>\n",
" <td>free_speech_recordings/Frysk/2017-10-26/fs_rea...</td>\n",
" <td>1</td>\n",
" <td>8</td>\n",
" <td>6135</td>\n",
" <td>Then the sun began to shine, and the traveler ...</td>\n",
" <td>Dêrnei begûn de sinne fûl te skinen en fuorten...</td>\n",
" <td>Daarna begon de zon krachtig te stralen, en hi...</td>\n",
" <td>Read a text 3</td>\n",
" <td>Foarlêze 3</td>\n",
" <td>Voorlezen 3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>1aa95d23fddf3827</td>\n",
" <td>Hielpers</td>\n",
" <td>375</td>\n",
" <td>androidSessionId1aa95d23fddf3827592248608182404</td>\n",
" <td>fy</td>\n",
" <td>2017-10-26 11:19:40</td>\n",
" <td>Read a text 3</td>\n",
" <td>Then the sun began to shine, and the traveler ...</td>\n",
" <td>free_speech_recordings/Hielpers/2017-10-26/fs_...</td>\n",
" <td>9</td>\n",
" <td>8</td>\n",
" <td>6135</td>\n",
" <td>Then the sun began to shine, and the traveler ...</td>\n",
" <td>Dêrnei begûn de sinne fûl te skinen en fuorten...</td>\n",
" <td>Daarna begon de zon krachtig te stralen, en hi...</td>\n",
" <td>Read a text 3</td>\n",
" <td>Foarlêze 3</td>\n",
" <td>Voorlezen 3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>1aa95d23fddf3827</td>\n",
" <td>Frysk</td>\n",
" <td>376</td>\n",
" <td>androidSessionId1aa95d23fddf3827592854579719673</td>\n",
" <td>fy</td>\n",
" <td>2017-10-26 11:29:47</td>\n",
" <td>Growing up</td>\n",
" <td>Tell us a story about where you grew up?</td>\n",
" <td>free_speech_recordings/Frysk/2017-10-26/fs_gro...</td>\n",
" <td>1</td>\n",
" <td>5</td>\n",
" <td>6135</td>\n",
" <td>Tell us a story about where you grew up?</td>\n",
" <td>Fertel wat oer wêrst wei komst</td>\n",
" <td>Vertel iets over waar je bent opgegroeid?</td>\n",
" <td>Growing up</td>\n",
" <td>Opgroeie</td>\n",
" <td>Opgroeien</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>F95F07AE-B524-4256-9171-F7FFA9E49156</td>\n",
" <td>Frysk</td>\n",
" <td>388</td>\n",
" <td>70BBF265-BB3B-4B53-8C1E-9B6CA179906C</td>\n",
" <td>fy</td>\n",
" <td>2017-11-05 11:44:03</td>\n",
" <td>Growing up</td>\n",
" <td>Tell us a story about where you grew up?</td>\n",
" <td>free_speech_recordings/Frysk/2017-11-05/fs_gro...</td>\n",
" <td>1</td>\n",
" <td>5</td>\n",
" <td>6255</td>\n",
" <td>Tell us a story about where you grew up?</td>\n",
" <td>Fertel wat oer wêrst wei komst</td>\n",
" <td>Vertel iets over waar je bent opgegroeid?</td>\n",
" <td>Growing up</td>\n",
" <td>Opgroeie</td>\n",
" <td>Opgroeien</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>F95F07AE-B524-4256-9171-F7FFA9E49156</td>\n",
" <td>Frysk</td>\n",
" <td>389</td>\n",
" <td>70BBF265-BB3B-4B53-8C1E-9B6CA179906C</td>\n",
" <td>fy</td>\n",
" <td>2017-11-05 11:48:01</td>\n",
" <td>Read a text 2</td>\n",
" <td>The North Wind blew as hard as he could, but t...</td>\n",
" <td>free_speech_recordings/Frysk/2017-11-05/fs_rea...</td>\n",
" <td>1</td>\n",
" <td>7</td>\n",
" <td>6255</td>\n",
" <td>The North Wind blew as hard as he could, but t...</td>\n",
" <td>De noardewyn begûn út alle macht te blazen, ma...</td>\n",
" <td>De noordenwind begon toen uit alle macht te bl...</td>\n",
" <td>Read a text 2</td>\n",
" <td>Foarlêze 2</td>\n",
" <td>Voorlezen 2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>F95F07AE-B524-4256-9171-F7FFA9E49156</td>\n",
" <td>Frysk</td>\n",
" <td>390</td>\n",
" <td>70BBF265-BB3B-4B53-8C1E-9B6CA179906C</td>\n",
" <td>fy</td>\n",
" <td>2017-11-05 11:48:56</td>\n",
" <td>What is your word for...</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>free_speech_recordings/Frysk/2017-11-05/fs_wha...</td>\n",
" <td>1</td>\n",
" <td>9</td>\n",
" <td>6255</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>Wat is it wurd foar \"ierappel\" yn dyn taal?</td>\n",
" <td>Wat is het woord voor \"aardappel\" in je taal?</td>\n",
" <td>What is your word for...</td>\n",
" <td>Wat is dyn wurd foar...</td>\n",
" <td>Wat is je woord voor...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>F95F07AE-B524-4256-9171-F7FFA9E49156</td>\n",
" <td>Sranan</td>\n",
" <td>391</td>\n",
" <td>70BBF265-BB3B-4B53-8C1E-9B6CA179906C</td>\n",
" <td>fy</td>\n",
" <td>2017-11-05 11:52:51</td>\n",
" <td>Growing up</td>\n",
" <td>Tell us a story about where you grew up?</td>\n",
" <td>free_speech_recordings/Sranan/2017-11-05/fs_gr...</td>\n",
" <td>16</td>\n",
" <td>5</td>\n",
" <td>6255</td>\n",
" <td>Tell us a story about where you grew up?</td>\n",
" <td>Fertel wat oer wêrst wei komst</td>\n",
" <td>Vertel iets over waar je bent opgegroeid?</td>\n",
" <td>Growing up</td>\n",
" <td>Opgroeie</td>\n",
" <td>Opgroeien</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td>C8F56303-E912-4BC3-AD51-E1CA8B425AE9</td>\n",
" <td>Frysk</td>\n",
" <td>410</td>\n",
" <td>0D2F2AD3-177A-4991-AD5F-55CAF1BFDE35</td>\n",
" <td>nl</td>\n",
" <td>2017-11-16 13:15:06</td>\n",
" <td>What is your word for...</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>free_speech_recordings/Frysk/2017-11-16/fs_wha...</td>\n",
" <td>1</td>\n",
" <td>9</td>\n",
" <td>6445</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>Wat is it wurd foar \"ierappel\" yn dyn taal?</td>\n",
" <td>Wat is het woord voor \"aardappel\" in je taal?</td>\n",
" <td>What is your word for...</td>\n",
" <td>Wat is dyn wurd foar...</td>\n",
" <td>Wat is je woord voor...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>19</th>\n",
" <td>B653C16D-BDA8-4A47-BC6A-C3171D89AF71</td>\n",
" <td>Frysk</td>\n",
" <td>411</td>\n",
" <td>07E90580-694C-4F9C-A82B-D431D88F6508</td>\n",
" <td>nl</td>\n",
" <td>2017-11-16 13:16:32</td>\n",
" <td>What is your word for...</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>free_speech_recordings/Frysk/2017-11-16/fs_wha...</td>\n",
" <td>1</td>\n",
" <td>9</td>\n",
" <td>6450</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>Wat is it wurd foar \"ierappel\" yn dyn taal?</td>\n",
" <td>Wat is het woord voor \"aardappel\" in je taal?</td>\n",
" <td>What is your word for...</td>\n",
" <td>Wat is dyn wurd foar...</td>\n",
" <td>Wat is je woord voor...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>20</th>\n",
" <td>B653C16D-BDA8-4A47-BC6A-C3171D89AF71</td>\n",
" <td>Nederlands</td>\n",
" <td>412</td>\n",
" <td>4CB961F7-432A-43EE-B748-41CB18DA6ACC</td>\n",
" <td>nl</td>\n",
" <td>2017-11-16 13:20:38</td>\n",
" <td>Joke</td>\n",
" <td>Tell us a joke!</td>\n",
" <td>free_speech_recordings/Nederlands/2017-11-16/f...</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>6450</td>\n",
" <td>Tell us a joke!</td>\n",
" <td>Fertel in mopke!</td>\n",
" <td>Vertel een mop!</td>\n",
" <td>Joke</td>\n",
" <td>Mopke</td>\n",
" <td>Mop</td>\n",
" </tr>\n",
" <tr>\n",
" <th>21</th>\n",
" <td>C8F56303-E912-4BC3-AD51-E1CA8B425AE9</td>\n",
" <td>Nederlands</td>\n",
" <td>414</td>\n",
" <td>0D2F2AD3-177A-4991-AD5F-55CAF1BFDE35</td>\n",
" <td>nl</td>\n",
" <td>2017-11-16 13:21:42</td>\n",
" <td>What is your word for...</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>free_speech_recordings/Nederlands/2017-11-16/f...</td>\n",
" <td>3</td>\n",
" <td>9</td>\n",
" <td>6445</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>Wat is it wurd foar \"ierappel\" yn dyn taal?</td>\n",
" <td>Wat is het woord voor \"aardappel\" in je taal?</td>\n",
" <td>What is your word for...</td>\n",
" <td>Wat is dyn wurd foar...</td>\n",
" <td>Wat is je woord voor...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>22</th>\n",
" <td>b40fb5e73d431e6b</td>\n",
" <td>Frysk</td>\n",
" <td>426</td>\n",
" <td>androidSessionIdb40fb5e73d431e6b136597419729297</td>\n",
" <td>nl</td>\n",
" <td>2017-11-22 13:14:36</td>\n",
" <td>What is your word for...</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>free_speech_recordings/Frysk/2017-11-22/fs_wha...</td>\n",
" <td>1</td>\n",
" <td>9</td>\n",
" <td>6496</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>Wat is it wurd foar \"ierappel\" yn dyn taal?</td>\n",
" <td>Wat is het woord voor \"aardappel\" in je taal?</td>\n",
" <td>What is your word for...</td>\n",
" <td>Wat is dyn wurd foar...</td>\n",
" <td>Wat is je woord voor...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>23</th>\n",
" <td>b40fb5e73d431e6b</td>\n",
" <td>Français</td>\n",
" <td>428</td>\n",
" <td>androidSessionIdb40fb5e73d431e6b136976454194882</td>\n",
" <td>nl</td>\n",
" <td>2017-11-22 13:20:56</td>\n",
" <td>Your own story</td>\n",
" <td>Tell us your own story here</td>\n",
" <td>free_speech_recordings/Français/2017-11-22/fs_...</td>\n",
" <td>22</td>\n",
" <td>3</td>\n",
" <td>6496</td>\n",
" <td>Tell us your own story here</td>\n",
" <td>Fertel dyn eigen ferhaal hjir</td>\n",
" <td>Vertel je eigen verhaal hier</td>\n",
" <td>Your own story</td>\n",
" <td>Dyn eigen ferhaal</td>\n",
" <td>Je eigen verhaal</td>\n",
" </tr>\n",
" <tr>\n",
" <th>24</th>\n",
" <td>C9FD6142-A64A-4AD8-B317-3BB833A11667</td>\n",
" <td>Nederlands</td>\n",
" <td>452</td>\n",
" <td>9A976F40-ADF7-4F72-9C2F-9BD4F1AF4DF5</td>\n",
" <td>nl</td>\n",
" <td>2017-12-12 10:36:40</td>\n",
" <td>What would you do with a million euros?</td>\n",
" <td>What would you do with a million euros?</td>\n",
" <td>free_speech_recordings/Nederlands/2017-12-12/f...</td>\n",
" <td>3</td>\n",
" <td>4</td>\n",
" <td>6892</td>\n",
" <td>What would you do with a million euros?</td>\n",
" <td>Wat soesto dwaan mei €1.000.000?</td>\n",
" <td>Wat zou je doen met €1.000.000?</td>\n",
" <td>What would you do with a million euros?</td>\n",
" <td>Wat soesto dwaan mei €1.000.000?</td>\n",
" <td>Wat zou je doen met €1.000.000?</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25</th>\n",
" <td>C9FD6142-A64A-4AD8-B317-3BB833A11667</td>\n",
" <td>Frysk</td>\n",
" <td>453</td>\n",
" <td>372127E4-AFFE-4BF5-BE7A-FF9D981F4D2E</td>\n",
" <td>nl</td>\n",
" <td>2017-12-12 10:42:33</td>\n",
" <td>What is your word for...</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>free_speech_recordings/Frysk/2017-12-12/fs_wha...</td>\n",
" <td>1</td>\n",
" <td>9</td>\n",
" <td>6892</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>Wat is it wurd foar \"ierappel\" yn dyn taal?</td>\n",
" <td>Wat is het woord voor \"aardappel\" in je taal?</td>\n",
" <td>What is your word for...</td>\n",
" <td>Wat is dyn wurd foar...</td>\n",
" <td>Wat is je woord voor...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>26</th>\n",
" <td>A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D</td>\n",
" <td>Nederlands</td>\n",
" <td>476</td>\n",
" <td>63B8E61A-433A-433A-924F-0B2D9D6F0353</td>\n",
" <td>fy</td>\n",
" <td>2018-04-08 17:37:12</td>\n",
" <td>What is your word for...</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>free_speech_recordings/Nederlands/2018-04-08/f...</td>\n",
" <td>3</td>\n",
" <td>9</td>\n",
" <td>7372</td>\n",
" <td>What is the word for \"potato\" in your local la...</td>\n",
" <td>Wat is it wurd foar \"ierappel\" yn dyn taal?</td>\n",
" <td>Wat is het woord voor \"aardappel\" in je taal?</td>\n",
" <td>What is your word for...</td>\n",
" <td>Wat is dyn wurd foar...</td>\n",
" <td>Wat is je woord voor...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>27</th>\n",
" <td>A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D</td>\n",
" <td>Stadsfrys</td>\n",
" <td>477</td>\n",
" <td>63B8E61A-433A-433A-924F-0B2D9D6F0353</td>\n",
" <td>fy</td>\n",
" <td>2018-04-08 17:46:49</td>\n",
" <td>What would you do with a million euros?</td>\n",
" <td>What would you do with a million euros?</td>\n",
" <td>free_speech_recordings/Stadsfrys/2018-04-08/fs...</td>\n",
" <td>14</td>\n",
" <td>4</td>\n",
" <td>7372</td>\n",
" <td>What would you do with a million euros?</td>\n",
" <td>Wat soesto dwaan mei €1.000.000?</td>\n",
" <td>Wat zou je doen met €1.000.000?</td>\n",
" <td>What would you do with a million euros?</td>\n",
" <td>Wat soesto dwaan mei €1.000.000?</td>\n",
" <td>Wat zou je doen met €1.000.000?</td>\n",
" </tr>\n",
" <tr>\n",
" <th>28</th>\n",
" <td>8190723c74b4b0bf</td>\n",
" <td>Nederlands</td>\n",
" <td>500</td>\n",
" <td>androidSessionId8190723c74b4b0bf57887798554061</td>\n",
" <td>nl</td>\n",
" <td>2018-11-18 11:03:50</td>\n",
" <td>Growing up</td>\n",
" <td>Tell us a story about where you grew up?</td>\n",
" <td>free_speech_recordings/Nederlands/2018-11-18/f...</td>\n",
" <td>3</td>\n",
" <td>5</td>\n",
" <td>8193</td>\n",
" <td>Tell us a story about where you grew up?</td>\n",
" <td>Fertel wat oer wêrst wei komst</td>\n",
" <td>Vertel iets over waar je bent opgegroeid?</td>\n",
" <td>Growing up</td>\n",
" <td>Opgroeie</td>\n",
" <td>Opgroeien</td>\n",
" </tr>\n",
" <tr>\n",
" <th>29</th>\n",
" <td>8190723c74b4b0bf</td>\n",
" <td>Gronings</td>\n",
" <td>501</td>\n",
" <td>androidSessionId8190723c74b4b0bf58065148631545</td>\n",
" <td>nl</td>\n",
" <td>2018-11-18 11:06:50</td>\n",
" <td>Growing up</td>\n",
" <td>Tell us a story about where you grew up?</td>\n",
" <td>free_speech_recordings/Gronings/2018-11-18/fs_...</td>\n",
" <td>4</td>\n",
" <td>5</td>\n",
" <td>8193</td>\n",
" <td>Tell us a story about where you grew up?</td>\n",
" <td>Fertel wat oer wêrst wei komst</td>\n",
" <td>Vertel iets over waar je bent opgegroeid?</td>\n",
" <td>Growing up</td>\n",
" <td>Opgroeie</td>\n",
" <td>Opgroeien</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" device_id language id \\\n",
"0 738191E5-80E4-4D07-A15E-6B7DE881906A Stadsfrys 159 \n",
"1 738191E5-80E4-4D07-A15E-6B7DE881906A Stadsfrys 160 \n",
"2 738191E5-80E4-4D07-A15E-6B7DE881906A Frysk 161 \n",
"3 fb28928ef1703592 Frysk 221 \n",
"4 fb28928ef1703592 Frysk 222 \n",
"5 fb28928ef1703592 Nederlands 223 \n",
"6 fb28928ef1703592 English 224 \n",
"7 820E203F-1B70-40A8-BD47-04681D07BC9C Nederlands 333 \n",
"8 820E203F-1B70-40A8-BD47-04681D07BC9C Nederlands 334 \n",
"9 820E203F-1B70-40A8-BD47-04681D07BC9C Frysk 335 \n",
"10 1aa95d23fddf3827 Frysk 373 \n",
"11 1aa95d23fddf3827 Frysk 374 \n",
"12 1aa95d23fddf3827 Hielpers 375 \n",
"13 1aa95d23fddf3827 Frysk 376 \n",
"14 F95F07AE-B524-4256-9171-F7FFA9E49156 Frysk 388 \n",
"15 F95F07AE-B524-4256-9171-F7FFA9E49156 Frysk 389 \n",
"16 F95F07AE-B524-4256-9171-F7FFA9E49156 Frysk 390 \n",
"17 F95F07AE-B524-4256-9171-F7FFA9E49156 Sranan 391 \n",
"18 C8F56303-E912-4BC3-AD51-E1CA8B425AE9 Frysk 410 \n",
"19 B653C16D-BDA8-4A47-BC6A-C3171D89AF71 Frysk 411 \n",
"20 B653C16D-BDA8-4A47-BC6A-C3171D89AF71 Nederlands 412 \n",
"21 C8F56303-E912-4BC3-AD51-E1CA8B425AE9 Nederlands 414 \n",
"22 b40fb5e73d431e6b Frysk 426 \n",
"23 b40fb5e73d431e6b Français 428 \n",
"24 C9FD6142-A64A-4AD8-B317-3BB833A11667 Nederlands 452 \n",
"25 C9FD6142-A64A-4AD8-B317-3BB833A11667 Frysk 453 \n",
"26 A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D Nederlands 476 \n",
"27 A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D Stadsfrys 477 \n",
"28 8190723c74b4b0bf Nederlands 500 \n",
"29 8190723c74b4b0bf Gronings 501 \n",
"\n",
" session_id app_locale \\\n",
"0 6349CCA2-358F-4AE5-821F-543AD0B9C97A fy \n",
"1 ACEF45B4-3162-4A5F-B83A-3E39BE140117 fy \n",
"2 ACEF45B4-3162-4A5F-B83A-3E39BE140117 fy \n",
"3 androidSessionIdfb28928ef170359258404890608067 nl \n",
"4 androidSessionIdfb28928ef170359258618421150746 nl \n",
"5 androidSessionIdfb28928ef170359258714896361595 nl \n",
"6 androidSessionIdfb28928ef170359259137367204559 nl \n",
"7 28F45D4F-71F4-45A3-A9A2-B9813A16EC13 nl \n",
"8 28F45D4F-71F4-45A3-A9A2-B9813A16EC13 nl \n",
"9 28F45D4F-71F4-45A3-A9A2-B9813A16EC13 nl \n",
"10 androidSessionId1aa95d23fddf3827591709392581151 fy \n",
"11 androidSessionId1aa95d23fddf3827591856351721095 fy \n",
"12 androidSessionId1aa95d23fddf3827592248608182404 fy \n",
"13 androidSessionId1aa95d23fddf3827592854579719673 fy \n",
"14 70BBF265-BB3B-4B53-8C1E-9B6CA179906C fy \n",
"15 70BBF265-BB3B-4B53-8C1E-9B6CA179906C fy \n",
"16 70BBF265-BB3B-4B53-8C1E-9B6CA179906C fy \n",
"17 70BBF265-BB3B-4B53-8C1E-9B6CA179906C fy \n",
"18 0D2F2AD3-177A-4991-AD5F-55CAF1BFDE35 nl \n",
"19 07E90580-694C-4F9C-A82B-D431D88F6508 nl \n",
"20 4CB961F7-432A-43EE-B748-41CB18DA6ACC nl \n",
"21 0D2F2AD3-177A-4991-AD5F-55CAF1BFDE35 nl \n",
"22 androidSessionIdb40fb5e73d431e6b136597419729297 nl \n",
"23 androidSessionIdb40fb5e73d431e6b136976454194882 nl \n",
"24 9A976F40-ADF7-4F72-9C2F-9BD4F1AF4DF5 nl \n",
"25 372127E4-AFFE-4BF5-BE7A-FF9D981F4D2E nl \n",
"26 63B8E61A-433A-433A-924F-0B2D9D6F0353 fy \n",
"27 63B8E61A-433A-433A-924F-0B2D9D6F0353 fy \n",
"28 androidSessionId8190723c74b4b0bf57887798554061 nl \n",
"29 androidSessionId8190723c74b4b0bf58065148631545 nl \n",
"\n",
" submitted_at theme_short_text \\\n",
"0 2017-09-20 21:05:47 What is your word for... \n",
"1 2017-09-20 21:09:51 Your own story \n",
"2 2017-09-20 21:15:01 Read a text 3 \n",
"3 2017-09-23 15:05:05 What is your word for... \n",
"4 2017-09-23 15:08:40 Growing up \n",
"5 2017-09-23 15:10:16 Your own story \n",
"6 2017-09-23 15:17:19 Your own story \n",
"7 2017-10-12 08:49:53 What is your word for... \n",
"8 2017-10-12 08:50:49 What would you do with a million euros? \n",
"9 2017-10-12 08:51:51 Your own story \n",
"10 2017-10-26 11:10:42 What is your word for... \n",
"11 2017-10-26 11:13:08 Read a text 3 \n",
"12 2017-10-26 11:19:40 Read a text 3 \n",
"13 2017-10-26 11:29:47 Growing up \n",
"14 2017-11-05 11:44:03 Growing up \n",
"15 2017-11-05 11:48:01 Read a text 2 \n",
"16 2017-11-05 11:48:56 What is your word for... \n",
"17 2017-11-05 11:52:51 Growing up \n",
"18 2017-11-16 13:15:06 What is your word for... \n",
"19 2017-11-16 13:16:32 What is your word for... \n",
"20 2017-11-16 13:20:38 Joke \n",
"21 2017-11-16 13:21:42 What is your word for... \n",
"22 2017-11-22 13:14:36 What is your word for... \n",
"23 2017-11-22 13:20:56 Your own story \n",
"24 2017-12-12 10:36:40 What would you do with a million euros? \n",
"25 2017-12-12 10:42:33 What is your word for... \n",
"26 2018-04-08 17:37:12 What is your word for... \n",
"27 2018-04-08 17:46:49 What would you do with a million euros? \n",
"28 2018-11-18 11:03:50 Growing up \n",
"29 2018-11-18 11:06:50 Growing up \n",
"\n",
" theme_long_text \\\n",
"0 What is the word for \"potato\" in your local la... \n",
"1 Tell us your own story here \n",
"2 Then the sun began to shine, and the traveler ... \n",
"3 What is the word for \"potato\" in your local la... \n",
"4 Tell us a story about where you grew up? \n",
"5 Tell us your own story here \n",
"6 Tell us your own story here \n",
"7 What is the word for \"potato\" in your local la... \n",
"8 What would you do with a million euros? \n",
"9 Tell us your own story here \n",
"10 What is the word for \"potato\" in your local la... \n",
"11 Then the sun began to shine, and the traveler ... \n",
"12 Then the sun began to shine, and the traveler ... \n",
"13 Tell us a story about where you grew up? \n",
"14 Tell us a story about where you grew up? \n",
"15 The North Wind blew as hard as he could, but t... \n",
"16 What is the word for \"potato\" in your local la... \n",
"17 Tell us a story about where you grew up? \n",
"18 What is the word for \"potato\" in your local la... \n",
"19 What is the word for \"potato\" in your local la... \n",
"20 Tell us a joke! \n",
"21 What is the word for \"potato\" in your local la... \n",
"22 What is the word for \"potato\" in your local la... \n",
"23 Tell us your own story here \n",
"24 What would you do with a million euros? \n",
"25 What is the word for \"potato\" in your local la... \n",
"26 What is the word for \"potato\" in your local la... \n",
"27 What would you do with a million euros? \n",
"28 Tell us a story about where you grew up? \n",
"29 Tell us a story about where you grew up? \n",
"\n",
" recording language_id \\\n",
"0 free_speech_recordings/Stadsfrys/2017-09-20/fs... 14 \n",
"1 free_speech_recordings/Stadsfrys/2017-09-20/fs... 14 \n",
"2 free_speech_recordings/Frysk/2017-09-20/fs_rea... 1 \n",
"3 free_speech_recordings/Frysk/2017-09-23/fs_wha... 1 \n",
"4 free_speech_recordings/Frysk/2017-09-23/fs_gro... 1 \n",
"5 free_speech_recordings/Nederlands/2017-09-23/f... 3 \n",
"6 free_speech_recordings/English/2017-09-23/fs_y... 2 \n",
"7 free_speech_recordings/Nederlands/2017-10-12/f... 3 \n",
"8 free_speech_recordings/Nederlands/2017-10-12/f... 3 \n",
"9 free_speech_recordings/Frysk/2017-10-12/fs_you... 1 \n",
"10 free_speech_recordings/Frysk/2017-10-26/fs_wha... 1 \n",
"11 free_speech_recordings/Frysk/2017-10-26/fs_rea... 1 \n",
"12 free_speech_recordings/Hielpers/2017-10-26/fs_... 9 \n",
"13 free_speech_recordings/Frysk/2017-10-26/fs_gro... 1 \n",
"14 free_speech_recordings/Frysk/2017-11-05/fs_gro... 1 \n",
"15 free_speech_recordings/Frysk/2017-11-05/fs_rea... 1 \n",
"16 free_speech_recordings/Frysk/2017-11-05/fs_wha... 1 \n",
"17 free_speech_recordings/Sranan/2017-11-05/fs_gr... 16 \n",
"18 free_speech_recordings/Frysk/2017-11-16/fs_wha... 1 \n",
"19 free_speech_recordings/Frysk/2017-11-16/fs_wha... 1 \n",
"20 free_speech_recordings/Nederlands/2017-11-16/f... 3 \n",
"21 free_speech_recordings/Nederlands/2017-11-16/f... 3 \n",
"22 free_speech_recordings/Frysk/2017-11-22/fs_wha... 1 \n",
"23 free_speech_recordings/Français/2017-11-22/fs_... 22 \n",
"24 free_speech_recordings/Nederlands/2017-12-12/f... 3 \n",
"25 free_speech_recordings/Frysk/2017-12-12/fs_wha... 1 \n",
"26 free_speech_recordings/Nederlands/2018-04-08/f... 3 \n",
"27 free_speech_recordings/Stadsfrys/2018-04-08/fs... 14 \n",
"28 free_speech_recordings/Nederlands/2018-11-18/f... 3 \n",
"29 free_speech_recordings/Gronings/2018-11-18/fs_... 4 \n",
"\n",
" speech_theme_id survey_result_id \\\n",
"0 9 1539 \n",
"1 3 1539 \n",
"2 8 1539 \n",
"3 9 2951 \n",
"4 5 2951 \n",
"5 3 2951 \n",
"6 3 2951 \n",
"7 9 5477 \n",
"8 4 5477 \n",
"9 3 5477 \n",
"10 9 6135 \n",
"11 8 6135 \n",
"12 8 6135 \n",
"13 5 6135 \n",
"14 5 6255 \n",
"15 7 6255 \n",
"16 9 6255 \n",
"17 5 6255 \n",
"18 9 6445 \n",
"19 9 6450 \n",
"20 2 6450 \n",
"21 9 6445 \n",
"22 9 6496 \n",
"23 3 6496 \n",
"24 4 6892 \n",
"25 9 6892 \n",
"26 9 7372 \n",
"27 4 7372 \n",
"28 5 8193 \n",
"29 5 8193 \n",
"\n",
" theme_long_text_en \\\n",
"0 What is the word for \"potato\" in your local la... \n",
"1 Tell us your own story here \n",
"2 Then the sun began to shine, and the traveler ... \n",
"3 What is the word for \"potato\" in your local la... \n",
"4 Tell us a story about where you grew up? \n",
"5 Tell us your own story here \n",
"6 Tell us your own story here \n",
"7 What is the word for \"potato\" in your local la... \n",
"8 What would you do with a million euros? \n",
"9 Tell us your own story here \n",
"10 What is the word for \"potato\" in your local la... \n",
"11 Then the sun began to shine, and the traveler ... \n",
"12 Then the sun began to shine, and the traveler ... \n",
"13 Tell us a story about where you grew up? \n",
"14 Tell us a story about where you grew up? \n",
"15 The North Wind blew as hard as he could, but t... \n",
"16 What is the word for \"potato\" in your local la... \n",
"17 Tell us a story about where you grew up? \n",
"18 What is the word for \"potato\" in your local la... \n",
"19 What is the word for \"potato\" in your local la... \n",
"20 Tell us a joke! \n",
"21 What is the word for \"potato\" in your local la... \n",
"22 What is the word for \"potato\" in your local la... \n",
"23 Tell us your own story here \n",
"24 What would you do with a million euros? \n",
"25 What is the word for \"potato\" in your local la... \n",
"26 What is the word for \"potato\" in your local la... \n",
"27 What would you do with a million euros? \n",
"28 Tell us a story about where you grew up? \n",
"29 Tell us a story about where you grew up? \n",
"\n",
" theme_long_text_fy \\\n",
"0 Wat is it wurd foar \"ierappel\" yn dyn taal? \n",
"1 Fertel dyn eigen ferhaal hjir \n",
"2 Dêrnei begûn de sinne fûl te skinen en fuorten... \n",
"3 Wat is it wurd foar \"ierappel\" yn dyn taal? \n",
"4 Fertel wat oer wêrst wei komst \n",
"5 Fertel dyn eigen ferhaal hjir \n",
"6 Fertel dyn eigen ferhaal hjir \n",
"7 Wat is it wurd foar \"ierappel\" yn dyn taal? \n",
"8 Wat soesto dwaan mei €1.000.000? \n",
"9 Fertel dyn eigen ferhaal hjir \n",
"10 Wat is it wurd foar \"ierappel\" yn dyn taal? \n",
"11 Dêrnei begûn de sinne fûl te skinen en fuorten... \n",
"12 Dêrnei begûn de sinne fûl te skinen en fuorten... \n",
"13 Fertel wat oer wêrst wei komst \n",
"14 Fertel wat oer wêrst wei komst \n",
"15 De noardewyn begûn út alle macht te blazen, ma... \n",
"16 Wat is it wurd foar \"ierappel\" yn dyn taal? \n",
"17 Fertel wat oer wêrst wei komst \n",
"18 Wat is it wurd foar \"ierappel\" yn dyn taal? \n",
"19 Wat is it wurd foar \"ierappel\" yn dyn taal? \n",
"20 Fertel in mopke! \n",
"21 Wat is it wurd foar \"ierappel\" yn dyn taal? \n",
"22 Wat is it wurd foar \"ierappel\" yn dyn taal? \n",
"23 Fertel dyn eigen ferhaal hjir \n",
"24 Wat soesto dwaan mei €1.000.000? \n",
"25 Wat is it wurd foar \"ierappel\" yn dyn taal? \n",
"26 Wat is it wurd foar \"ierappel\" yn dyn taal? \n",
"27 Wat soesto dwaan mei €1.000.000? \n",
"28 Fertel wat oer wêrst wei komst \n",
"29 Fertel wat oer wêrst wei komst \n",
"\n",
" theme_long_text_nl \\\n",
"0 Wat is het woord voor \"aardappel\" in je taal? \n",
"1 Vertel je eigen verhaal hier \n",
"2 Daarna begon de zon krachtig te stralen, en hi... \n",
"3 Wat is het woord voor \"aardappel\" in je taal? \n",
"4 Vertel iets over waar je bent opgegroeid? \n",
"5 Vertel je eigen verhaal hier \n",
"6 Vertel je eigen verhaal hier \n",
"7 Wat is het woord voor \"aardappel\" in je taal? \n",
"8 Wat zou je doen met €1.000.000? \n",
"9 Vertel je eigen verhaal hier \n",
"10 Wat is het woord voor \"aardappel\" in je taal? \n",
"11 Daarna begon de zon krachtig te stralen, en hi... \n",
"12 Daarna begon de zon krachtig te stralen, en hi... \n",
"13 Vertel iets over waar je bent opgegroeid? \n",
"14 Vertel iets over waar je bent opgegroeid? \n",
"15 De noordenwind begon toen uit alle macht te bl... \n",
"16 Wat is het woord voor \"aardappel\" in je taal? \n",
"17 Vertel iets over waar je bent opgegroeid? \n",
"18 Wat is het woord voor \"aardappel\" in je taal? \n",
"19 Wat is het woord voor \"aardappel\" in je taal? \n",
"20 Vertel een mop! \n",
"21 Wat is het woord voor \"aardappel\" in je taal? \n",
"22 Wat is het woord voor \"aardappel\" in je taal? \n",
"23 Vertel je eigen verhaal hier \n",
"24 Wat zou je doen met €1.000.000? \n",
"25 Wat is het woord voor \"aardappel\" in je taal? \n",
"26 Wat is het woord voor \"aardappel\" in je taal? \n",
"27 Wat zou je doen met €1.000.000? \n",
"28 Vertel iets over waar je bent opgegroeid? \n",
"29 Vertel iets over waar je bent opgegroeid? \n",
"\n",
" theme_short_text_en theme_short_text_fy \\\n",
"0 What is your word for... Wat is dyn wurd foar... \n",
"1 Your own story Dyn eigen ferhaal \n",
"2 Read a text 3 Foarlêze 3 \n",
"3 What is your word for... Wat is dyn wurd foar... \n",
"4 Growing up Opgroeie \n",
"5 Your own story Dyn eigen ferhaal \n",
"6 Your own story Dyn eigen ferhaal \n",
"7 What is your word for... Wat is dyn wurd foar... \n",
"8 What would you do with a million euros? Wat soesto dwaan mei €1.000.000? \n",
"9 Your own story Dyn eigen ferhaal \n",
"10 What is your word for... Wat is dyn wurd foar... \n",
"11 Read a text 3 Foarlêze 3 \n",
"12 Read a text 3 Foarlêze 3 \n",
"13 Growing up Opgroeie \n",
"14 Growing up Opgroeie \n",
"15 Read a text 2 Foarlêze 2 \n",
"16 What is your word for... Wat is dyn wurd foar... \n",
"17 Growing up Opgroeie \n",
"18 What is your word for... Wat is dyn wurd foar... \n",
"19 What is your word for... Wat is dyn wurd foar... \n",
"20 Joke Mopke \n",
"21 What is your word for... Wat is dyn wurd foar... \n",
"22 What is your word for... Wat is dyn wurd foar... \n",
"23 Your own story Dyn eigen ferhaal \n",
"24 What would you do with a million euros? Wat soesto dwaan mei €1.000.000? \n",
"25 What is your word for... Wat is dyn wurd foar... \n",
"26 What is your word for... Wat is dyn wurd foar... \n",
"27 What would you do with a million euros? Wat soesto dwaan mei €1.000.000? \n",
"28 Growing up Opgroeie \n",
"29 Growing up Opgroeie \n",
"\n",
" theme_short_text_nl \n",
"0 Wat is je woord voor... \n",
"1 Je eigen verhaal \n",
"2 Voorlezen 3 \n",
"3 Wat is je woord voor... \n",
"4 Opgroeien \n",
"5 Je eigen verhaal \n",
"6 Je eigen verhaal \n",
"7 Wat is je woord voor... \n",
"8 Wat zou je doen met €1.000.000? \n",
"9 Je eigen verhaal \n",
"10 Wat is je woord voor... \n",
"11 Voorlezen 3 \n",
"12 Voorlezen 3 \n",
"13 Opgroeien \n",
"14 Opgroeien \n",
"15 Voorlezen 2 \n",
"16 Wat is je woord voor... \n",
"17 Opgroeien \n",
"18 Wat is je woord voor... \n",
"19 Wat is je woord voor... \n",
"20 Mop \n",
"21 Wat is je woord voor... \n",
"22 Wat is je woord voor... \n",
"23 Je eigen verhaal \n",
"24 Wat zou je doen met €1.000.000? \n",
"25 Wat is je woord voor... \n",
"26 Wat is je woord voor... \n",
"27 Wat zou je doen met €1.000.000? \n",
"28 Opgroeien \n",
"29 Opgroeien "
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pandas.read_sql('''\n",
" SELECT\n",
" survey.device_id,\n",
" lang.name as language,\n",
" result.*\n",
" FROM core_freespeechresult AS result\n",
" LEFT JOIN core_language as lang\n",
" ON lang.id = result.language_id\n",
" LEFT JOIN core_surveyresult as survey\n",
" ON survey.id = result.survey_result_id\n",
" WHERE result.submitted_at > \"2017-09-19\"\n",
" AND result.survey_result_id IN (\n",
" SELECT result.survey_result_id\n",
" FROM core_freespeechresult AS result\n",
" WHERE submitted_at > \"2017-09-19\"\n",
" GROUP BY result.survey_result_id\n",
" HAVING COUNT(DISTINCT result.language_id) > 1\n",
" )\n",
"''', db)"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<table border=\"1\" cellpadding=\"3\" cellspacing=\"0\" style=\"border:black; border-collapse:collapse;\"><tr><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\"></td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">core_freespeechresult</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">core_picturegameresult</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">core_predictionquizresult</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">core_surveyresult</td></tr><tr><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">2017-09</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">172</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">21416</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">8837</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">4413</td></tr><tr><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">2017-10</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">78</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">9286</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">2364</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">1307</td></tr><tr><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">2017-11</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">51</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">3586</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">701</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">464</td></tr><tr><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">2017-12</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">32</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">1642</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">997</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">466</td></tr><tr><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">2018-01</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">2</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">387</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">101</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">46</td></tr><tr><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">2018-02</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">1</td><td style=\"border-left: 1px solid;border-right: 1
],
"text/plain": [
"<ipy_table.ipy_table.IpyTable at 0x7fe937d55fd0>"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"allcounts = dict()\n",
"tables = ['core_freespeechresult', 'core_picturegameresult', 'core_predictionquizresult', 'core_surveyresult']\n",
"for table in tables:\n",
" counts = pandas.read_sql('''\n",
" SELECT\n",
" CONCAT(YEAR(result.submitted_at), '-', MONTH(result.submitted_at)) AS 'year-month',\n",
" COUNT(*) AS '# games'\n",
" FROM {table} AS result\n",
" WHERE submitted_at > \"2017-09-19\"\n",
" GROUP BY YEAR(result.submitted_at), MONTH(result.submitted_at)\n",
" '''.format(table=table), db)\n",
" allcounts[table] = dict(zip(\n",
" ['-'.join('{:02d}'.format(int(d)) for d in date.split('-')) for date in counts['year-month']],\n",
" counts['# games']\n",
" ))\n",
" \n",
"dates = sorted({\n",
" date for dates in allcounts.values() for date in dates\n",
"})\n",
"make_table([[''] + tables] + [\n",
" [date] + [allcounts[table].get(date, 0) for table in tables]\n",
" for date in dates\n",
"])"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<table border=\"1\" cellpadding=\"3\" cellspacing=\"0\" style=\"border:black; border-collapse:collapse;\"><tr><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">core_freespeechresult</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">2019-01-30T19:40:03.000000000</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">435</td></tr><tr><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">core_picturegameresult</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">2019-03-09T00:59:58.000000000</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">47672</td></tr><tr><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">core_predictionquizresult</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">2019-03-13T04:21:56.000000000</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">15522</td></tr><tr><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">core_surveyresult</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">2019-03-12T14:48:58.000000000</td><td style=\"border-left: 1px solid;border-right: 1px solid;border-top: 1px solid;border-bottom: 1px solid;\">8321</td></tr></table>"
],
"text/plain": [
"<ipy_table.ipy_table.IpyTable at 0x7fe936091f28>"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"make_table([\n",
" [\n",
" table,\n",
" pandas.read_sql('''\n",
" SELECT MAX(submitted_at) FROM {table}\n",
" '''.format(table=table), db).values[0, 0],\n",
" pandas.read_sql('''\n",
" SELECT COUNT(*) FROM {table}\n",
" '''.format(table=table), db).values[0, 0]\n",
" ]\n",
" for table in pandas.read_sql('''\n",
" SHOW TABLES\n",
" ''', db).values[:, 0]\n",
" if 'submitted_at' in set(pandas.read_sql('''\n",
" DESCRIBE {table}\n",
" '''.format(table=table), db)['Field'])\n",
"])"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.5"
}
},
"nbformat": 4,
"nbformat_minor": 2
}