ETL Extract Transform Load

• Complete the transform_avg_rating() function by grouping by the course_id column, and taking the mean of the rating column.
• Use extract_rating_data() to extract raw ratings data. It takes in as argument the database engine db_engines.
• Use transform_avg_rating() on the raw rating data you've extracted.
• Mencari kesamaan antar user 1 2 3 berdasarkan rating yang di input di sistem

Now that you have a grasp of what's happening in the datacamp_application database, let's go ahead and write up a query for that database.

The goal is to get a feeling for the data in this exercise. You'll get the rating data for three sample users and then use a predefined helper function, print_user_comparison(), to compare the sets of course ids these users rated.

• Complete the connection URI. The database is called datacamp_application. The host is localhost with port 5432. The username is repl and the password is password.
• Select the ratings of users with id: 4387, 18163 and 8770.
• Fill in print_user_comparison() with the three users you selected.

# Complete the connection URI

connection_uri = "postgresql://repl:password@localhost:5432/datacamp_application" 

db_engine = sqlalchemy.create_engine(connection_uri)

# Get user with id 4387

user1 = pd.read_sql("SELECT * FROM rating WHERE user_id=4387", db_engine)

# Get user with id 18163

user2 = pd.read_sql("SELECT * FROM rating WHERE user_id=18163", db_engine)

# Get user with id 8770

user3 = pd.read_sql("SELECT * FROM rating WHERE user_id=8770", db_engine)

# Use the helper function to compare the 3 users

print_user_comparison(user1, user2, user3)

Course id overlap between users:

================================ User 1 and User 2 overlap: {32, 96, 36, 6, 7, 44, 95} User 1 and User 3 overlap: set() User 2 and User 3 overlap: set()

Mencari Average Rating

In this exercise, you'll complete a transformation function transform_avg_rating() that aggregates the rating data using the pandas DataFrame's .groupby() method. The goal is to get a DataFrame with two columns, a course id and its average rating:

course_id	avg_rating
123	4.72
111	4.62
…	…

In this exercise, you'll complete this transformation function, and apply it on raw rating data extracted via the helper function extract_rating_data() which extracts course ratings from the rating table.

• Complete the transform_avg_rating() function by grouping by the course_id column, and taking the mean of the rating column.
• Use extract_rating_data() to extract raw ratings data. It takes in as argument the database engine db_engines.
• Use transform_avg_rating() on the raw rating data you've extracted

# Complete the transformation function

def transform_avg_rating(rating_data):

    # Group by course_id and extract average rating per course

    avg_rating = rating_data.groupby('course_id').rating.mean()

    # Return sorted average ratings per course

    sort_rating = avg_rating.sort_values(ascending=False).reset_index()

    return sort_rating

# Extract the rating data into a DataFrame    

rating_data = extract_rating_data(db_engines)

# Use transform_avg_rating on the extracted data and print results

avg_rating_data = transform_avg_rating(rating_data)

print(avg_rating_data) 

course_id rating 0 46 4.800000 1 23 4.800000 2 96 4.692765 3 56 4.661765 4 24 4.653061 .. ... ... 94 54 4.238095 95 92 4.222222 96 29 4.208333 97 17 4.147059 98 42 4.107570