170 KiB

Raw Permalink Blame History

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

Hi Herbert,

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

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

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

Nanna

In [1]:

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

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

from getpass import getpass

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

db = MySQLdb.connect(host='127.0.0.1', port=3307, user='stimmen', passwd=mysql_password, db='stimmen', charset='utf8')

········

In [2]:

# Kind reminded of the table names
print('\n'.join(
    table
    for table in pandas.read_sql('''show tables;''', db)['Tables_in_stimmen']
    if table.startswith('core_')
))

core_freespeechresult
core_freespeechtheme
core_language
core_picturegameitem
core_picturegameresult
core_predictionquizresult
core_predictionquizresultquestionanswer
core_surveyanswer
core_surveyquestion
core_surveyquestioncategory
core_surveyresult
core_surveyresultquestionanswer

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

In [3]:

def remove_repeating(rows, columns=[0]):
    """Empty cells which are the same as the cell above
    (in the previous row) for the specified columns, to create
    a less redunant and more readable table"""
    return [
        [
            '' if column == column_ and i in columns else column
            for i, (column, column_) in enumerate(zip(row, row_))
        ]
        for row, row_ in zip(rows, [[None] * len(rows[0])] + rows)
    ]

In [4]:

for gametype in ['prediction quiz', 'picture game', 'free speech']:
    demographics = pandas.read_sql('''
        SELECT 
            answer.question_text AS question,
            answer.answer_text AS answer,
            game.id AS game_id,
            game.survey_result_id AS survey_id
        FROM core_{gametype}result AS game
        LEFT JOIN core_surveyresultquestionanswer AS answer
            ON answer.survey_result_id = game.survey_result_id
        WHERE submitted_at > '2017-09-19'
    '''.format(
        gametype=gametype.replace(' ', '')
    ), db)
    
    table = [
        [
            question_text,
            question_answer,
            len(records)
        ]
        for question_text, answers in demographics.groupby('question')
        for question_answer, records in answers.groupby('answer')
    ]

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

    make_table(
        [
            [gametype, '', ''],
            ['question', 'answer', 'count'],
            ['Total participants', '', n_participants],
            ['No survey', '', n_no_survey],
            ['With survey', '', n_with_survey],
            ['', '', '']
        ] + [
            [question, 'total answers', len(answers_)]
            for question, answers_ in demographics.groupby('question')
        ] + [['', '', '']] +
        remove_repeating(sorted(table, key=lambda x: (x[0], -x[2])))
    )
    set_row_style(1, bold=True)
    display(set_row_style(0, bold=True))

prediction quiz
question	answer	count
Total participants		15177
No survey		11829
With survey		3348

What is your age bracket?	total answers	3348
What is your gender?	total answers	3348
Which language are you the most proficient in?	total answers	3348
Which languages do you actively use in your life?	total answers	7848

What is your age bracket?	11-20	1064
	21-30	748
	41-50	424
	51-60	414
	31-40	352
	61-70	239
	71-80	66
	81-90	18
	0-10	16
	91-100	7
What is your gender?	female	1689
	male	1640
	other	19
Which language are you the most proficient in?	Frisian	1740
	Dutch	1487
	Bildts	33
	Stadsfrys	33
	English	17
	Other	10
	German	5
	Amelands	4
	Gronings	3
	Stellingwarfs	3
	Hindeloopers	2
	North Frisian	2
	Ostfriesisches Platt	2
	Turkish	2
	Arabic	1
	Drents	1
	Mandarin	1
	Sranan	1
	Terschellings	1
Which languages do you actively use in your life?	Dutch	2867
	Frisian	2787
	English	1169
	German	335
	City Frisian	173
	French	126
	Other	93
	Bildts	72
	Gronings	31
	Stellingwarfs	26
	Norwegian	22
	Amelands	20
	Hindeloopers	13
	Drents	12
	Arabic	9
	Terschellings	9
	Bahasa Indonesia	8
	Afrikaans	7
	Sranan	7
	Papiamentu	6
	Tarifit	6
	Turkish	6
	Mandarin Chinese	5
	North Frisian	5
	Ostfriesisches Platt	5
	Polish	5
	Niedersächsisch	4
	Schiermonnikoogs	4
	Galician	3
	Ostniederdeutsch	3
	Latgalīšu volūda	2
	Latviešu valoda	2
	Ostfälisch	2
	Sarnami Hindoestani	2
	Westfälisch	2

