<jupyter_start><jupyter_text>Preparation for the moodle exercise in SparkIn this jupyter notebook we are going to make the preprocessing part of the dataset that is going to be used in the graded exercise of this week. Getting the dataFollow the procedure that is described below. The dataset can be found here: http://data.greatlanguagegame.com.s3.amazonaws.com/confusion-2014-03-02.tbz2. More specifically do the following:- login using ssh to your cluster:  ```ssh @-ssh.azurehdinsight.net```- download the data: ```wget http://data.greatlanguagegame.com.s3.amazonaws.com/confusion-2014-03-02.tbz2```- extract the data: ```tar -jxvf confusion-2014-03-02.tbz2```- upload the data to HDFS: ```hdfs dfs -put confusion-2014-03-02/confusion-2014-03-02.json /tmp/```After you have uploaded the dataset into the Azure Blob, upload this notebook onto the Spark Jupyter server  (`https://.azurehdinsight.net/jupyter`). More Info about the dataYou can find more information about the dataset (as well as the schema and examples) in this link: http://lars.yencken.org/datasets/languagegame/  Preprocessing commandsIn your newly created notebook run these commands in order to have the dataset into an RDD:<jupyter_code>import json

path = "/tmp/confusion-2014-03-02.json"
raw_data = sc.textFile(path)
dataset = raw_data.map(json.loads).cache()<jupyter_output>Starting Spark application<jupyter_text>After that you will be able to run the queries of the moodle question of this week. The RDD that you have to perform your queries on is the ```dataset``` one. For example, the following command returns one element of the dataset:<jupyter_code>dataset.take(1)<jupyter_output>[{'choices': ['Maori', 'Mandarin', 'Norwegian', 'Tongan'], 'guess': 'Norwegian', 'target': 'Norwegian', 'date': '2013-08-19', 'country': 'AU', 'sample': '48f9c924e0d98c959d8a6f1862b3ce9a'}]<jupyter_text>Instructions:In every query we ask you for three quantities: the query itself, the result of the query as well as the productivity time. That means the development time of each query (time elapsed before you start writing the query, and the time at which the correct, final query is ready). Note that the time part of every question is optional and not graded. In order to make easier the time recording we created two functions that do it automatically. Run the cell below in order to import the functions into the current notebook. Then before each query we will have a ```start_exercise()``` cell that you have to run in order to start time recording. After you have finished your query and you are sure about the answer run the ```finish_exercise()``` one to get the time measurement.<jupyter_code>import time

def start_exercise():
    global last
    last = time.time()
    
def finish_exercise():
    global last
    print("This exercise took {0}s".format(int(time.time()-last)))<jupyter_output><empty_output><jupyter_text>If you want to have a part of the dataset in order to run faster experiments then run the cell below and run your queries against the ```dataset_part``` RDD. However your final answer, the format of your final query and the productivity time should be run/measured against the whole dataset.<jupyter_code>dataset_part = dataset.sample(False, 0.0001).cache()<jupyter_output><empty_output><jupyter_text>Assignment 1Find the number of games where the guessed language is correct (meaning equal to the target one) and that language is Russian.<jupyter_code>start_exercise()
dataset \
    .filter(lambda o: o['guess'] == o['target']) \
    .filter(lambda o: o['target'] == 'Russian') \
    .count()
finish_exercise()<jupyter_output>This exercise took 47s<jupyter_text>Assignment 2Return the number of distinct "target" languages.<jupyter_code>start_exercise()
dataset \
    .map(lambda o: o['target']) \
    .distinct() \
    .count()
finish_exercise()<jupyter_output>This exercise took 15s<jupyter_text>Assignment 3Return the sample IDs (i.e., the *sample* field) of the top three games where the guessed language is correct (equal to the target one) ordered by language (ascending), then by country (ascending), then by date (ascending).<jupyter_code>start_exercise()
dataset \
    .filter(lambda o: o['guess'] == o['target']) \
    .map(lambda o: ((o['target'], o['country'], o['date']), o)) \
    .sortByKey() \
    .map(lambda t: t[1]) \
    .take(3)
finish_exercise()<jupyter_output>This exercise took 95s<jupyter_text>Assignment 4Aggregate all games by country and target language, counting the number of guesses for each group and return the frequencies of the three most frequent country/language combinations.<jupyter_code>start_exercise()
dataset \
    .map(lambda o: ((o['country'], o['target']), 1)) \
    .reduceByKey(lambda i1, i2: i1 + i2) \
    .map(lambda t: (t[1], t[0])) \
    .sortByKey(ascending=False) \
    .take(3)
finish_exercise()<jupyter_output>This exercise took 15s<jupyter_text>Assignment 5Find the overall percentage of correct guesses when the first answer (amongst the array of possible answers) was the correct one.<jupyter_code>start_exercise()
n = dataset \
    .filter(lambda o: o['guess'] == o['target']) \
    .filter(lambda o: o['target'] == o['choices'][0]) \
    .count()
n_tot = dataset.count()
n / n_tot
finish_exercise()<jupyter_output>This exercise took 23s<jupyter_text>Assignment 6Sort the languages by decreasing overall percentage of correct guesses and return the first three languages.<jupyter_code>start_exercise()
dataset \
    .map(lambda o: (o['target'], (1, 1 if o['target'] == o['guess'] else 0))) \
    .reduceByKey(lambda t1, t2: (t1[0] + t2[0], t1[1] + t2[1])) \
    .map(lambda t: (t[1][1]/t[1][0], t[0])) \
    .sortByKey(ascending=False) \
    .take(3)
finish_exercise()<jupyter_output>This exercise took 15s<jupyter_text>Assignment 7Return the number games played on the latest day.<jupyter_code>start_exercise()
latest_day = dataset \
    .map(lambda o: (o['date'], 0)) \
    .sortByKey(False) \
    .take(1)[0][0]
print("Latest day:", latest_day)
dataset \
    .filter(lambda o: o['date'] == latest_day) \
    .count()
finish_exercise()<jupyter_output>This exercise took 55s
{'fraction_non_alphanumeric': 0.11556642216788916, 'fraction_numerical': 0.023308883455582723, 'mean_word_length': 4.928502415458937, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 36, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '587737', 'license': 'MIT', 'n_tokens_mistral': 1984, 'n_tokens_neox': 1875, 'n_words': 679, 'path': 'Exercise06_Spark_Moodle.ipynb', 'repo_name': 'oliverdelacruz/big-data', 'size': '6135'}