Data Science 100 Knocks (Structured Data Processing) – R Part4 (Q61 to Q80)

Commentary :

This code performs the following operations on the df_receipt data frame:

It filters out the rows where the customer_id starts with "Z" using the filter function.

It groups the data by customer_id using the group_by function.

It summarizes the amount column for each customer_id by taking the sum using the summarise function.

It drops the groups created in step 2 using the .groups = "drop" argument.

It creates a new column called log_amount by taking the logarithm of sum_amount plus 0.5, using base 10.

It selects the first 10 rows of the resulting data frame using the slice function.

Overall, this code creates a new column with the logarithm of the total amount spent by each customer in the df_receipt data frame, which is a common transformation used to normalize skewed data.

Commentary :

This code processes a data frame called df_receipt. It does the following steps:

Filters out rows where the customer_id starts with "Z". This is done using the filter() function from the dplyr package with the !grepl() function as a condition.

Groups the remaining rows by customer_id, using the group_by() function from the dplyr package.

Calculates the total amount of each customer's purchases using the sum() function on the amount column. This is done using the summarise() function from the dplyr package.

Adds a new column called log_amount that contains the natural logarithm of the sum_amount column plus 0.5. This is done using the mutate() function from the dplyr package and the log() function from base R.

Finally, it selects the first 10 rows of the resulting data frame using the slice() function from the dplyr package.

Commentary :

This code takes the data frame df_product and creates a new column unit_profit using the mutate() function. The unit_profit column is calculated as the difference between unit_price and unit_cost, which are existing columns in df_product.

The slice() function is then used to show the first 10 rows of the resulting data frame with the new unit_profit column included.

Commentary :

This code is performing some calculations on a dataframe named df_product.

mutate is used to create a new column named unit_profit_rate. This column is calculated by subtracting the unit_cost from the unit_price and then dividing the result by unit_price.

The summarise function is then used to compute the mean of the unit_profit_rate column. The result is assigned to a new column named total_mean.

In summary, this code is calculating the average profit rate for each unit of product sold in df_product.

Commentary :

This code performs the following steps:

It selects only the columns "product_cd", "unit_price", and "unit_cost" from the data frame "df_product".

It creates a new column called "new_price" by dividing the unit cost by 0.7 and truncating the result to the nearest integer. This is an arbitrary calculation intends to lower the price of the product by a certain percentage.

It creates another new column called "new_profit_rate" by subtracting the unit cost from the new price and dividing the result by the new price. This is meant to calculate the profit rate for the new price.

Finally, it selects the first 10 rows of the resulting data frame.

Commentary :

This code is performing some calculations on the product data frame (df_product).

First, it is selecting three columns, "product_cd", "unit_price", and "unit_cost".

Then, it is creating a new column, "new_price", which is calculated by dividing "unit_cost" by 0.7 (i.e., reducing the cost by 30%) and rounding the result to the nearest whole number using the round() function.

Next, it creates a new column, "new_profit_rate", which is calculated by subtracting "unit_cost" from "new_price" and dividing the result by "new_price".

Finally, it selects the first 10 rows of the resulting data frame and displays them.

This code essentially calculates the new price and profit rate for each product if the cost of production is reduced by 30%.

Commentary :

This code manipulates the df_product data frame by adding two new variables new_price and new_profit_rate.

mutate(new_price = ceiling(unit_cost / 0.7)) creates a new column new_price that contains the result of the expression ceiling(unit_cost / 0.7). The ceiling() function rounds the result of the expression unit_cost / 0.7 up to the nearest integer. This results in a new price that is 30% higher than the unit cost.

mutate(new_profit_rate = (new_price - unit_cost) / new_price) creates a new column new_profit_rate that contains the result of the expression (new_price - unit_cost) / new_price. This expression calculates the new profit rate, which is the percentage of the new price that represents profit. This is calculated as the difference between the new price and the unit cost, divided by the new price.

The slice(1:10) function is used to display the first 10 rows of the resulting data frame.

Commentary :