picture game
question	answer	count
Total participants		41655
No survey		0
With survey		41655

What is your age bracket?	total answers	41655
What is your gender?	total answers	41655
Which language are you the most proficient in?	total answers	41655
Which languages do you actively use in your life?	total answers	103303

What is your age bracket?	21-30	10255
	11-20	9756
	51-60	6126
	41-50	5853
	31-40	5661
	61-70	2679
	71-80	1017
	91-100	146
	0-10	134
	81-90	28
What is your gender?	female	24296
	male	17047
	other	312
Which language are you the most proficient in?	Frisian	20755
	Dutch	17878
	English	993
	Other	702
	Stadsfrys	449
	Bildts	419
	German	235
	Gronings	40
	Amelands	31
	Drents	28
	North Frisian	27
	Turkish	21
	Galician	15
	Hindeloopers	14
	Stellingwarfs	14
	Arabic	13
	Terschellings	11
	Papiamentu	10
Which languages do you actively use in your life?	Dutch	36236
	Frisian	32716
	English	17498
	German	5727
	Other	2769
	City Frisian	2350
	French	2145
	Bildts	903
	Norwegian	819
	Gronings	321
	Stellingwarfs	286
	Amelands	263
	Drents	207
	Bahasa Indonesia	157
	North Frisian	125
	Tarifit	125
	Sranan	120
	Polish	117
	Niedersächsisch	99
	Terschellings	85
	Schiermonnikoogs	39
	Afrikaans	30
	Hindeloopers	30
	Mandarin Chinese	26
	Arabic	22
	Turkish	21
	Ostniederdeutsch	19
	Papiamentu	14
	Sarnami Hindoestani	10
	Westfälisch	10
	Latviešu valoda	9
	Ostfriesisches Platt	5

free speech
question	answer	count
Total participants		381
No survey		0
With survey		381

What is your age bracket?	total answers	381
What is your gender?	total answers	381
Which language are you the most proficient in?	total answers	381
Which languages do you actively use in your life?	total answers	953

What is your age bracket?	11-20	116
	21-30	86
	31-40	51
	41-50	42
	51-60	39
	61-70	18
	71-80	12
	0-10	7
	91-100	6
	81-90	4
What is your gender?	male	198
	female	169
	other	14
Which language are you the most proficient in?	Dutch	177
	Frisian	152
	English	17
	Other	14
	German	6
	Bildts	3
	Stadsfrys	3
	Afrikaans	2
	North Frisian	2
	Amelands	1
	Mandarin	1
	Ostfriesisches Platt	1
	Sarnami Hindoestani	1
	Turkish	1
Which languages do you actively use in your life?	Dutch	303
	Frisian	263
	English	169
	German	46
	Other	29
	City Frisian	24
	French	20
	Gronings	10
	Bahasa Indonesia	7
	Norwegian	7
	Bildts	6
	Sranan	6
	Drents	5
	Polish	5
	Tarifit	5
	Afrikaans	4
	Amelands	4
	Arabic	4
	Hindeloopers	4
	Schiermonnikoogs	4
	Stellingwarfs	4
	Turkish	4
	Niedersächsisch	3
	Sarnami Hindoestani	3
	Terschellings	3
	Galician	2
	Mandarin Chinese	2
	Westfälisch	2
	North Frisian	1
	Ostfriesisches Platt	1
	Ostfälisch	1
	Ostniederdeutsch	1
	Papiamentu	1

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

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

In [5]:

pandas.read_sql('''
    SELECT COUNT(DISTINCT survey_result_id), COUNT(DISTINCT session_id), COUNT(*)
    FROM core_picturegameresult
    WHERE submitted_at > "2017-09-19"
''', db)

Out[5]:

	COUNT(DISTINCT survey_result_id)	COUNT(DISTINCT session_id)	COUNT(*)
0	1930	24695	41655

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

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

There are not so many, these are the ones:

In [6]:

pandas.read_sql('''
    SELECT
        CONCAT(YEAR(result.submitted_at), '-', MONTH(result.submitted_at)) AS 'year-month',
        COUNT(*) AS '# games',
        COUNT(DISTINCT result.survey_result_id) AS '# surveys',
        GROUP_CONCAT(DISTINCT lang.name SEPARATOR ',') AS 'languages'
    FROM core_freespeechresult AS result
    LEFT JOIN core_language as lang
      ON lang.id = result.language_id
    WHERE submitted_at > "2017-09-19"
    AND result.survey_result_id IN (
        SELECT result.survey_result_id
        FROM core_freespeechresult AS result
        WHERE submitted_at > "2017-09-19"
        GROUP BY result.survey_result_id
        HAVING COUNT(DISTINCT result.language_id) > 1
    )
    GROUP BY YEAR(result.submitted_at), MONTH(result.submitted_at)
''', db)

Out[6]:

	year-month	# games	# surveys	languages
0	2017-9	7	2	English,Nederlands,Frysk,Stadsfrys
1	2017-10	7	2	Frysk,Hielpers,Nederlands
2	2017-11	10	4	Nederlands,Frysk,Français,Sranan
3	2017-12	2	1	Frysk,Nederlands
4	2018-4	2	1	Stadsfrys,Nederlands
5	2018-11	2	1	Gronings,Nederlands

The device ids:

In [7]:

pandas.read_sql('''
    SELECT
        DISTINCT survey.device_id
    FROM core_freespeechresult AS result
    LEFT JOIN core_language as lang
      ON lang.id = result.language_id
    LEFT JOIN core_surveyresult as survey
      ON survey.id = result.survey_result_id
    WHERE result.submitted_at > "2017-09-19"
    AND result.survey_result_id IN (
        SELECT result.survey_result_id
        FROM core_freespeechresult AS result
        WHERE result.submitted_at > "2017-09-19"
        GROUP BY result.survey_result_id
        HAVING COUNT(DISTINCT result.language_id) > 1
    )
''', db)

Out[7]:

	device_id
0	738191E5-80E4-4D07-A15E-6B7DE881906A
1	fb28928ef1703592
2	820E203F-1B70-40A8-BD47-04681D07BC9C
3	1aa95d23fddf3827
4	F95F07AE-B524-4256-9171-F7FFA9E49156
5	C8F56303-E912-4BC3-AD51-E1CA8B425AE9
6	B653C16D-BDA8-4A47-BC6A-C3171D89AF71
7	b40fb5e73d431e6b
8	C9FD6142-A64A-4AD8-B317-3BB833A11667
9	A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D
10	8190723c74b4b0bf

The results:

In [8]:

pandas.read_sql('''
    SELECT
        survey.device_id,
        lang.name as language,
        result.*
    FROM core_freespeechresult AS result
    LEFT JOIN core_language as lang
      ON lang.id = result.language_id
    LEFT JOIN core_surveyresult as survey
      ON survey.id = result.survey_result_id
    WHERE result.submitted_at > "2017-09-19"
    AND result.survey_result_id IN (
        SELECT result.survey_result_id
        FROM core_freespeechresult AS result
        WHERE submitted_at > "2017-09-19"
        GROUP BY result.survey_result_id
        HAVING COUNT(DISTINCT result.language_id) > 1
    )
''', db)

Out[8]:

	device_id	language	id	session_id	app_locale	submitted_at	theme_short_text	theme_long_text	recording	language_id	speech_theme_id	survey_result_id	theme_long_text_en	theme_long_text_fy	theme_long_text_nl	theme_short_text_en	theme_short_text_fy	theme_short_text_nl
