<jupyter_start><jupyter_text>Part I. ETL Pipeline for Pre-Processing the Files  PLEASE RUN THE FOLLOWING CODE FOR PRE-PROCESSING THE FILES  Import Python packages<jupyter_code># Import Python packages 
from decimal import Decimal
import pandas as pd
import cassandra
import re
import os
import glob
import numpy as np
import json
import csv<jupyter_output><empty_output><jupyter_text>Creating list of filepaths to process original event csv data files<jupyter_code># checking your current working directory
print(os.getcwd())

# Get your current folder and subfolder event data
filepath = os.getcwd() + '/event_data'

# Create a for loop to create a list of files and collect each filepath
for root, dirs, files in os.walk(filepath):
    
# join the file path and roots with the subdirectories using glob
    file_path_list = glob.glob(os.path.join(root,'*'))
    #print(file_path_list)<jupyter_output>/Users/trickyetc/Documents/GitHub/data-modelling-with-cassandra<jupyter_text>Processing the files to create the data file csv that will be used for Apache Casssandra tables<jupyter_code># initiating an empty list of rows that will be generated from each file
full_data_rows_list = [] 
    
# for every filepath in the file path list 
for f in file_path_list:

# reading csv file 
    with open(f, 'r', encoding = 'utf8', newline='') as csvfile: 
        # creating a csv reader object 
        csvreader = csv.reader(csvfile) 
        next(csvreader)
        
 # extracting each data row one by one and append it        
        for line in csvreader:
            #print(line)
            full_data_rows_list.append(line) 
            
# uncomment the code below if you would like to get total number of rows 
#print(len(full_data_rows_list))
# uncomment the code below if you would like to check to see what the list of event data rows will look like
#print(full_data_rows_list)

# creating a smaller event data csv file called event_datafile_full csv that will be used to insert data into the \
# Apache Cassandra tables
csv.register_dialect('myDialect', quoting=csv.QUOTE_ALL, skipinitialspace=True)

with open('event_datafile_new.csv', 'w', encoding = 'utf8', newline='') as f:
    writer = csv.writer(f, dialect='myDialect')
    writer.writerow(['artist','firstName','gender','itemInSession','lastName','length',\
                'level','location','sessionId','song','userId'])
    for row in full_data_rows_list:
        if (row[0] == ''):
            continue
        writer.writerow((row[0], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[12], row[13], row[16]))

# check the number of rows in your csv file
with open('event_datafile_new.csv', 'r', encoding = 'utf8') as f:
    print(sum(1 for line in f))<jupyter_output>6821<jupyter_text>Part II. Complete the Apache Cassandra coding portion of your project.   Creating a Cluster<jupyter_code>from cassandra.cluster import Cluster
try:
    # This should make a connection to a Cassandra instance your local machine
    cluster = Cluster(['192.168.99.100'])
    # To establish connection and begin executing queries, need a session
    session = cluster.connect()
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Create Keyspace<jupyter_code>try:
    session.execute("""CREATE  KEYSPACE IF NOT EXISTS udacity 
                       WITH REPLICATION =
                       { 'class' : 'SimpleStrategy', 'replication_factor' : 1 }
                    """)
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Set Keyspace<jupyter_code>try:
    session.set_keyspace('udacity')
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Now we need to create tables to run the following queries. Remember, with Apache Cassandra you model the database tables on the queries you want to run.  Create queries to ask the following three questions of the data  Query 1:  Give me the artist, song title and song's length in the music app history that was heard during<jupyter_code>## sessionId = 338, and itemInSession = 4
try:
    session.execute("""CREATE TABLE IF NOT EXISTS history_by_session (
                       session_id bigint, item_in_session int, artist text, song_title text, length decimal,
                       PRIMARY KEY ((session_id), item_in_session))
                    """)
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Insert data into table `history_by_session`<jupyter_code>file = 'event_datafile_new.csv'

with open(file, encoding = 'utf8') as f:
    csvreader = csv.reader(f)
    next(csvreader) # skip header
    for line in csvreader:
        query = "INSERT INTO history_by_session (session_id, item_in_session, artist, song_title, length) "
        query = query + "VALUES(%s, %s, %s, %s, %s)"
        session.execute(query, (int(line[8]), int(line[3]), line[0], line[9], Decimal(line[5])))<jupyter_output><empty_output><jupyter_text>Do a SELECT to verify that the data have been inserted into `history_by_session`<jupyter_code>try:
    rows = session.execute("""SELECT artist, song_title, length
                              FROM history_by_session
                              WHERE session_id = 338 
                              AND item_in_session = 4""")
    
    df = pd.DataFrame(list(rows), columns=['artist', 'song_title', 'length'])
    print(df.to_string())    
except Exception as e:
    print(e)<jupyter_output>artist                       song_title    length
