<jupyter_start><jupyter_text>ETL for Non-Text data of Udemy Course Dataset**** The raw Udemy course dataset includes (1) a course list of business analytics web crapped from Udemy's business analytics webpages, and (2) detailed information of each course web crapped from course webpage.* The course information includes non-text data, such as enrollment numbers and ratings, and text data of course description and instruction introduction.  * This notebook aims to perform the extract, transform, and load (ETL) procedure on the non-text data of the raw dataset. The text data is processed in a seperate notebook. ***<jupyter_code>import pandas as pd
import os.path
import codecs
import re<jupyter_output><empty_output><jupyter_text>1. Process the Dataset of Course List.<jupyter_code>data = pd.read_table("../data/course_list.tsv",encoding='utf-8')
data = data.iloc[:, :-1]
data = data.drop(columns=['link'])
print(data.columns)
print(data.shape)<jupyter_output>Index(['id', 'name', 'seller', 'price', 'original_price', 'lectures', 'hours',
       'level'],
      dtype='object')
(1013, 8)<jupyter_text>Handle Missing data<jupyter_code>data['lectures'].fillna(value="All Levels", inplace=True)
data["price"].replace("Free","0.0", inplace=True)
data["price"] = data["price"].astype(float)
index = data.index[data["original_price"].isna()]
data.loc[index,"original_price"] = pd.Series(data.loc[index]["price"])
data["level"] = data["level"].astype('category')
total_num = data.shape[0]
miss_course = []
for i in range(total_num):
    if not os.path.exists('../data/raw/'+str(i+1)+'_info.txt'):
        miss_course.append(i)
print(miss_course)
data = data.drop(data.index[miss_course])
# data = data.reset_index(drop=True)<jupyter_output>[372, 494, 868, 914]<jupyter_text>2. Process the Dataset Scrapped from Course Webpage<jupyter_code>title = []
enrollment = []
rating = []
num_ratings = []
description = []
five_stars = []
four_stars = []
three_stars = []
two_stars = []
downloadable_resources = []<jupyter_output><empty_output><jupyter_text>Parse variables<jupyter_code>for i in range(total_num):
    fname = '../data/raw/'+str(i+1)+'_info.txt'
    if i not in miss_course:
        with codecs.open(fname,encoding="utf8") as f:
            content = f.readlines()
        title.append(content[1].replace('\n',''))
        enrollment.append(content[3].replace('\n',''))
        rating.append(content[4].replace('\n',''))
        num_ratings.append(content[5].replace('\n',''))
        description.append(content[7].replace('\n','') + " "+content[8].replace('\n','')+ " "+content[9].replace('\n',''))
        five_stars.append(content[10].replace('\n',''))
        four_stars.append(content[11].replace('\n',''))
        three_stars.append(content[12].replace('\n',''))
        two_stars.append(content[13].replace('\n',''))
        try:
            downloadable_resources.append(re.findall(r'\d+', content[17])[0])
        except:
            downloadable_resources.append(0)
        f.close()
def convert_star(d):
    """
    Extract percentage value from a string.
    :param d: A string have percentage.
    :return: A percentage value between 0 and 1.
    """
    tmp = re.findall(r'\d+', d)
    if len(tmp)>0:
        return float(tmp[0])/100
    else:
        return 0
data['enrollment'] = pd.Series(enrollment).replace("NULL","0").replace("","0").astype(int)
data['rating'] = pd.Series(rating).replace("NULL","0").astype(float)
data['num_ratings'] = pd.Series(num_ratings).replace("NULL","0").astype(float)
data['five_stars'] = pd.Series(five_stars).apply(convert_star)
data['four_stars'] = pd.Series(four_stars).apply(convert_star)
data['three_stars'] = pd.Series(three_stars).apply(convert_star)
data['two_stars'] = pd.Series(two_stars).apply(convert_star)
data['description'] = pd.Series(description)
data['downloadable_resources'] = pd.Series(downloadable_resources).astype(int)
data['description'] = pd.Series(description)<jupyter_output><empty_output><jupyter_text>Parse intructor information.<jupyter_code>instructor_no_courses = []
instructor_info = []
def convert_number(d):
    """
    Extract int value from a string.
    :param d: A string with integer.
    :return: The integer value.
    """
    d = d.replace(",", "")
    tmp = re.findall(r'\d+', d)
    if len(tmp)>0:
        return tmp[0]
    else:
        return 0
for i in range(total_num):
    fname = '../data/raw/'+str(i+1)+'_instructor.txt'
    if i not in miss_course:
        with codecs.open(fname,encoding="utf8") as f:
            content = f.readlines()
        instructor_no_courses.append(convert_number(content[3]))
        instructor_info.append(content[6].replace('\n',''))
data['instructor_info'] = pd.Series(instructor_info)
data['instructor_no_courses'] = pd.Series(instructor_no_courses).astype(int)
data = data.drop([1009,1010,1011,1012])
data.head()<jupyter_output><empty_output><jupyter_text>3. Save the Clean Dataset<jupyter_code>data.to_pickle("data.pkl")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1694065757818765, 'fraction_numerical': 0.016840417000801924, 'mean_word_length': 4.862514688601645, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '300367', 'license': 'MIT', 'n_tokens_mistral': 1718, 'n_tokens_neox': 1677, 'n_words': 323, 'path': 'ETL/ETL_nonText.ipynb', 'repo_name': 'leizhipeng/analyze_mooc_course_data', 'size': '4988'}