0	738191E5-80E4-4D07-A15E-6B7DE881906A	Stadsfrys	159	6349CCA2-358F-4AE5-821F-543AD0B9C97A	fy	2017-09-20 21:05:47	What is your word for...	What is the word for "potato" in your local la...	free_speech_recordings/Stadsfrys/2017-09-20/fs...	14	9	1539	What is the word for "potato" in your local la...	Wat is it wurd foar "ierappel" yn dyn taal?	Wat is het woord voor "aardappel" in je taal?	What is your word for...	Wat is dyn wurd foar...	Wat is je woord voor...
1	738191E5-80E4-4D07-A15E-6B7DE881906A	Stadsfrys	160	ACEF45B4-3162-4A5F-B83A-3E39BE140117	fy	2017-09-20 21:09:51	Your own story	Tell us your own story here	free_speech_recordings/Stadsfrys/2017-09-20/fs...	14	3	1539	Tell us your own story here	Fertel dyn eigen ferhaal hjir	Vertel je eigen verhaal hier	Your own story	Dyn eigen ferhaal	Je eigen verhaal
2	738191E5-80E4-4D07-A15E-6B7DE881906A	Frysk	161	ACEF45B4-3162-4A5F-B83A-3E39BE140117	fy	2017-09-20 21:15:01	Read a text 3	Then the sun began to shine, and the traveler ...	free_speech_recordings/Frysk/2017-09-20/fs_rea...	1	8	1539	Then the sun began to shine, and the traveler ...	Dêrnei begûn de sinne fûl te skinen en fuorten...	Daarna begon de zon krachtig te stralen, en hi...	Read a text 3	Foarlêze 3	Voorlezen 3
3	fb28928ef1703592	Frysk	221	androidSessionIdfb28928ef170359258404890608067	nl	2017-09-23 15:05:05	What is your word for...	What is the word for "potato" in your local la...	free_speech_recordings/Frysk/2017-09-23/fs_wha...	1	9	2951	What is the word for "potato" in your local la...	Wat is it wurd foar "ierappel" yn dyn taal?	Wat is het woord voor "aardappel" in je taal?	What is your word for...	Wat is dyn wurd foar...	Wat is je woord voor...
4	fb28928ef1703592	Frysk	222	androidSessionIdfb28928ef170359258618421150746	nl	2017-09-23 15:08:40	Growing up	Tell us a story about where you grew up?	free_speech_recordings/Frysk/2017-09-23/fs_gro...	1	5	2951	Tell us a story about where you grew up?	Fertel wat oer wêrst wei komst	Vertel iets over waar je bent opgegroeid?	Growing up	Opgroeie	Opgroeien
5	fb28928ef1703592	Nederlands	223	androidSessionIdfb28928ef170359258714896361595	nl	2017-09-23 15:10:16	Your own story	Tell us your own story here	free_speech_recordings/Nederlands/2017-09-23/f...	3	3	2951	Tell us your own story here	Fertel dyn eigen ferhaal hjir	Vertel je eigen verhaal hier	Your own story	Dyn eigen ferhaal	Je eigen verhaal
6	fb28928ef1703592	English	224	androidSessionIdfb28928ef170359259137367204559	nl	2017-09-23 15:17:19	Your own story	Tell us your own story here	free_speech_recordings/English/2017-09-23/fs_y...	2	3	2951	Tell us your own story here	Fertel dyn eigen ferhaal hjir	Vertel je eigen verhaal hier	Your own story	Dyn eigen ferhaal	Je eigen verhaal
7	820E203F-1B70-40A8-BD47-04681D07BC9C	Nederlands	333	28F45D4F-71F4-45A3-A9A2-B9813A16EC13	nl	2017-10-12 08:49:53	What is your word for...	What is the word for "potato" in your local la...	free_speech_recordings/Nederlands/2017-10-12/f...	3	9	5477	What is the word for "potato" in your local la...	Wat is it wurd foar "ierappel" yn dyn taal?	Wat is het woord voor "aardappel" in je taal?	What is your word for...	Wat is dyn wurd foar...	Wat is je woord voor...
8	820E203F-1B70-40A8-BD47-04681D07BC9C	Nederlands	334	28F45D4F-71F4-45A3-A9A2-B9813A16EC13	nl	2017-10-12 08:50:49	What would you do with a million euros?	What would you do with a million euros?	free_speech_recordings/Nederlands/2017-10-12/f...	3	4	5477	What would you do with a million euros?	Wat soesto dwaan mei €1.000.000?	Wat zou je doen met €1.000.000?	What would you do with a million euros?	Wat soesto dwaan mei €1.000.000?	Wat zou je doen met €1.000.000?
9	820E203F-1B70-40A8-BD47-04681D07BC9C	Frysk	335	28F45D4F-71F4-45A3-A9A2-B9813A16EC13	nl	2017-10-12 08:51:51	Your own story	Tell us your own story here	free_speech_recordings/Frysk/2017-10-12/fs_you...	1	3	5477	Tell us your own story here	Fertel dyn eigen ferhaal hjir	Vertel je eigen verhaal hier	Your own story	Dyn eigen ferhaal	Je eigen verhaal
10	1aa95d23fddf3827	Frysk	373	androidSessionId1aa95d23fddf3827591709392581151	fy	2017-10-26 11:10:42	What is your word for...	What is the word for "potato" in your local la...	free_speech_recordings/Frysk/2017-10-26/fs_wha...	1	9	6135	What is the word for "potato" in your local la...	Wat is it wurd foar "ierappel" yn dyn taal?	Wat is het woord voor "aardappel" in je taal?	What is your word for...	Wat is dyn wurd foar...	Wat is je woord voor...
11	1aa95d23fddf3827	Frysk	374	androidSessionId1aa95d23fddf3827591856351721095	fy	2017-10-26 11:13:08	Read a text 3	Then the sun began to shine, and the traveler ...	free_speech_recordings/Frysk/2017-10-26/fs_rea...	1	8	6135	Then the sun began to shine, and the traveler ...	Dêrnei begûn de sinne fûl te skinen en fuorten...	Daarna begon de zon krachtig te stralen, en hi...	Read a text 3	Foarlêze 3	Voorlezen 3
12	1aa95d23fddf3827	Hielpers	375	androidSessionId1aa95d23fddf3827592248608182404	fy	2017-10-26 11:19:40	Read a text 3	Then the sun began to shine, and the traveler ...	free_speech_recordings/Hielpers/2017-10-26/fs_...	9	8	6135	Then the sun began to shine, and the traveler ...	Dêrnei begûn de sinne fûl te skinen en fuorten...	Daarna begon de zon krachtig te stralen, en hi...	Read a text 3	Foarlêze 3	Voorlezen 3
13	1aa95d23fddf3827	Frysk	376	androidSessionId1aa95d23fddf3827592854579719673	fy	2017-10-26 11:29:47	Growing up	Tell us a story about where you grew up?	free_speech_recordings/Frysk/2017-10-26/fs_gro...	1	5	6135	Tell us a story about where you grew up?	Fertel wat oer wêrst wei komst	Vertel iets over waar je bent opgegroeid?	Growing up	Opgroeie	Opgroeien
14	F95F07AE-B524-4256-9171-F7FFA9E49156	Frysk	388	70BBF265-BB3B-4B53-8C1E-9B6CA179906C	fy	2017-11-05 11:44:03	Growing up	Tell us a story about where you grew up?	free_speech_recordings/Frysk/2017-11-05/fs_gro...	1	5	6255	Tell us a story about where you grew up?	Fertel wat oer wêrst wei komst	Vertel iets over waar je bent opgegroeid?	Growing up	Opgroeie	Opgroeien
15	F95F07AE-B524-4256-9171-F7FFA9E49156	Frysk	389	70BBF265-BB3B-4B53-8C1E-9B6CA179906C	fy	2017-11-05 11:48:01	Read a text 2	The North Wind blew as hard as he could, but t...	free_speech_recordings/Frysk/2017-11-05/fs_rea...	1	7	6255	The North Wind blew as hard as he could, but t...	De noardewyn begûn út alle macht te blazen, ma...	De noordenwind begon toen uit alle macht te bl...	Read a text 2	Foarlêze 2	Voorlezen 2
16	F95F07AE-B524-4256-9171-F7FFA9E49156	Frysk	390	70BBF265-BB3B-4B53-8C1E-9B6CA179906C	fy	2017-11-05 11:48:56	What is your word for...	What is the word for "potato" in your local la...	free_speech_recordings/Frysk/2017-11-05/fs_wha...	1	9	6255	What is the word for "potato" in your local la...	Wat is it wurd foar "ierappel" yn dyn taal?	Wat is het woord voor "aardappel" in je taal?	What is your word for...	Wat is dyn wurd foar...	Wat is je woord voor...
17	F95F07AE-B524-4256-9171-F7FFA9E49156	Sranan	391	70BBF265-BB3B-4B53-8C1E-9B6CA179906C	fy	2017-11-05 11:52:51	Growing up	Tell us a story about where you grew up?	free_speech_recordings/Sranan/2017-11-05/fs_gr...	16	5	6255	Tell us a story about where you grew up?	Fertel wat oer wêrst wei komst	Vertel iets over waar je bent opgegroeid?	Growing up	Opgroeie	Opgroeien
18	C8F56303-E912-4BC3-AD51-E1CA8B425AE9	Frysk	410	0D2F2AD3-177A-4991-AD5F-55CAF1BFDE35	nl	2017-11-16 13:15:06	What is your word for...	What is the word for "potato" in your local la...	free_speech_recordings/Frysk/2017-11-16/fs_wha...	1	9	6445	What is the word for "potato" in your local la...	Wat is it wurd foar "ierappel" yn dyn taal?	Wat is het woord voor "aardappel" in je taal?	What is your word for...	Wat is dyn wurd foar...	Wat is je woord voor...
19	B653C16D-BDA8-4A47-BC6A-C3171D89AF71	Frysk	411	07E90580-694C-4F9C-A82B-D431D88F6508	nl	2017-11-16 13:16:32	What is your word for...	What is the word for "potato" in your local la...	free_speech_recordings/Frysk/2017-11-16/fs_wha...	1	9	6450	What is the word for "potato" in your local la...	Wat is it wurd foar "ierappel" yn dyn taal?	Wat is het woord voor "aardappel" in je taal?	What is your word for...	Wat is dyn wurd foar...	Wat is je woord voor...
20	B653C16D-BDA8-4A47-BC6A-C3171D89AF71	Nederlands	412	4CB961F7-432A-43EE-B748-41CB18DA6ACC	nl	2017-11-16 13:20:38	Joke	Tell us a joke!	free_speech_recordings/Nederlands/2017-11-16/f...	3	2	6450	Tell us a joke!	Fertel in mopke!	Vertel een mop!	Joke	Mopke	Mop
21	C8F56303-E912-4BC3-AD51-E1CA8B425AE9	Nederlands	414	0D2F2AD3-177A-4991-AD5F-55CAF1BFDE35	nl	2017-11-16 13:21:42	What is your word for...	What is the word for "potato" in your local la...	free_speech_recordings/Nederlands/2017-11-16/f...	3	9	6445	What is the word for "potato" in your local la...	Wat is it wurd foar "ierappel" yn dyn taal?	Wat is het woord voor "aardappel" in je taal?	What is your word for...	Wat is dyn wurd foar...	Wat is je woord voor...
22	b40fb5e73d431e6b	Frysk	426	androidSessionIdb40fb5e73d431e6b136597419729297	nl	2017-11-22 13:14:36	What is your word for...	What is the word for "potato" in your local la...	free_speech_recordings/Frysk/2017-11-22/fs_wha...	1	9	6496	What is the word for "potato" in your local la...	Wat is it wurd foar "ierappel" yn dyn taal?	Wat is het woord voor "aardappel" in je taal?	What is your word for...	Wat is dyn wurd foar...	Wat is je woord voor...
23	b40fb5e73d431e6b	Français	428	androidSessionIdb40fb5e73d431e6b136976454194882	nl	2017-11-22 13:20:56	Your own story	Tell us your own story here	free_speech_recordings/Français/2017-11-22/fs_...	22	3	6496	Tell us your own story here	Fertel dyn eigen ferhaal hjir	Vertel je eigen verhaal hier	Your own story	Dyn eigen ferhaal	Je eigen verhaal
24	C9FD6142-A64A-4AD8-B317-3BB833A11667	Nederlands	452	9A976F40-ADF7-4F72-9C2F-9BD4F1AF4DF5	nl	2017-12-12 10:36:40	What would you do with a million euros?	What would you do with a million euros?	free_speech_recordings/Nederlands/2017-12-12/f...	3	4	6892	What would you do with a million euros?	Wat soesto dwaan mei €1.000.000?	Wat zou je doen met €1.000.000?	What would you do with a million euros?	Wat soesto dwaan mei €1.000.000?	Wat zou je doen met €1.000.000?
25	C9FD6142-A64A-4AD8-B317-3BB833A11667	Frysk	453	372127E4-AFFE-4BF5-BE7A-FF9D981F4D2E	nl	2017-12-12 10:42:33	What is your word for...	What is the word for "potato" in your local la...	free_speech_recordings/Frysk/2017-12-12/fs_wha...	1	9	6892	What is the word for "potato" in your local la...	Wat is it wurd foar "ierappel" yn dyn taal?	Wat is het woord voor "aardappel" in je taal?	What is your word for...	Wat is dyn wurd foar...	Wat is je woord voor...
26	A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D	Nederlands	476	63B8E61A-433A-433A-924F-0B2D9D6F0353	fy	2018-04-08 17:37:12	What is your word for...	What is the word for "potato" in your local la...	free_speech_recordings/Nederlands/2018-04-08/f...	3	9	7372	What is the word for "potato" in your local la...	Wat is it wurd foar "ierappel" yn dyn taal?	Wat is het woord voor "aardappel" in je taal?	What is your word for...	Wat is dyn wurd foar...	Wat is je woord voor...
27	A258D9E9-FD32-4A97-A5A1-A4B72ADFC71D	Stadsfrys	477	63B8E61A-433A-433A-924F-0B2D9D6F0353	fy	2018-04-08 17:46:49	What would you do with a million euros?	What would you do with a million euros?	free_speech_recordings/Stadsfrys/2018-04-08/fs...	14	4	7372	What would you do with a million euros?	Wat soesto dwaan mei €1.000.000?	Wat zou je doen met €1.000.000?	What would you do with a million euros?	Wat soesto dwaan mei €1.000.000?	Wat zou je doen met €1.000.000?
28	8190723c74b4b0bf	Nederlands	500	androidSessionId8190723c74b4b0bf57887798554061	nl	2018-11-18 11:03:50	Growing up	Tell us a story about where you grew up?	free_speech_recordings/Nederlands/2018-11-18/f...	3	5	8193	Tell us a story about where you grew up?	Fertel wat oer wêrst wei komst	Vertel iets over waar je bent opgegroeid?	Growing up	Opgroeie	Opgroeien
29	8190723c74b4b0bf	Gronings	501	androidSessionId8190723c74b4b0bf58065148631545	nl	2018-11-18 11:06:50	Growing up	Tell us a story about where you grew up?	free_speech_recordings/Gronings/2018-11-18/fs_...	4	5	8193	Tell us a story about where you grew up?	Fertel wat oer wêrst wei komst	Vertel iets over waar je bent opgegroeid?	Growing up	Opgroeie	Opgroeien