This code uses the cbind function to combine three columns of df_product data frame, i.e., product_cd, unit_price, and tax_price, into a new data frame. The head function is then used to display the first ten rows of this new data frame.

The first column of the new data frame is labeled product_cd and contains the values of the product_cd column from df_product. The second column is labeled df_product$unit_price and contains the values of the unit_price column from df_product.

The third column is labeled tax_price and is created by applying the trunc function to the result of multiplying each value of the unit_price column by 1.1. This calculates the price of the product including a 10% tax.

Note that the use of df_product$ in the column name for the second column is unnecessary as the cbind function already extracts the unit_price column from df_product.

Commentary :

This code is calculating the sales rate of category 07 products for the top 10 customers who have bought the most of those products. Here is a breakdown of what each line does:

inner_join(df_receipt, df_product[c("product_cd","category_major_cd")], by="product_cd"): This joins the df_receipt and df_product data frames based on the product_cd column, and only keeps the product_cd and category_major_cd columns from df_product. The result is a new data frame that has information on the category of each product purchased in df_receipt.

%>% filter(category_major_cd == "07"): This filters the joined data frame from step 1 to only keep rows where the category_major_cd column is equal to "07".

%>% group_by(customer_id) %>% summarise(sum_07 = sum(amount), .groups = "drop"): This groups the filtered data frame from step 2 by customer_id and calculates the sum of the amount column for each group. The result is a new data frame that has the total amount spent on category 07 products by each customer.

%>% inner_join(df_receipt, by="customer_id"): This joins the data frame from step 3 with the original df_receipt data frame based on the customer_id column. The result is a new data frame that has the total amount spent by each customer on all products.

%>% group_by(customer_id) %>% summarise(sum_all=sum(amount), sum_07=max(sum_07), .groups = "drop"): This groups the joined data frame from step 4 by customer_id and calculates the sum of the amount column for each group, as well as the maximum value of the sum_07 column for each group. The result is a new data frame that has the total amount spent by each customer on all products, as well as the maximum amount spent by each customer on category 07 products.

%>% mutate(sales_rate = sum_07 / sum_all): This calculates the sales rate for each customer by dividing the maximum amount spent on category 07 products (sum_07) by the total amount spent on all products (sum_all).

%>% slice(1:10): This keeps only the top 10 rows of the data frame based on the sales rate column. The result is a new data frame that shows the top 10 customers with the highest sales rate for category 07 products, along with their total amount spent on all products and their maximum amount spent on category 07 products.

Commentary :

This code performs the following tasks:

Selects the columns 'customer_id' and 'sales_ymd' from the 'df_receipt' data frame.

Removes duplicated rows while keeping all columns ('distinct').

Joins the resulting data frame with the 'df_customer' data frame by 'customer_id'.

Adds a new column 'elapsed_days' that represents the difference in days between the 'sales_ymd' and 'application_date' columns, converted to integers.

Selects only the 'customer_id', 'sales_ymd', 'application_date', and 'elapsed_days' columns.

Returns the first 10 rows of the resulting data frame.

The inner workings of the code can be broken down into the following steps:

# Select customer_id and sales_ymd columns from df_receipt
df1 <- df_receipt[c("customer_id", "sales_ymd")]

# Remove duplicated rows while keeping all columns
df2 <- distinct(df1, .keep_all = TRUE)

# Join with df_customer on customer_id
df3 <- inner_join(df2, df_customer[c("customer_id", "application_date")], by = "customer_id")

# Add a new column with elapsed days
df4 <- mutate(df3, elapsed_days = as.integer(strptime(as.character(sales_ymd), "%Y%m%d") - strptime(application_date, "%Y%m%d")))

# Select columns and return first 10 rows
df5 <- select(df4, customer_id, sales_ymd, application_date, elapsed_days)
slice(df5, 1:10)

The code uses the dplyr package to perform data manipulations. The distinct function removes duplicated rows based on all columns, and the inner_join function joins two data frames based on a common column. The mutate function creates a new column in a data frame, and the select function selects specific columns from a data frame. Finally, the slice function returns a subset of rows from a data frame.

