Data Science 100 Knocks (Structured Data Processing) – R Part3 (Q41 to Q60)

Commentary :

This code uses the dplyr package in R to manipulate a data frame called df_receipt. Here is a step-by-step explanation of what the code is doing:

df_receipt %>% - This is a pipe operator in R which passes the df_receipt data frame to the next function in the pipeline as its first argument.

group_by(sales_ymd) %>% - This groups the data frame by the sales_ymd column, which contains dates or date-time data. The %>% operator again pipes this grouped data frame to the next function in the pipeline.

summarise(sum_amount = sum(amount), .groups = "drop") %>% - This summarizes the grouped data frame by calculating the sum of the amount column for each unique sales_ymd group. The resulting data frame will have two columns: sales_ymd and sum_amount. The .groups = "drop" argument is used to drop the grouping information added by group_by, since it is no longer needed. The %>% operator again pipes this summarized data frame to the next function in the pipeline.

mutate(lag_ymd = lag(sales_ymd), lag_amount = lag(sum_amount), diff_amount = sum_amount - lag_amount) %>% - This adds three new columns to the summarized data frame. The first two columns, lag_ymd and lag_amount, contain the lagged values of the sales_ymd and sum_amount columns, respectively. The third column, diff_amount, contains the difference between the sum_amount and lag_amount columns. The %>% operator again pipes this modified data frame to the next function in the pipeline.

slice(1:10) - This selects the first 10 rows of the modified data frame. The resulting data frame will have the columns sales_ymd, sum_amount, lag_ymd, lag_amount, and diff_amount, and will contain the first 10 rows of the original df_receipt data frame after it has been grouped, summarized, and modified.

Overall, this code performs some kind of time-series analysis on sales data, calculating the total amount of sales for each date, and then calculating the difference between each day's sales and the previous day's sales. The resulting data frame should give insight into the daily sales trends over time.

Commentary :

This code is also manipulating a data frame called df_receipt using the dplyr package in R. Here is a step-by-step explanation of what the code is doing:

df_sum <-df_receipt %>% group_by(sales_ymd) %>% summarise(sum_amount = sum(amount), .groups = "drop") - This is similar to the first line of code in the previous examples, which groups the df_receipt data frame by the sales_ymd column, calculates the sum of the amount column for each unique sales_ymd group, and stores the resulting data frame in a new variable called df_sum.

for (i in 1:3) { - This initiates a for loop that will iterate 3 times.

col_name_1 <- paste("lag_ymd_", i , sep="_") - This creates a new character string variable called col_name_1 that concatenates the prefix "lag_ymd_" with the current value of i.

col_name_2 <- paste("lag_amount", i , sep="_") - This creates a new character string variable called col_name_2 that concatenates the prefix "lag_amount_" with the current value of i.

df_tmp <- df_sum %>% mutate(!!col_name_1 := lag(sales_ymd, n = i), !!col_name_2 := lag(sum_amount, n = i)) - This creates a new temporary data frame called df_tmp that is created by lagging the sales_ymd and sum_amount columns in the df_sum data frame by i days using the lag() function from dplyr. However, instead of adding new columns with static names like in the previous example, this code dynamically creates column names by using the string variables created in steps 3 and 4. The !! operator is used to unquote the variable names so that they are evaluated as column names.

if (i == 1)　{ df_lag <- df_tmp } - If this is the first iteration of the loop, the df_lag data frame is set equal to df_tmp.

else { df_lag <- cbind(df_lag, df_tmp[c(col_name_1, col_name_2)]) } - If this is not the first iteration of the loop, the df_tmp data frame is appended to the existing df_lag data frame using cbind(). However, instead of selecting specific columns like in the previous example, this code dynamically selects the columns whose names match the values of col_name_1 and col_name_2.

} - The for loop ends here.

df_lag %>% drop_na(everything()) %>% arrange(sales_ymd) %>% slice(1:10) - This takes the df_lag data frame that was created in the loop, drops any rows with missing data using drop_na(), arranges the rows by sales_ymd using arrange(), and then selects the first 10 rows of the resulting data frame using slice().

The purpose of this code is similar to the previous example, which is to create a new data frame called df_lag that contains the lagged values of the sales_ymd and sum_amount columns for each of the previous 3 days. However, instead of hard-coding column names, this code dynamically generates

Commentary :

This code creates a summary of sales amounts by customer gender and age era (i.e., decade of age), using data from two data frames: df_customer and df_receipt.

First, the code selects three columns from df_customer (customer_id, gender_cd, and age) and creates a new variable era by rounding down age to the nearest decade.

Next, it joins the df_receipt data frame by customer_id, creating a new data frame with information about customer gender, age era, and sales amounts.

The data is then grouped by gender_cd and era, and the total sales amount is calculated using summarise().

