<jupyter_start><jupyter_text>Moodle Database: Educational Data Log Analysis The Moodle LMS  is a free and open-source learning management system written in PHP and distributed under the GNU General Public License. It is used for blended learning, distance education, flipped classroom and other e-learning projects in schools, universities, workplaces and other sectors. With customizable management features, it is used to create private websites with online courses for educators and trainers to achieve learning goals. Moodle allows for extending and tailoring learning environments using community-sourced plugins .In this notebokk we are going to explore the 10 Academy Moodle logs stored in the database together with many other relevant tables.   Table of content1.  Installing the required libraries 2.  Importing the required libraries 3.  Moodle database understanding  4. Data Extraction Transformation and Loading (ETL)  Installing the necessary libraries<jupyter_code>#!pip install ipython-sql
#!pip install sqlalchemy
#!pip install psycopg2<jupyter_output><empty_output><jupyter_text>Importing necessary libraries<jupyter_code>import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import psycopg2
import logging
from IPython.display import display
#allowing connection to the database
%load_ext sql
#ipython-sql
%sql postgresql://bessy:Streetdance53@localhost/moodle
#sqlalchemy
engine = create_engine('postgresql://bessy:Streetdance53@localhost/moodle')<jupyter_output><empty_output><jupyter_text>Moodle database Understanding.Now, let's have a glance of how some of the tables look like.We will consider the following tables;`mdl_logstore_standard_log`,`mdl_context`,`mdl_user`,`mdl_course`,`mdl_modules `,`mdl_course_modules`,`mdl_course_modules_completion`, `mdl_grade_items`,`mdl_grade_grades`,`mdl_grade_categories`,`mdl_grade_items_history`,`mdl_grade_grades_history`,`mdl_grade_categories_history`,`mdl_forum`,`mdl_forum_discussions`,`mdl_forum_posts`. `Table:mdl_logstore_standard_log`<jupyter_code>%%sql
SELECT *FROM mdl_logstore_standard_log LIMIT 3;<jupyter_output>* postgresql://bessy:***@localhost/moodle
3 rows affected.<jupyter_text>`Table: mdl_context`<jupyter_code>%%sql
SELECT * FROM mdl_context LIMIT 3;<jupyter_output>* postgresql://bessy:***@localhost/moodle
3 rows affected.<jupyter_text>`mdl_course`<jupyter_code>%%sql
SELECT * FROM mdl_course LIMIT 3;<jupyter_output>* postgresql://bessy:***@localhost/moodle
3 rows affected.<jupyter_text>`mdl_user`<jupyter_code>%%sql
SELECT * FROM mdl_user LIMIT 3;<jupyter_output>* postgresql://bessy:***@localhost/moodle
3 rows affected.<jupyter_text>`mdl_modules`<jupyter_code>%%sql
SELECT * FROM mdl_modules LIMIT 3;<jupyter_output>* postgresql://bessy:***@localhost/moodle
3 rows affected.<jupyter_text>`mdl_course_modules`<jupyter_code>%%sql
SELECT * FROM mdl_course_modules LIMIT 3;<jupyter_output>* postgresql://bessy:***@localhost/moodle
3 rows affected.<jupyter_text>`mdl_course_modules_completion`<jupyter_code>%%sql
SELECT * FROM mdl_course_modules_completion LIMIT 3<jupyter_output>* postgresql://bessy:***@localhost/moodle
3 rows affected.<jupyter_text>`mdl_grade_grades`<jupyter_code>%%sql
SELECT * FROM mdl_grade_grades LIMIT 3<jupyter_output>* postgresql://bessy:***@localhost/moodle
3 rows affected.<jupyter_text>Number of tables in the database;<jupyter_code>%%sql
SELECT COUNT(*) FROM information_schema.tables<jupyter_output>* postgresql://bessy:***@localhost/moodle
1 rows affected.<jupyter_text>Number of records in the following tables;<jupyter_code>mit = ['mdl_logstore_standard_log', 'mdl_context', 'mdl_user', 'mdl_course', 'mdl_modules' , 'mdl_course_modules', 'mdl_course_modules_completion',
       'mdl_grade_items', 'mdl_grade_grades', 'mdl_grade_categories', 'mdl_grade_items_history', 'mdl_grade_grades_history', 
       'mdl_grade_categories_history', 'mdl_forum', 'mdl_forum_discussions', 'mdl_forum_posts']

# fetches and returns number of records of a given table in a moodle database
def table_count(table):
    count = %sql SELECT COUNT(*) as {table}_count from {table}
    return count

for table in mit:
    display(table_count(table))<jupyter_output>* postgresql://bessy:***@localhost/moodle
1 rows affected.<jupyter_text>Number of quiz submission by time<jupyter_code>%%sql
select  date_part('hour', timestamp with time zone 'epoch' + timefinish * interval '1 second') as hour, count(1)
from mdl_quiz_attempts qa
where qa.preview = 0 and qa.timefinish <> 0
group by date_part('hour', timestamp with time zone 'epoch' + timefinish * interval '1 second')
order by hour
%%sql
SELECT COUNT(id), EXTRACT(HOUR FROM to_timestamp(timecreated)) FROM mdl_logstore_standard_log WHERE action ='submitted' AND component='mod_quiz'
group by EXTRACT(HOUR FROM to_timestamp(timecreated));<jupyter_output>* postgresql://bessy:***@localhost/moodle
24 rows affected.<jupyter_text>Monthly usage time of learners who have confirmed and are not deleted<jupyter_code>%%sql
select extract(month from to_timestamp(mdl_stats_user_monthly.timeend)) as calendar_month,
    count(distinct mdl_stats_user_monthly.userid) as total_users