Commentary :

This code performs the following operations:

df_receipt[c("customer_id", "sales_ymd")] selects only the columns customer_id and sales_ymd from the df_receipt data frame.

distinct(., .keep_all = TRUE) removes duplicate rows while keeping all columns. This ensures that there is only one row for each unique combination of customer_id and sales_ymd.

inner_join(df_customer[c("customer_id", "application_date")], by = "customer_id") joins the result from the previous step with the df_customer data frame on the customer_id column, selecting only the customer_id and application_date columns from df_customer.

mutate(elapsed_months = trunc(time_length( interval( strptime(application_date, "%Y%m%d"), strptime(as.character(sales_ymd), "%Y%m%d") ), "month"))) creates a new column elapsed_months that calculates the number of months between the application_date and sales_ymd dates. This is done by converting the dates to the appropriate format using strptime, calculating the difference using the interval function, and then using time_length to calculate the number of months. The trunc function is used to round down to the nearest integer.

select(customer_id, sales_ymd, application_date, elapsed_months) selects only the columns customer_id, sales_ymd, application_date, and elapsed_months.

slice(1:10) selects only the first 10 rows of the resulting data frame.

Overall, this code is calculating the number of months between the application date and the sales date for each customer, based on their purchase history.

Commentary :

This code performs the following operations:

Extracts two columns, customer_id and sales_ymd, from the df_receipt data frame.

Removes duplicated rows of the above two columns and keeps only the first occurrence of each unique pair of customer_id and sales_ymd.

Joins the resulting data frame with df_customer data frame on the customer_id column.

Computes the elapsed years between the application_date and sales_ymd dates for each customer by creating an interval using interval function and then using time_length function to extract the number of years. The trunc function is used to round down the result to an integer value.

Selects the customer_id, sales_ymd, application_date, and elapsed_years columns.

Finally, selects the first 10 rows of the resulting data frame using the slice function.

In summary, this code computes the elapsed years between a customer's application_date and their sales_ymd date.

Commentary :

This code is processing some data related to customers and their purchase history. Here is an explanation of the code line by line:

df_receipt[c("customer_id", "sales_ymd")]: select the columns "customer_id" and "sales_ymd" from the "df_receipt" dataframe.

distinct(., .keep_all = TRUE): remove duplicate rows while keeping all columns.

inner_join(df_customer[c("customer_id","application_date")], by="customer_id"): join the "df_customer" dataframe using the "customer_id" column.

mutate(elapsed_epoch = as.numeric(strptime(as.character(sales_ymd), "%Y%m%d")) - as.numeric(strptime(application_date, "%Y%m%d"))): create a new column called "elapsed_epoch" that calculates the number of seconds between the "sales_ymd" column and "application_date" column.

select(customer_id, sales_ymd, application_date, elapsed_epoch): select the columns "customer_id", "sales_ymd", "application_date", and "elapsed_epoch" from the joined dataframe.

slice(1:10): select the first 10 rows of the resulting dataframe.

Commentary :

This code calculates the number of elapsed days since the start of the week (Monday) for each sales date in the df_receipt data frame. Here is a breakdown of the code:

df_receipt["sales_ymd"]: This selects the sales_ymd column from the df_receipt data frame.

%>%: This is the pipe operator that passes the result from the previous operation to the next operation.

mutate(monday = as.Date(floor_date( strptime(as.character(sales_ymd), "%Y%m%d") - 1 , unit = "week")) + 1): This creates a new column called monday that represents the Monday of the week for each sales date. It does this by converting the sales_ymd column to a date object, subtracting one day, rounding down to the previous week (with Sunday being the end of the week), and adding one day to make it the start of the week (Monday).

%>%: Another pipe operator.

mutate(elapsed_days = as.integer( as.Date( strptime(as.character(sales_ymd), "%Y%m%d")) - monday)): This creates a new column called elapsed_days that represents the number of days that have elapsed since the start of the week (Monday) for each sales date. It does this by converting the sales_ymd column to a date object, subtracting the Monday of the week (from the monday column), and then converting the result to an integer.