spread() is used to pivot the data so that there is one row for each era, with columns for total sales amounts for each gender.

Finally, rename() is used to give more descriptive names to the columns representing male, female, and unknown gender.

The result is a data frame called df_sales_summary that summarizes total sales amounts by gender and age era.

Commentary :

This code modifies the df_sales_summary data frame that was created in the previous code block.

gather() function is used to transform the data from wide format to long format by stacking the columns male, female, and unknown into a single column called sum_amount, and creating a new column called gender_cd to store the previous column names (male, female, or unknown).

mutate() function is used to recode the values in the gender_cd column. It uses the case_when() function to create a new column with three possible values: "00" for male, "01" for female, and "99" for unknown gender.

Overall, this code is preparing the df_sales_summary data frame for merging with other data frames, by reshaping it into long format and recoding the gender values to a standard format.

Commentary :

This code creates a new data frame called df_tmp by selecting the customer_id and birth_day columns from the df_customer data frame.

The df_customer["customer_id"] syntax selects the customer_id column as a data frame (not as a vector), which is necessary for combining it with another column using cbind().

The strftime() function is used to format the birth_day column as a character string in the %Y%m%d format, which represents the year, month, and day of birth as a 8-digit number.

colnames() function is used to assign new column names to the data frame, so that the columns are named "customer_id" and "birth_day".

Finally, head() function is used to show the first 10 rows of the df_tmp data frame.

Overall, this code creates a new data frame with two columns that can be used to merge with other data frames based on customer ID or birth date.

Commentary :

This code creates a new data frame called df_tmp by selecting the receipt_no, receipt_sub_no, and sales_ymd columns from the df_receipt data frame, and converting the sales_ymd column from an integer in yyyymmdd format to a POSIXlt class object that represents date and time in R.

The df_receipt[c("receipt_no", "receipt_sub_no")] syntax selects the receipt_no and receipt_sub_no columns as a data frame (not as a vector), which is necessary for combining them with another column using cbind().

as.character() is used to convert the sales_ymd column from an integer to a character vector before passing it to the strptime() function.

The strptime() function is used to convert the character vector sales_ymd to a POSIXlt class object that represents date and time in R. The %Y%m%d argument specifies the format of the character string to be converted.

colnames() function is used to assign new column names to the data frame, so that the columns are named "receipt_no", "receipt_sub_no", and "sales_ymd".

Finally, head() function is used to show the first 10 rows of the df_tmp data frame.

Overall, this code creates a new data frame with three columns that can be used to merge with other data frames based on receipt number, receipt sub-number, or sales date.

Commentary :

This code creates a new data frame df_tmp by combining selected columns from the original data frame df_receipt. Specifically, it selects the columns "receipt_no", "receipt_sub_no", and "sales_epoch" from df_receipt and binds them together with the function cbind().

The third column "sales_epoch" contains the number of seconds since January 1, 1970, known as the Unix epoch. The as.POSIXct() function is then used to convert this numerical value to a POSIXct object, which represents a date and time in R. The origin argument is set to "1970-01-01" to indicate that the epoch starts at midnight on January 1, 1970.

Finally, the function colnames() is used to rename the columns of df_tmp to "receipt_no", "receipt_sub_no", and "sales_ymd", respectively.

The resulting df_tmp data frame contains the same two columns as df_receipt ("receipt_no" and "receipt_sub_no"), plus a new column "sales_ymd" with the converted date and time information from "sales_epoch". The head() function is used to display the first 10 rows of df_tmp.

Commentary :

This code is creating a new data frame named df_tmp by binding three columns together from the df_receipt data frame. The first two columns, receipt_no and receipt_sub_no, are being selected directly from df_receipt.

The third column, sales_year, is being created using the substring() function to extract the first 4 characters from the output of as.POSIXct(), which is being used to convert the sales_epoch column to a date/time format.

The as.POSIXct() function is using the origin parameter to specify that the Unix epoch time (1970-01-01) is being used as the reference point. The substring() function is then used to extract the year from this date/time format.

Finally, colnames() is used to assign the column names "receipt_no", "receipt_sub_no", and "sales_year" to the corresponding columns in the new data frame. The head() function is then used to display the first 10 rows of the new data frame.

Commentary :

This code creates a new data frame df_tmp that contains the columns "receipt_no", "receipt_sub_no", and "sales_ymd". The "sales_ymd" column is derived from the "sales_epoch" column in df_receipt.

Specifically, the substring() function is used to extract the month component from the "sales_epoch" column by specifying the starting position as 6 (which corresponds to the first character of the month component) and the ending position as 7 (which corresponds to the last character of the month component). The resulting character string represents the month of the sales date.

The new data frame df_tmp is created by combining the selected columns from df_receipt with the extracted month component using the cbind() function. Finally, the column names of df_tmp are updated to "receipt_no", "receipt_sub_no", and "sales_ymd".

