{ "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
FieldTypeNullKeyDefaultExtra
0idint(11)NOPRINoneauto_increment
1question_textvarchar(255)NONone
2answer_textvarchar(255)NONone
3prediction_quiz_idint(11)NOMULNone
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
COUNT(DISTINCT survey_result_id)COUNT(DISTINCT session_id)COUNT(*)
019302469541655
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
year-month# games# surveyslanguages
02017-972English,Nederlands,Frysk,Stadsfrys
12017-1072Frysk,Hielpers,Nederlands
22017-11104Nederlands,Frysk,Français,Sranan
32017-1221Frysk,Nederlands
42018-421Stadsfrys,Nederlands
52018-1121Gronings,Nederlands
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
device_id
0738191E5-80E4-4D07-A15E-6B7DE881906A
1fb28928ef1703592
2820E203F-1B70-40A8-BD47-04681D07BC9C
31aa95d23fddf3827
4F95F07AE-B524-4256-9171-F7FFA9E49156
5C8F56303-E912-4BC3-AD51-E1CA8B425AE9
6B653C16D-BDA8-4A47-BC6A-C3171D89AF71
7b40fb5e73d431e6b
8C9FD6142-A64A-4AD8-B317-3BB833A11667
9A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D
108190723c74b4b0bf
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
device_idlanguageidsession_idapp_localesubmitted_attheme_short_texttheme_long_textrecordinglanguage_idspeech_theme_idsurvey_result_idtheme_long_text_entheme_long_text_fytheme_long_text_nltheme_short_text_entheme_short_text_fytheme_short_text_nl
0738191E5-80E4-4D07-A15E-6B7DE881906AStadsfrys1596349CCA2-358F-4AE5-821F-543AD0B9C97Afy2017-09-20 21:05:47What is your word for...What is the word for \"potato\" in your local la...free_speech_recordings/Stadsfrys/2017-09-20/fs...1491539What 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...
1738191E5-80E4-4D07-A15E-6B7DE881906AStadsfrys160ACEF45B4-3162-4A5F-B83A-3E39BE140117fy2017-09-20 21:09:51Your own storyTell us your own story herefree_speech_recordings/Stadsfrys/2017-09-20/fs...1431539Tell us your own story hereFertel dyn eigen ferhaal hjirVertel je eigen verhaal hierYour own storyDyn eigen ferhaalJe eigen verhaal
2738191E5-80E4-4D07-A15E-6B7DE881906AFrysk161ACEF45B4-3162-4A5F-B83A-3E39BE140117fy2017-09-20 21:15:01Read a text 3Then the sun began to shine, and the traveler ...free_speech_recordings/Frysk/2017-09-20/fs_rea...181539Then 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 3Foarlêze 3Voorlezen 3
3fb28928ef1703592Frysk221androidSessionIdfb28928ef170359258404890608067nl2017-09-23 15:05:05What is your word for...What is the word for \"potato\" in your local la...free_speech_recordings/Frysk/2017-09-23/fs_wha...192951What 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...
4fb28928ef1703592Frysk222androidSessionIdfb28928ef170359258618421150746nl2017-09-23 15:08:40Growing upTell us a story about where you grew up?free_speech_recordings/Frysk/2017-09-23/fs_gro...152951Tell us a story about where you grew up?Fertel wat oer wêrst wei komstVertel iets over waar je bent opgegroeid?Growing upOpgroeieOpgroeien
5fb28928ef1703592Nederlands223androidSessionIdfb28928ef170359258714896361595nl2017-09-23 15:10:16Your own storyTell us your own story herefree_speech_recordings/Nederlands/2017-09-23/f...332951Tell us your own story hereFertel dyn eigen ferhaal hjirVertel je eigen verhaal hierYour own storyDyn eigen ferhaalJe eigen verhaal
6fb28928ef1703592English224androidSessionIdfb28928ef170359259137367204559nl2017-09-23 15:17:19Your own storyTell us your own story herefree_speech_recordings/English/2017-09-23/fs_y...232951Tell us your own story hereFertel dyn eigen ferhaal hjirVertel je eigen verhaal hierYour own storyDyn eigen ferhaalJe eigen verhaal
7820E203F-1B70-40A8-BD47-04681D07BC9CNederlands33328F45D4F-71F4-45A3-A9A2-B9813A16EC13nl2017-10-12 08:49:53What is your word for...What is the word for \"potato\" in your local la...free_speech_recordings/Nederlands/2017-10-12/f...395477What 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...
8820E203F-1B70-40A8-BD47-04681D07BC9CNederlands33428F45D4F-71F4-45A3-A9A2-B9813A16EC13nl2017-10-12 08:50:49What would you do with a million euros?What would you do with a million euros?free_speech_recordings/Nederlands/2017-10-12/f...345477What 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?
9820E203F-1B70-40A8-BD47-04681D07BC9CFrysk33528F45D4F-71F4-45A3-A9A2-B9813A16EC13nl2017-10-12 08:51:51Your own storyTell us your own story herefree_speech_recordings/Frysk/2017-10-12/fs_you...135477Tell us your own story hereFertel dyn eigen ferhaal hjirVertel je eigen verhaal hierYour own storyDyn eigen ferhaalJe eigen verhaal
101aa95d23fddf3827Frysk373androidSessionId1aa95d23fddf3827591709392581151fy2017-10-26 11:10:42What is your word for...What is the word for \"potato\" in your local la...free_speech_recordings/Frysk/2017-10-26/fs_wha...196135What 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...
111aa95d23fddf3827Frysk374androidSessionId1aa95d23fddf3827591856351721095fy2017-10-26 11:13:08Read a text 3Then the sun began to shine, and the traveler ...free_speech_recordings/Frysk/2017-10-26/fs_rea...186135Then 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 3Foarlêze 3Voorlezen 3
121aa95d23fddf3827Hielpers375androidSessionId1aa95d23fddf3827592248608182404fy2017-10-26 11:19:40Read a text 3Then the sun began to shine, and the traveler ...free_speech_recordings/Hielpers/2017-10-26/fs_...986135Then 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 3Foarlêze 3Voorlezen 3
131aa95d23fddf3827Frysk376androidSessionId1aa95d23fddf3827592854579719673fy2017-10-26 11:29:47Growing upTell us a story about where you grew up?free_speech_recordings/Frysk/2017-10-26/fs_gro...156135Tell us a story about where you grew up?Fertel wat oer wêrst wei komstVertel iets over waar je bent opgegroeid?Growing upOpgroeieOpgroeien
14F95F07AE-B524-4256-9171-F7FFA9E49156Frysk38870BBF265-BB3B-4B53-8C1E-9B6CA179906Cfy2017-11-05 11:44:03Growing upTell us a story about where you grew up?free_speech_recordings/Frysk/2017-11-05/fs_gro...156255Tell us a story about where you grew up?Fertel wat oer wêrst wei komstVertel iets over waar je bent opgegroeid?Growing upOpgroeieOpgroeien
15F95F07AE-B524-4256-9171-F7FFA9E49156Frysk38970BBF265-BB3B-4B53-8C1E-9B6CA179906Cfy2017-11-05 11:48:01Read a text 2The North Wind blew as hard as he could, but t...free_speech_recordings/Frysk/2017-11-05/fs_rea...176255The 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 2Foarlêze 2Voorlezen 2
16F95F07AE-B524-4256-9171-F7FFA9E49156Frysk39070BBF265-BB3B-4B53-8C1E-9B6CA179906Cfy2017-11-05 11:48:56What is your word for...What is the word for \"potato\" in your local la...free_speech_recordings/Frysk/2017-11-05/fs_wha...196255What 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...
17F95F07AE-B524-4256-9171-F7FFA9E49156Sranan39170BBF265-BB3B-4B53-8C1E-9B6CA179906Cfy2017-11-05 11:52:51Growing upTell us a story about where you grew up?free_speech_recordings/Sranan/2017-11-05/fs_gr...1656255Tell us a story about where you grew up?Fertel wat oer wêrst wei komstVertel iets over waar je bent opgegroeid?Growing upOpgroeieOpgroeien
18C8F56303-E912-4BC3-AD51-E1CA8B425AE9Frysk4100D2F2AD3-177A-4991-AD5F-55CAF1BFDE35nl2017-11-16 13:15:06What is your word for...What is the word for \"potato\" in your local la...free_speech_recordings/Frysk/2017-11-16/fs_wha...196445What 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...
19B653C16D-BDA8-4A47-BC6A-C3171D89AF71Frysk41107E90580-694C-4F9C-A82B-D431D88F6508nl2017-11-16 13:16:32What is your word for...What is the word for \"potato\" in your local la...free_speech_recordings/Frysk/2017-11-16/fs_wha...196450What 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...
20B653C16D-BDA8-4A47-BC6A-C3171D89AF71Nederlands4124CB961F7-432A-43EE-B748-41CB18DA6ACCnl2017-11-16 13:20:38JokeTell us a joke!free_speech_recordings/Nederlands/2017-11-16/f...326450Tell us a joke!Fertel in mopke!Vertel een mop!JokeMopkeMop
21C8F56303-E912-4BC3-AD51-E1CA8B425AE9Nederlands4140D2F2AD3-177A-4991-AD5F-55CAF1BFDE35nl2017-11-16 13:21:42What is your word for...What is the word for \"potato\" in your local la...free_speech_recordings/Nederlands/2017-11-16/f...396445What 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...
22b40fb5e73d431e6bFrysk426androidSessionIdb40fb5e73d431e6b136597419729297nl2017-11-22 13:14:36What is your word for...What is the word for \"potato\" in your local la...free_speech_recordings/Frysk/2017-11-22/fs_wha...196496What 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...
23b40fb5e73d431e6bFrançais428androidSessionIdb40fb5e73d431e6b136976454194882nl2017-11-22 13:20:56Your own storyTell us your own story herefree_speech_recordings/Français/2017-11-22/fs_...2236496Tell us your own story hereFertel dyn eigen ferhaal hjirVertel je eigen verhaal hierYour own storyDyn eigen ferhaalJe eigen verhaal
24C9FD6142-A64A-4AD8-B317-3BB833A11667Nederlands4529A976F40-ADF7-4F72-9C2F-9BD4F1AF4DF5nl2017-12-12 10:36:40What would you do with a million euros?What would you do with a million euros?free_speech_recordings/Nederlands/2017-12-12/f...346892What 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?
25C9FD6142-A64A-4AD8-B317-3BB833A11667Frysk453372127E4-AFFE-4BF5-BE7A-FF9D981F4D2Enl2017-12-12 10:42:33What is your word for...What is the word for \"potato\" in your local la...free_speech_recordings/Frysk/2017-12-12/fs_wha...196892What 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...
26A258D9E9-FD32-4A97-A5A1-A4B72ADFC71DNederlands47663B8E61A-433A-433A-924F-0B2D9D6F0353fy2018-04-08 17:37:12What is your word for...What is the word for \"potato\" in your local la...free_speech_recordings/Nederlands/2018-04-08/f...397372What 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...
27A258D9E9-FD32-4A97-A5A1-A4B72ADFC71DStadsfrys47763B8E61A-433A-433A-924F-0B2D9D6F0353fy2018-04-08 17:46:49What would you do with a million euros?What would you do with a million euros?free_speech_recordings/Stadsfrys/2018-04-08/fs...1447372What 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?
288190723c74b4b0bfNederlands500androidSessionId8190723c74b4b0bf57887798554061nl2018-11-18 11:03:50Growing upTell us a story about where you grew up?free_speech_recordings/Nederlands/2018-11-18/f...358193Tell us a story about where you grew up?Fertel wat oer wêrst wei komstVertel iets over waar je bent opgegroeid?Growing upOpgroeieOpgroeien
298190723c74b4b0bfGronings501androidSessionId8190723c74b4b0bf58065148631545nl2018-11-18 11:06:50Growing upTell us a story about where you grew up?free_speech_recordings/Gronings/2018-11-18/fs_...458193Tell us a story about where you grew up?Fertel wat oer wêrst wei komstVertel iets over waar je bent opgegroeid?Growing upOpgroeieOpgroeien
\n", "
" ], "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": [ "
core_freespeechresultcore_picturegameresultcore_predictionquizresultcore_surveyresult
2017-091722141688374413
2017-1078928623641307
2017-11513586701464
2017-12321642997466
2018-01238710146
2018-0211478328
2018-03030413953
2018-04121851524294
2018-050563327127
2018-063268173112
2018-07011814897
2018-081194215114
2018-09011611747
2018-10720010790
2018-11157366759
2018-122736627
2019-015624137100
2019-020634924
2019-030812513
" ], "text/plain": [ "" ] }, "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": [ "
core_freespeechresult2019-01-30T19:40:03.000000000435
core_picturegameresult2019-03-09T00:59:58.00000000047672
core_predictionquizresult2019-03-13T04:21:56.00000000015522
core_surveyresult2019-03-12T14:48:58.0000000008321
" ], "text/plain": [ "" ] }, "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 }