%>%: Another pipe operator.

select(sales_ymd, elapsed_days, monday): This selects the sales_ymd, elapsed_days, and monday columns.

%>%: Another pipe operator.

slice(1:10): This selects the first ten rows of the resulting data frame.

Commentary :

This code selects a random sample of 1% of the rows in the df_customer dataframe using the sample_frac() function from the dplyr package, and then returns the first 10 rows of the resulting dataframe using the head() function.

The sample_frac() function is used to randomly sample a fraction of rows from a dataframe. In this case, the tbl argument specifies the dataframe to sample from, and the size argument specifies the fraction of rows to sample (0.01, or 1%). The resulting dataframe is then passed to the head() function to return the first 10 rows.

Commentary :

This code uses the dplyr package to randomly sample 10% of customers from each gender group and count the number of customers in each sample.

set.seed(71) sets a seed for the random number generator to ensure reproducibility of the sampling.

df_customer %>% takes the df_customer data frame as input and passes it to the next operation using the pipe operator %>%.

group_by(gender_cd) groups the data by the gender code column.

sample_frac(0.1) samples 10% of customers from each gender group. The sample_frac() function is used to randomly sample a fraction of rows from the grouped data frame. The argument 0.1 specifies the fraction to be sampled.

summarise(customer_num = n(), .groups = "drop") calculates the number of customers in each gender group. The n() function counts the number of rows in each group, and renames the resulting column to customer_num. The .groups argument is set to "drop" to remove the grouping information from the output.

The final output will be a data frame with two columns: gender_cd and customer_num. The gender_cd column specifies the gender code, and the customer_num column specifies the number of customers sampled from each gender group.

Commentary :

This code is processing a data frame called df_receipt and doing the following:

Grouping the data frame by customer_id using group_by function.

Calculating the sum of amount for each customer using summarise function and storing it in a new column called sum_amount.

Taking the logarithm of sum_amount column and storing it in a new column called log_sum_amount.

Calculating the z-score of log_sum_amount by subtracting the mean of log_sum_amount from each value and then dividing the result by the standard deviation of log_sum_amount.

Filtering out customers whose log_sum_amount z-score is greater than 3 (i.e., those whose log_sum_amount is more than three standard deviations away from the mean).

Selecting the first 10 rows of the resulting data frame using the slice function.

In summary, the code is identifying customers whose total spending is significantly higher or lower than the average by calculating their z-score and filtering out those whose z-score exceeds a certain threshold.

Commentary :

This code performs the following operations:

Group the df_receipt data frame by customer_id.

Filter out all the rows where the customer_id starts with the letter "Z".

Sum the amount for each customer_id.

Filter out the top and bottom 1.5% of the sum_amount values using the interquartile range (IQR) method.

Select the first 10 rows of the resulting data frame.

The IQR method is a way to detect and remove outliers from a dataset based on the quartiles. In this case, the upper and lower bounds are calculated as follows:

Upper bound: quantile(sum_amount)[4] + 1.5 * (quantile(sum_amount)[4] - quantile(sum_amount)[2])

Lower bound: quantile(sum_amount)[2] - 1.5 * (quantile(sum_amount)[4] - quantile(sum_amount)[2])

Any sum_amount values that fall outside of these bounds are filtered out. The resulting data frame contains the customer_id and sum_amount for the first 10 rows that meet the filter condition.

Commentary :

The code computes the number of missing values in each column of the df_product dataframe using the sapply() function.

sapply() applies a function to each column of the dataframe df_product. Here, the function used is function(x) sum(is.na(x)), which computes the number of missing values (NA) in the input column x. is.na() returns a logical vector of the same length as the input vector with TRUE for any missing value (NA) and FALSE otherwise. sum() is used to sum up the TRUE values in the logical vector, which gives the count of missing values in the column.

The output of the code is a named vector with column names of df_product as names and the corresponding counts of missing values as values.