Overall, this code extracts the month of each sales date from the "sales_epoch" column of df_receipt and adds it as a new column named "sales_ymd" to a new data frame df_tmp.

Commentary :

This code creates a new data frame called df_tmp that has three columns - "receipt_no", "receipt_sub_no", and "sales_day". The "receipt_no" and "receipt_sub_no" columns are copied from the "df_receipt" data frame. The "sales_day" column is created by extracting the day from the "sales_epoch" column of "df_receipt" data frame.

The "sales_epoch" column is first converted to a POSIXct object using the as.POSIXct function with the origin set to "1970-01-01". This is a common approach to converting Unix timestamps, which are measured in seconds since January 1, 1970, into R's datetime format. The substring function is then used to extract the day portion of the datetime object. The substring function takes three arguments - the input string, the starting position of the substring, and the ending position of the substring. In this case, the substring starts at position 9 (which is the day portion of the datetime string) and ends at position 10.

Finally, the column names are updated to "receipt_no", "receipt_sub_no", and "sales_day" using the colnames function. The resulting data frame df_tmp shows the "receipt_no", "receipt_sub_no", and "sales_day" columns for the first 10 rows of data.

Commentary :

This code performs the following operations on the df_receipt dataframe:

Filters out all the rows where the customer_id column starts with the letter "Z".Groups 

the remaining rows by customer_id.

Calculates the sum of the amount column for each customer_id.

Creates a new column sales_flg that is set to 1 if the sum of the amount column is greater than 2000, and 0 otherwise.

Selects the first 10 rows of the resulting dataframe.

The %>% operator is used to chain the operations together, making the code more concise and easier to read.

Here's a step-by-step breakdown of what's happening:

filter(!grepl("^Z", customer_id)): The filter() function removes all rows where the customer_id column starts with the letter "Z". The !grepl() function returns a logical vector indicating whether each element in the customer_id column does not match the pattern ^Z (i.e., does not start with the letter "Z"). The ! operator negates the logical vector, so that filter() keeps only the rows where the vector is TRUE.

group_by(customer_id): The group_by() function groups the remaining rows by customer_id.

summarise(sum_amount=sum(amount), .groups = "drop"): The summarise() function calculates the sum of the amount column for each customer_id, and stores the result in a new column called sum_amount. The .groups = "drop" argument tells summarise() to drop the grouping structure after summarizing the data.

mutate(sales_flg = ifelse(sum_amount > 2000, 1, 0)): The mutate() function creates a new column called sales_flg, which is set to 1 if the sum_amount column is greater than 2000, and 0 otherwise. The ifelse() function is a vectorized version of the standard if...else statement, which returns a vector of the same length as the condition vector, with elements selected from either the yes or no vectors depending on whether the element of the condition vector is TRUE or FALSE.

slice(1:10): The slice() function selects the first 10 rows of the resulting dataframe.

Commentary :

This code performs the following tasks:

Selects the columns "customer_id" and "postal_cd" from the data frame df_customer.

Creates a new column "postal_flg" that is set to 1 if the first 3 characters of "postal_cd" represent a postal code between 100 and 209 (inclusive) and 0 otherwise. The ifelse function is used to perform this check.

Joins this modified df_customer data frame with the df_receipt data frame on the "customer_id" column.

Groups the resulting data frame by the "postal_flg" column.

Calculates the number of unique customer IDs for each value of "postal_flg" using the n_distinct function.

Drops the grouping information using the ".groups = 'drop'" argument.

Returns the resulting data frame with columns "postal_flg" and "customer_cnt".

Overall, this code is calculating the number of customers in df_receipt that have postal codes within the range of 100-209, grouped by whether or not they have a postal code in that range.

Commentary :

This code uses the dplyr package in R to create a new column prefecture_cd in the df_customer data frame that maps prefecture names to prefecture codes for a subset of prefectures in Japan. Specifically, it uses the mutate() function to add a new column to the data frame and the case_when() function to map prefecture names to codes based on a regular expression pattern matching.

Here is a line-by-line explanation of the code:

df_customer %>% starts a pipeline with the df_customer data frame as the input.

mutate(prefecture_cd = creates a new column prefecture_cd in the data frame.

case_when( starts a series of conditions to map prefecture names to codes.

str_extract(address, pattern = "^.*?[都道府県]") == "埼玉県" ~ "11", matches the regular expression pattern "^.*?[都道府県]" to extract the prefecture name from the address column and maps "埼玉県" to "11".

str_extract(address, pattern = "^.*?[都道府県]") == "千葉県" ~ "12", does the same for "千葉県" and maps it to "12".

str_extract(address, pattern = "^.*?[都道府県]") == "東京都" ~ "13", does the same for "東京都" and maps it to "13".

str_extract(address, pattern = "^.*?[都道府県]") == "神奈川県" ~ "14")) does the same for "神奈川県" and maps it to "14".