In [9]:

allcounts = dict()
tables = ['core_freespeechresult', 'core_picturegameresult', 'core_predictionquizresult', 'core_surveyresult']
for table in tables:
    counts = pandas.read_sql('''
        SELECT
            CONCAT(YEAR(result.submitted_at), '-', MONTH(result.submitted_at)) AS 'year-month',
            COUNT(*) AS '# games'
        FROM {table} AS result
        WHERE submitted_at > "2017-09-19"
        GROUP BY YEAR(result.submitted_at), MONTH(result.submitted_at)
    '''.format(table=table), db)
    allcounts[table] = dict(zip(
        ['-'.join('{:02d}'.format(int(d)) for d in date.split('-')) for date in counts['year-month']],
        counts['# games']
    ))
    
dates = sorted({
     date for dates in allcounts.values() for date in dates
})
make_table([[''] + tables] + [
    [date] + [allcounts[table].get(date, 0) for table in tables]
    for date in dates
])

Out[9]:

	core_freespeechresult	core_picturegameresult	core_predictionquizresult	core_surveyresult
2017-09	172	21416	8837	4413
2017-10	78	9286	2364	1307
2017-11	51	3586	701	464
2017-12	32	1642	997	466
2018-01	2	387	101	46
2018-02	1	147	83	28
2018-03	0	304	139	53
2018-04	12	1851	524	294
2018-05	0	563	327	127
2018-06	3	268	173	112
2018-07	0	118	148	97
2018-08	1	194	215	114
2018-09	0	116	117	47
2018-10	7	200	107	90
2018-11	15	736	67	59
2018-12	2	73	66	27
2019-01	5	624	137	100
2019-02	0	63	49	24
2019-03	0	81	25	13

In [10]:

make_table([
    [
        table,
        pandas.read_sql('''
            SELECT MAX(submitted_at) FROM {table}
        '''.format(table=table), db).values[0, 0],
        pandas.read_sql('''
            SELECT COUNT(*) FROM {table}
        '''.format(table=table), db).values[0, 0]
    ]
    for table in pandas.read_sql('''
        SHOW TABLES
    ''', db).values[:, 0]
    if 'submitted_at' in set(pandas.read_sql('''
            DESCRIBE {table}
        '''.format(table=table), db)['Field'])
])

Out[10]:

core_freespeechresult	2019-01-30T19:40:03.000000000	435
core_picturegameresult	2019-03-09T00:59:58.000000000	47672
core_predictionquizresult	2019-03-13T04:21:56.000000000	15522
core_surveyresult	2019-03-12T14:48:58.000000000	8321

170 KiB Raw Permalink Blame History

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

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

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

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

170 KiB

Raw Permalink Blame History