from mdl_stats_user_monthly
    inner join mdl_role_assignments on mdl_stats_user_monthly.userid = mdl_role_assignments.userid
    inner join mdl_context on mdl_role_assignments.contextid = mdl_context.id
where mdl_stats_user_monthly.stattype = 'activity'
    and mdl_stats_user_monthly.courseid <>1
group by extract(month from to_timestamp(mdl_stats_user_monthly.timeend))
order by extract(month from to_timestamp(mdl_stats_user_monthly.timeend))
%%sql 
SELECT COUNT(lastaccess - firstaccess) AS usagetime, EXTRACT (MONTH FROM to_timestamp(firstaccess)) AS month 
FROM mdl_user WHERE confirmed = 1 AND deleted = 0 GROUP BY EXTRACT (MONTH FROM to_timestamp(firstaccess))<jupyter_output>* postgresql://bessy:***@localhost/moodle
7 rows affected.<jupyter_text>Count of log events per user<jupyter_code>actions = ['loggedin', 'viewed', 'started', 'submitted', 'uploaded', 'updated',  'searched', 
           'answered', 'attempted', 'abandoned']

# fetch and return count of log events of an action per user
def  event_count(action):
    count = %sql SELECT userid, COUNT(action) AS {action}_count FROM mdl_logstore_standard_log WHERE action='{action}' GROUP BY userid limit 5
    return count

for action in actions:
    display(event_count(action))<jupyter_output>* postgresql://bessy:***@localhost/moodle
5 rows affected.<jupyter_text>python class to pull * Overall grade of learners * Number of forum posts<jupyter_code>class PullGrade():
    def __init__(self):
        pass
    
    def open_db(self, **kwargs):
        # extract args, if they are not provided assign a default value
        user = kwargs.get('user', 'briodev')
        password = kwargs.get('password', '<PASSWORD>')
        db = kwargs.get('db', 'moodle')
        
        # make a connection to PostgreSQL
        # use exception to show error message if failed to connect
        try:
            params = dict(user=user, 
                         password=password,
                         host="127.0.0.1",
                         port = "5432",
                         database = db)
            proot = 'postgresql://{user}@{host}:5432/{database}'.format(**params)
            logging.info('Connecting to the PostgreSQL database... using sqlalchemy engine')
            engine = create_engine(proot)
        except (Exception, psycopg2.Error) as error:
            logging.error(r"Error while connecting to PostgreSQL {error}")
            
        return engine
    
    # fetch and return number of forum posts
    def forum_posts(self):
        count = %sql SELECT COUNT(*) from mdl_forum_posts
        return count
    
    # fetch and return overall grade of learners
    def overall_grade(self):
        overall = %sql SELECT userid, round(SUM(finalgrade)/count(*), 2) as overall_grade from mdl_grade_grades WHERE finalgrade is not null group by userid LIMIT 10
        return overall
db = PullGrade()
db.open_db()
#Forum_posts
db.forum_posts()
#Overall grade.
db.overall_grade()<jupyter_output>* postgresql://bessy:***@localhost/moodle
10 rows affected.<jupyter_text>Data Extraction Transformation and Loading (ETL)<jupyter_code>#reading the mdl_logstore_standard_log
log_df = pd.read_sql("select * from mdl_logstore_standard_log", engine)
def top_x(df, percent):
    total_len = df.shape[0]
    top = int((total_len * percent)/100)
    return df.iloc[:top,]<jupyter_output><empty_output><jupyter_text>Login count<jupyter_code>log_df_logged_in = log_df[log_df.action == 'loggedin'][['userid', 'action']]
login_by_user = log_df_logged_in.groupby('userid').count().sort_values('action', ascending=False)
login_by_user.columns = ["login_count"]
top_x(login_by_user, 1)<jupyter_output><empty_output><jupyter_text>Activity count<jupyter_code>activity_log = log_df[['userid', 'action']]
activity_log_by_user = activity_log.groupby('userid').count().sort_values('action', ascending=False)
activity_log_by_user.columns = ['activity_count']
top_x(activity_log_by_user, 1)
log_in_out = log_df[(log_df.action == "loggedin") | (log_df.action == "loggedout")]
user_id =  log_df.userid.unique()

d_times = {}

for user in user_id:
    log_user = log_df[log_df.userid == user].sort_values('timecreated')
    
    d_time = 0 
    isLoggedIn = 0
    loggedIn_timecreated = 0
    
    for i in range(len(log_user)): 
        row = log_user.iloc[i,]
        
        row_next = log_user.iloc[i+1,] if i+1 < len(log_user) else row
        
        if(row.action == "loggedin"): 
            isLoggedIn = 1
            loggedIn_timecreated = row.timecreated

        if( (i+1 == len(log_user)) | ( (row_next.action == "loggedin") & (isLoggedIn == 1) ) ):
            d_time += row.timecreated - loggedIn_timecreated
            isLoggedIn = 0

    d_times[user] = d_time


dedication_time_df = pd.DataFrame({'userid':list(d_times.keys()),
                                  'dedication_time':list(d_times.values())})
dedication_time_df
top_x(dedication_time_df.sort_values('dedication_time', ascending=False), 35)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10428360413589365, 'fraction_numerical': 0.007877892663712457, 'mean_word_length': 5.226854690374004, 'pattern_counts': {'":': 0, '<': 67, '<?xml version=': 0, '>': 66, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, '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': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '2766', 'license': 'MIT', 'n_tokens_mistral': 3437, 'n_tokens_neox': 3289, 'n_words': 792, 'path': 'notebooks/Educational-Data-Log-Analysis.ipynb', 'repo_name': 'Bessy-Mukaria/Educational-Data-Log-Analysis', 'size': '10157'}