select(customer_id, address, prefecture_cd) selects only the columns customer_id, address, and prefecture_cd from the data frame.

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

Commentary :

This code performs the following operations:

It takes a dataframe df_receipt.

It groups the rows in df_receipt by customer_id.

For each group of rows with the same customer_id, it calculates the total sum of the amount column using the sum() function, and stores the result in a new column called sum_amount.

It drops the grouping structure using .groups = "drop".

It creates a new column pct_group using the case_when() function, which assigns each row to a quartile based on the value of the sum_amount column. The quartiles are determined using the quantile() function.

Finally, it selects the first 10 rows of the resulting dataframe and stores it in a new dataframe called df_tmp.

For example, if quantile(sum_amount) returns [100, 200, 300, 400], then the resulting pct_group column will have the value "1" for rows where sum_amount is less than 200, "2" for rows where sum_amount is between 200 and 299, "3" for rows where sum_amount is between 300 and 399, and "4" for rows where sum_amount is greater than or equal to 400.

Commentary :

This code is used to calculate the "era" for each customer based on their birth date. Here's what each line does:

Select only the "customer_id", "birth_day", and "age" columns from the df_customer data frame.

Use the "mutate" function to add a new column called "era". The value of this column is calculated by dividing the "age" column by 10, taking the floor (i.e., rounding down to the nearest integer), and then multiplying by 10. This effectively rounds the age down to the nearest multiple of 10, giving an approximate "era" for each customer.

Use the "mutate" function again to adjust the era values. If the era is less than 60, it is left unchanged. If the era is greater than or equal to 60, it is set to 60.

Use the "slice" function to select the first 10 rows of the resulting data frame.

Note that this code uses the "sapply" function to apply the calculation to each row of the data frame. While this works, it can be slow for very large data frames. A more efficient approach would be to use vectorized operations instead.

Commentary :

This code manipulates a data frame called df_customer, which presumably contains information about customers. Here is an explanation of each line:

df_customer[c("customer_id", "gender_cd", "birth_day", "age")]: Selects the columns "customer_id", "gender_cd", "birth_day", and "age" from the data frame df_customer.

%>%: The pipe operator used to chain the data frame to the next operation.

mutate(era = trunc(age / 10) * 10): Adds a new column called "era" to the data frame, which is calculated by taking the age of each customer, dividing by 10, rounding down to the nearest integer, and multiplying by 10. This effectively bins the customers into 10-year age ranges.

mutate(era = case_when( era < 60 ~ formatC(era, width = 2,flag = "0"), era >= 60 ~ formatC(60, width = 2,flag = "0"))): Modifies the "era" column by applying a case_when statement, which converts the "era" values that are less than 60 to a two-digit string with a leading zero (e.g., "10" for ages 10-19), and sets the "era" values that are greater than or equal to 60 to "60".

mutate(gender_era = paste(gender_cd, era, sep = "")): Adds a new column called "gender_era" to the data frame, which is created by concatenating the "gender_cd" and "era" columns using the paste function. The sep = "" argument specifies that there should be no separator between the two values.

slice(1:10): Selects the first 10 rows of the data frame.

Commentary :

This code performs one-hot encoding on the gender_cd column of the df_customer dataframe using the dummyVars() and predict() functions from the caret package.

The dummyVars() function creates a set of dummy variables, representing the categories of the categorical variable specified in the formula. In this case, the formula ~gender_cd specifies that the gender_cd variable should be used to create the dummy variables. The fullRank = TRUE argument specifies that the reference level should be included in the dummy variable encoding.

The predict() function is then used to apply the encoding to the original df_customer dataframe. The resulting encoded dataframe is stored in the dummy_gender variable.

Finally, the head() function is used to display the first three rows of the dummy_gender dataframe, along with the customer_id column of the original df_customer dataframe, which is appended using cbind(). This allows us to see the encoding of the first few customers. The dummy variable encoding for each customer will consist of a 1 in the column corresponding to their gender and a 0 in the column corresponding to the other gender.

Commentary :

This code performs the following steps:

Filter rows in the df_receipt dataframe where the customer_id column does not start with the character "Z". This is done using the filter function from the dplyr package and the grepl function which matches a regular expression pattern to the customer_id column.

Group the resulting rows by customer_id using the group_by function.

Calculate the sum of the amount column for each group using the summarise function from dplyr.

Create a new column called std_amount that contains the standardized sum of amounts for each customer. The scale function is used to standardize the sum amounts by centering the data at the mean (center = TRUE) and scaling it to have a standard deviation of 1 (scale = TRUE).

Finally, the slice function is used to select the first 10 rows of the resulting dataframe, including the customer_id and std_amount columns.