0  Faithless  Music Matters (<NAME>)  495.3073<jupyter_text>Query 2: Give me only the following: name of artist, song (sorted by itemInSession) and user (first and last name)\<jupyter_code>## for userid = 10, sessionid = 182
try:
    session.execute("""CREATE TABLE IF NOT EXISTS history_by_user_session (
                       user_id bigint, session_id bigint, item_in_session int, artist text, song_title text, 
                       user_first_name text, user_last_name text,
                       PRIMARY KEY ((user_id, session_id), item_in_session))
                       WITH CLUSTERING ORDER BY (item_in_session DESC)
                    """)
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Insert data into table `history_by_user_session`<jupyter_code># We have provided part of the code to set up the CSV file. Please complete the Apache Cassandra code below#
file = 'event_datafile_new.csv'

with open(file, encoding = 'utf8') as f:
    csvreader = csv.reader(f)
    next(csvreader) # skip header
    for line in csvreader:
        query = "INSERT INTO history_by_user_session (user_id, session_id, item_in_session, artist, song_title, " \
                "user_first_name, user_last_name) "
        query = query + "VALUES(%s, %s, %s, %s, %s, %s, %s)"
        session.execute(query, (int(line[10]), int(line[8]), int(line[3]), line[0], line[9], line[1], line[4]))<jupyter_output><empty_output><jupyter_text>Do a SELECT to verify that the data have been inserted into `history_by_user_session`<jupyter_code>try:
    rows = session.execute("""SELECT artist, song_title, user_first_name, user_last_name
                              FROM history_by_user_session
                              WHERE user_id = 10
                              AND session_id = 182""")
    
    df = pd.DataFrame(list(rows), columns=['artist', 'song_title', 'user_first_name', 'user_last_name'])
    print(df.to_string())    
except Exception as e:
    print(e)<jupyter_output>artist                                         song_title user_first_name user_last_name
0      <NAME>  Catch You Baby (<NAME> & Max Sanna Radio...          Sylvie           Cruz
1  <NAME>                                          Kilometer          Sylvie           Cruz
2       Three Drives                                        Greece 2000          Sylvie           Cruz
3   Down To The Bone                                 Keep On Keepin' On          Sylvie           Cruz<jupyter_text>Query 3: Give me every user name (first and last) in my music app history who listened to the song 'All Hands Against His Own'<jupyter_code>try:
    session.execute("""CREATE TABLE IF NOT EXISTS history_by_song_title (
                       user_id bigint, song_title text, user_first_name text, user_last_name text,
                       PRIMARY KEY ((song_title), user_id))
                    """)
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Insert data into table `history_by_song_title`<jupyter_code># We have provided part of the code to set up the CSV file. Please complete the Apache Cassandra code below#
file = 'event_datafile_new.csv'

with open(file, encoding = 'utf8') as f:
    csvreader = csv.reader(f)
    next(csvreader) # skip header
    for line in csvreader:
        query = "INSERT INTO history_by_song_title (user_id, song_title, user_first_name, user_last_name) "
        query = query + "VALUES(%s, %s, %s, %s)"
        session.execute(query, (int(line[10]), line[9], line[1], line[4]))<jupyter_output><empty_output><jupyter_text>Do a SELECT to verify that the data have been inserted into each table<jupyter_code>try:
    rows = session.execute("""SELECT user_id, song_title, user_first_name, user_last_name
                              FROM history_by_song_title
                              WHERE song_title='All Hands Against His Own'
                              """)
    
    df = pd.DataFrame(list(rows), columns=['user_id', 'user_first_name', 'user_last_name', 'song_title'])
    print(df.to_string())    
except Exception as e:
    print(e)<jupyter_output>user_id            user_first_name user_last_name song_title
0       29  All Hands Against His Own     Jacqueline      Lynch
1       80  All Hands Against His Own          Tegan     Levine
2       95  All Hands Against His Own           Sara    Johnson<jupyter_text>Drop the tables before closing out the sessions<jupyter_code>try:
    session.execute("DROP TABLE history_by_session")
    session.execute("DROP TABLE history_by_user_session")
    session.execute("DROP TABLE history_by_song_title" )
except Exception as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Close the session and cluster connection¶<jupyter_code>session.shutdown()
cluster.shutdown()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09059165858389913, 'fraction_numerical': 0.009893307468477207, 'mean_word_length': 3.117811501597444, 'pattern_counts': {'":': 0, '<': 68, '<?xml version=': 0, '>': 68, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 9, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '490269', 'license': 'MIT', 'n_tokens_mistral': 3208, 'n_tokens_neox': 3052, 'n_words': 955, 'path': 'Project_1B_ Project_Template.ipynb', 'repo_name': 'sai-g/data-modelling-with-cassandra', 'size': '10343'}