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

Commentary :

The code performs the following actions:

df_sales_amount = df_receipt.query('not customer_id.str.startswith("Z")', engine='python')
It selects all rows from a DataFrame called df_receipt where the customer_id column does not start with the letter "Z". The resulting DataFrame is saved as df_sales_amount.

df_sales_amount = df_sales_amount.groupby('customer_id').agg({'amount':'sum'}).reset_index()
It groups the df_sales_amount DataFrame by customer_id and calculates the sum of the amount column for each group using the agg method. The resulting DataFrame is saved as df_sales_amount.

df_sales_amount['log_amount'] = np.log10(df_sales_amount['amount'] + 0.5)
It calculates the logarithm (base 10) of the amount column in the df_sales_amount DataFrame, adding 0.5 to the amount values first to avoid taking the logarithm of 0 or negative values. The resulting logarithmic values are saved in a new column called log_amount.

df_sales_amount.head(10)
It prints the first 10 rows of the resulting df_sales_amount DataFrame using the head method.

In summary, the code selects rows from a DataFrame where the customer ID does not start with "Z", groups those rows by customer ID, calculates the sum of sales amounts for each customer, takes the logarithm (base 10) of the sales amounts, and prints the first 10 rows of the resulting DataFrame.

Commentary :

This code performs some data processing on a pandas DataFrame called df_receipt.

First, it filters out any rows where the customer_id column starts with the letter "Z". This is done using the query() method with a condition that uses the str.startswith() method. The engine='python' argument is provided to use the Python parser instead of the default pandas parser for string operations.

The resulting DataFrame is then grouped by the customer_id column using the groupby() method.

The agg() method is used to apply a function to the amount column for each group. In this case, the sum() function is used to calculate the total sales amount for each customer.

The resulting DataFrame is then reset to have a new index using reset_index().

Finally, a new column called log_amount is created by taking the natural logarithm of the amount column plus 0.5 (to avoid taking the logarithm of zero or negative values).

The first 10 rows of the resulting DataFrame are displayed using the head() method.

Commentary :

The code performs the following actions:

df_tmp = df_product.copy()
It creates a copy of a DataFrame called df_product and assigns it to a new variable called df_tmp. The copy is made to avoid modifying the original DataFrame.

df_tmp['unit_profit'] = df_tmp['unit_price'] - df_tmp['unit_cost']
It calculates the profit per unit for each product by subtracting the unit cost from the unit price, and saves the result in a new column called unit_profit. The resulting DataFrame is saved as df_tmp.

df_tmp.head(10)
It prints the first 10 rows of the df_tmp DataFrame using the head method.

In summary, the code creates a copy of a DataFrame, calculates the profit per unit for each product by subtracting the unit cost from the unit price, adds a new column with the profit per unit to the copied DataFrame, and prints the first 10 rows of the resulting DataFrame.

Commentary :

The code performs the following actions:

df_tmp = df_product.copy()
It creates a copy of a DataFrame called df_product and assigns it to a new variable called df_tmp. The copy is made to avoid modifying the original DataFrame.

df_tmp['unit_profit_rate'] = (df_tmp['unit_price'] - df_tmp['unit_cost']) / df_tmp['unit_price']
It calculates the profit rate per unit for each product by dividing the profit (unit price minus unit cost) by the unit price, and saves the result in a new column called unit_profit_rate. The resulting DataFrame is saved as df_tmp.

df_tmp['unit_profit_rate'].mean(skipna=True)
It calculates the mean of the unit_profit_rate column in the df_tmp DataFrame using the mean method, skipping missing values (NaN) if any, and prints the result

In summary, the code creates a copy of a DataFrame, calculates the profit rate per unit for each product by dividing the profit (unit price minus unit cost) by the unit price, adds a new column with the profit rate per unit to the copied DataFrame, calculates the mean of the profit rate column, and prints the resulting mean value.

Commentary :

The code performs the following actions:

df_tmp = df_product[['product_cd', 'unit_price', 'unit_cost']].copy()
It creates a new DataFrame called df_tmp that includes only three columns of interest: product_cd, unit_price, and unit_cost, and makes a copy of these columns from the original df_product DataFrame.

df_tmp['new_price'] = np.floor(df_tmp['unit_cost'] / 0.7)
It calculates a new price for each product by dividing the unit cost by 0.7 (a markup factor of 1.43), rounding down to the nearest integer using the floor method, and saves the result in a new column called new_price. The resulting DataFrame is saved as df_tmp.

df_tmp['new_profit_rate'] = (df_tmp['new_price'] - df_tmp['unit_cost']) / df_tmp['new_price']
It calculates the profit rate per unit for each product using the new price calculated in the previous step, and saves the result in a new column called new_profit_rate. The resulting DataFrame is saved as df_tmp.

df_tmp.head(10)
It prints the first 10 rows of the df_tmp DataFrame using the head method.

In summary, the code creates a new DataFrame with three columns of interest, calculates a new price for each product based on a markup factor of 1.43, calculates the profit rate per unit for each product using the new price, adds two new columns to the copied DataFrame, and prints the first 10 rows of the resulting DataFrame.

Commentary :

The code performs the following actions:

df_tmp = df_product[['product_cd', 'unit_price', 'unit_cost']].copy()
It creates a new DataFrame called df_tmp that includes only three columns of interest: product_cd, unit_price, and unit_cost, and makes a copy of these columns from the original df_product DataFrame.

df_tmp['new_price'] = np.round(df_tmp['unit_cost'] / 0.7)
It calculates a new price for each product by dividing the unit cost by 0.7 (a markup factor of 1.43), rounding to the nearest integer using the round method, and saves the result in a new column called new_price. The resulting DataFrame is saved as df_tmp.

df_tmp['new_profit_rate'] = (df_tmp['new_price'] - df_tmp['unit_cost']) / df_tmp['new_price']
It calculates the profit rate per unit for each product using the new price calculated in the previous step, and saves the result in a new column called new_profit_rate. The resulting DataFrame is saved as df_tmp.

df_tmp.head(10)
It prints the first 10 rows of the df_tmp DataFrame using the head method.

In summary, the code creates a new DataFrame with three columns of interest, calculates a new price for each product based on a markup factor of 1.43 and rounding to the nearest integer, calculates the profit rate per unit for each product using the new price, adds two new columns to the copied DataFrame, and prints the first 10 rows of the resulting DataFrame.

Commentary :

This code manipulats a pandas DataFrame object named df_product.

Here's a breakdown of the code line-by-line:

df_tmp = df_product[['product_cd', 'unit_price', 'unit_cost']].copy(): This line creates a new DataFrame object named df_tmp that is a subset of df_product and includes only the columns "product_cd", "unit_price", and "unit_cost". The .copy() method is used to create a new copy of the DataFrame instead of referencing the original df_product.

df_tmp['new_price'] = np.ceil(df_tmp['unit_cost'] / 0.7): This line calculates a new column called "new_price" in df_tmp, which is the result of dividing the "unit_cost" column by 0.7 and then taking the ceiling of the result using numpy's ceil() function. This calculats a new price based on a profit margin of 30%.

df_tmp['new_profit_rate'] = \ (df_tmp['new_price'] - df_tmp['unit_cost']) / df_tmp['new_price']: This line calculates a new column called "new_profit_rate" in df_tmp, which is the result of subtracting the "unit_cost" column from the "new_price" column and then dividing by the "new_price" column. This calculats the profit rate for the new price.

df_tmp.head(10): This line prints out the first 10 rows of the df_tmp DataFrame. This uses to check that the calculations in the previous lines have been performed correctly.

Commentary :

This code also manipulats a pandas DataFrame object named df_product.

Here's a breakdown of the code line-by-line:

df_tmp = df_tmp = df_product[['product_cd', 'unit_price']].copy(): This line creates a new DataFrame object named df_tmp that is a subset of df_product and includes only the columns "product_cd" and "unit_price". The .copy() method is used to create a new copy of the DataFrame instead of referencing the original df_product.

df_tmp['tax_price'] = np.floor(df_tmp['unit_price'] * 1.1): This line calculates a new column called "tax_price" in df_tmp, which is the result of multiplying the "unit_price" column by 1.1 and then taking the floor of the result using numpy's floor() function. This calculates a new price with a 10% tax added.

df_tmp.head(10): This line prints out the first 10 rows of the df_tmp DataFrame. This uses to check that the calculations in the previous lines have been performed correctly.

Commentary :

This code also manipulates pandas DataFrame objects, df_receipt and df_product.

Here's a breakdown of the code line-by-line:

df_temp = df_receipt.merge(df_product, how='left', on='product_cd').groupby(['customer_id', 'category_major_cd'])['amount'].sum().unstack(): This line first merges df_receipt and df_product on the "product_cd" column using a left join. It then groups the resulting DataFrame by "customer_id" and "category_major_cd", calculates the sum of the "amount" column for each group, and unstacks the resulting Series to create a DataFrame with "customer_id" as the index, "category_major_cd" as the column names, and the sum of the "amount" column as the values.

df_temp = df_temp[df_temp['07'] > 0]: This line filters the df_temp DataFrame to only include rows where the value in the "07" column is greater than 0. This removes customers who have not purchased anything from the "07" category.

df_temp['sum_all'] = df_temp.sum(axis=1): This line calculates a new column in df_temp called "sum_all", which is the sum of all the columns in each row. This calculats the total amount spent by each customer across all categories.

df_temp['sales_rate'] = df_temp['07'] / df_temp['sum_all']: This line calculates a new column in df_temp called "sales_rate", which is the result of dividing the "07" column by the "sum_all" column. This calculates the percentage of a customer's total purchases that are from the "07" category.

df_temp.columns.name = '': This line removes the column name from the df_temp DataFrame.

df_temp = df_temp.reset_index(): This line resets the index of df_temp to create a new column called "index" with a range of integers starting from 0.

df_temp.head(10): This line prints out the first 10 rows of the df_temp DataFrame. This uses to check that the calculations in the previous lines have been performed correctly.

Commentary :

This code is similar to the previous code we discussed, but instead of calculating the number of days between a customer's application date and their purchase date, it calculates the number of elapsed months.

Here's a step-by-step breakdown of the code:

df_tmp = df_receipt[['customer_id', 'sales_ymd']].drop_duplicates(): creates a new DataFrame named df_tmp by selecting only the columns "customer_id" and "sales_ymd" from the df_receipt DataFrame, and then dropping any duplicate rows. The resulting DataFrame will have a unique combination of customer IDs and sales dates.

df_tmp = pd.merge(df_tmp, df_customer[['customer_id', 'application_date']], how='inner', on='customer_id'): performs an inner join on df_tmp and the df_customer DataFrame, selecting only the "customer_id" and "application_date" columns from the df_customer DataFrame. The resulting DataFrame will only contain rows with customer IDs that are present in both df_tmp and df_customer.

df_tmp['sales_ymd'] = pd.to_datetime(df_tmp['sales_ymd'].astype('str')): converts the "sales_ymd" column of df_tmp to a datetime format using the pd.to_datetime() function.

df_tmp['application_date'] = pd.to_datetime(df_tmp['application_date']): converts the "application_date" column of df_tmp to a datetime format using the pd.to_datetime() function.

df_tmp['elapsed_months'] = df_tmp[['sales_ymd', 'application_date']]. \ apply(lambda x: relativedelta(x[0], x[1]).years * 12 + \ relativedelta(x[0], x[1]).months, axis=1): calculates the difference between the "sales_ymd" and "application_date" columns and assigns the result to a new column named "elapsed_months" in df_tmp. This calculation uses the relativedelta function from the dateutil library to compute the difference between two datetime values in terms of years and months. The apply() method applies this function to each row of the "sales_ymd" and "application_date" columns, and returns the total number of elapsed months.

df_tmp.head(10): displays the first 10 rows of df_tmp to verify that the calculations were performed correctly.

Overall, this code is useful for analyzing customer behavior and determining how long it takes for customers to make a purchase after applying for a membership or account, in terms of elapsed months.

Commentary :

This code is similar to the previous examples we discussed, but instead of calculating the number of elapsed days or months between a customer's application date and purchase date, it calculates the number of elapsed years.

Here's a step-by-step breakdown of the code:

df_tmp = df_receipt[['customer_id', 'sales_ymd']].drop_duplicates(): creates a new DataFrame named df_tmp by selecting only the columns "customer_id" and "sales_ymd" from the df_receipt DataFrame, and then dropping any duplicate rows. The resulting DataFrame will have a unique combination of customer IDs and sales dates.

df_tmp = pd.merge(df_tmp, df_customer[['customer_id', 'application_date']], how='inner', on='customer_id'): performs an inner join on df_tmp and the df_customer DataFrame, selecting only the "customer_id" and "application_date" columns from the df_customer DataFrame. The resulting DataFrame will only contain rows with customer IDs that are present in both df_tmp and df_customer.

df_tmp['sales_ymd'] = pd.to_datetime(df_tmp['sales_ymd'].astype('str')): converts the "sales_ymd" column of df_tmp to a datetime format using the pd.to_datetime() function.

df_tmp['application_date'] = pd.to_datetime(df_tmp['application_date']): converts the "application_date" column of df_tmp to a datetime format using the pd.to_datetime() function.

df_tmp['elapsed_years'] = df_tmp[['sales_ymd', 'application_date']]. \ apply(lambda x: relativedelta(x[0], x[1]).years, axis=1): calculates the difference between the "sales_ymd" and "application_date" columns and assigns the result to a new column named "elapsed_years" in df_tmp. This calculation uses the relativedelta function from the dateutil library to compute the difference between two datetime values in terms of years. The apply() method applies this function to each row of the "sales_ymd" and "application_date" columns, and returns the total number of elapsed years.

df_tmp.head(10): displays the first 10 rows of df_tmp to verify that the calculations were performed correctly.

Overall, this code is useful for analyzing customer behavior and determining how long it takes for customers to make a purchase after applying for a membership or account, in terms of elapsed years.

Commentary :

This code calculates the elapsed time in seconds between a customer's application date and purchase date by converting the datetime values to Unix timestamps.

Here's a step-by-step breakdown of the code:

df_tmp = df_receipt[['customer_id', 'sales_ymd']].drop_duplicates(): creates a new DataFrame named df_tmp by selecting only the columns "customer_id" and "sales_ymd" from the df_receipt DataFrame, and then dropping any duplicate rows. The resulting DataFrame will have a unique combination of customer IDs and sales dates.

df_tmp = pd.merge(df_tmp, df_customer[['customer_id', 'application_date']], how='inner', on='customer_id'): performs an inner join on df_tmp and the df_customer DataFrame, selecting only the "customer_id" and "application_date" columns from the df_customer DataFrame. The resulting DataFrame will only contain rows with customer IDs that are present in both df_tmp and df_customer.

df_tmp['sales_ymd'] = pd.to_datetime(df_tmp['sales_ymd'].astype('str')): converts the "sales_ymd" column of df_tmp to a datetime format using the pd.to_datetime() function.

df_tmp['application_date'] = pd.to_datetime(df_tmp['application_date']): converts the "application_date" column of df_tmp to a datetime format using the pd.to_datetime() function.

df_tmp['elapsed_epoch'] = df_tmp['sales_ymd'].view(np.int64) - \ df_tmp['application_date'].view(np.int64): calculates the difference between the "sales_ymd" and "application_date" columns and assigns the result to a new column named "elapsed_epoch" in df_tmp. This calculation uses the view() method to convert the datetime values to an int64 format (Unix timestamp), and then subtracts the two values element-wise to obtain the elapsed time in nanoseconds.

df_tmp['elapsed_epoch'] = df_tmp['elapsed_epoch'] / 10**9: converts the elapsed time from nanoseconds to seconds by dividing the "elapsed_epoch" column by 10^9.

df_tmp.head(10): displays the first 10 rows of df_tmp to verify that the calculations were performed correctly.

Overall, this code is useful for analyzing customer behavior and determining the length of time it takes for customers to make a purchase after applying for a membership or account, in terms of elapsed seconds.

Commentary :

The code is performing the following steps:

A new DataFrame called df_tmp is created by selecting a single column sales_ymd from an existing DataFrame df_receipt, and then making a copy of that column.

The sales_ymd column in df_tmp is then converted to a pandas datetime format using the pd.to_datetime function. This allows the code to work with dates in a more flexible way, such as being able to extract information like the day of the week.

A new column called elapsed_days is created in df_tmp using the apply method. This method applies a lambda function to each row of the sales_ymd column, which in this case returns the day of the week as an integer (with Monday being 0 and Sunday being 6).

Another new column called monday is created in df_tmp using the apply method with another lambda function. This function takes each row in the sales_ymd column, subtracts the number of days elapsed since the beginning of the week (which is obtained from x.weekday()), and returns the resulting date rounded down to the nearest Monday. This effectively groups all the dates in df_tmp into weekly intervals starting on a Monday.

Finally, the head method is called on df_tmp to display the first 10 rows of the resulting DataFrame.

Overall, this code processes a column of sales dates to extract useful information for analysis or aggregation purposes. Specifically, it creates new columns in the DataFrame that represent the day of the week and the start date of the corresponding week, which could be used for grouping and aggregating sales data by week or day of the week.

Commentary :

The code is sampling a subset of rows from a pandas DataFrame called df_customer, and then displaying the first 10 rows of that subset.

Here's what each part of the code does:

df_customer is a pandas DataFrame that contains customer data, presumably with many rows and columns.

The sample method is called on df_customer with the argument frac=0.01. This method randomly selects a fraction of the rows in the DataFrame, where frac represents the fraction of rows to sample. In this case, frac=0.01 means that 1% of the rows in df_customer will be selected at random.

The head method is called on the resulting subset of df_customer, with the argument 10. This method returns the first 10 rows of the subset, which are then displayed.

So overall, this code is selecting a random sample of 1% of the rows from df_customer, and then displaying the first 10 rows of that sample. This could be useful for exploring the data or performing quick analyses on a smaller subset of the data without having to work with the entire dataset.

Commentary :

The code performs the following steps:

train_test_split is a function from the scikit-learn library that randomly splits a dataset into two subsets: a "training" subset and a "testing" subset. In this code, it is applied to a pandas DataFrame called df_customer, with the argument test_size=0.1 specifying that 10% of the data should be reserved for testing, and the argument stratify=df_customer['gender_cd'] specifying that the split should be stratified by the values in the gender_cd column. The function returns two DataFrames, but the first one is ignored (assigned to _, which is a convention in Python for a variable that is not going to be used).

The second DataFrame returned by train_test_split is assigned to a new variable called df_tmp.

The groupby method is called on df_tmp with the argument gender_cd. This method groups the rows of df_tmp by the values in the gender_cd column.

The agg method is called on the result of the groupby operation, with the argument {'customer_id' : 'count'}. This method applies a function (in this case, count) to the customer_id column of each group, and returns a DataFrame with the resulting counts for each group.

The resulting DataFrame is displayed.

So overall, this code randomly splits a pandas DataFrame into training and testing subsets, stratifying the split by the values in a specific column (gender_cd). It then creates a new DataFrame from the testing subset and groups it by the gender_cd column, and calculates the count of unique values of the customer_id column for each group. This can be useful for understanding the distribution of customers by gender in the testing subset of the data.

Commentary :

This code is performing data preprocessing and filtering on a pandas DataFrame object named df_receipt.

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

df_sales_amount = df_receipt.groupby('customer_id').agg({'amount':'sum'}).reset_index(): This line groups the rows of df_receipt by the values in the customer_id column and calculates the sum of the amount column for each group. The resulting DataFrame df_sales_amount has one row per unique customer_id value and two columns: customer_id and amount. The reset_index() method is used to reset the index to integer values after the grouping.

df_sales_amount['log_sum_amount'] = np.log(df_sales_amount['amount'] + 0.5): This line creates a new column in df_sales_amount named log_sum_amount that contains the natural logarithm of the sum of amount for each customer, plus 0.5 to avoid taking the log of 0.

df_sales_amount['log_sum_amount_ss'] = preprocessing.scale(df_sales_amount['log_sum_amount']): This line creates another new column in df_sales_amount named log_sum_amount_ss that contains the scaled values of log_sum_amount using the preprocessing.scale function from the scikit-learn library. This function subtracts the mean and divides by the standard deviation of the input array, resulting in a new array with zero mean and unit variance.

df_sales_amount.query('abs(log_sum_amount_ss) > 3').head(10): This line filters df_sales_amount to only keep the rows where the absolute value of log_sum_amount_ss is greater than 3, and then selects the first 10 rows of the resulting DataFrame. This filter selects customers whose total sales amount is more than 3 standard deviations away from the mean, indicating that they may be outliers. The query method is used to filter the rows based on a string expression, which in this case is 'abs(log_sum_amount_ss) > 3'.

Commentary :

This code is performing data preprocessing and filtering on a pandas DataFrame object named df_receipt.

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

df_sales_amount = df_receipt.query('not customer_id.str.startswith("Z")', engine='python').groupby('customer_id').agg({'amount':'sum'}).reset_index(): This line first filters df_receipt to exclude rows where the customer_id column starts with the letter "Z". The query method is used to filter the rows based on a string expression. The engine='python' argument is used to specify the use of Python engine for the query, which allows for the use of the str.startswith method. The resulting DataFrame is then grouped by customer_id and the sum of the amount column is calculated for each group. The resulting DataFrame df_sales_amount has one row per unique customer_id value and two columns: customer_id and amount. The reset_index() method is used to reset the index to integer values after the grouping.

pct25 = np.percentile(df_sales_amount['amount'], q=25): This line calculates the 25th percentile of the amount column in df_sales_amount using the numpy.percentile function and assigns the result to the variable pct25.

pct75 = np.percentile(df_sales_amount['amount'], q=75): This line calculates the 75th percentile of the amount column in df_sales_amount using the numpy.percentile function and assigns the result to the variable pct75.

iqr = pct75 - pct25: This line calculates the interquartile range (IQR) of the amount column in df_sales_amount by subtracting the 25th percentile (pct25) from the 75th percentile (pct75) and assigns the result to the variable iqr.

amount_low = pct25 - (iqr * 1.5): This line calculates the lower bound of the "normal" range of sales amounts using the formula amount_low = pct25 - (iqr * 1.5). This is commonly known as the lower "whisker" of the box plot.

amount_high = pct75 + (iqr * 1.5): This line calculates the upper bound of the "normal" range of sales amounts using the formula amount_high = pct75 + (iqr * 1.5). This is commonly known as the upper "whisker" of the box plot.

df_sales_amount.query('amount < @amount_low or @amount_high < amount').head(10): This line filters df_sales_amount to only keep the rows where the amount column is less than amount_low or greater than amount_high, and then selects the first 10 rows of the resulting DataFrame. This filter selects customers whose total sales amount falls outside the "normal" range of sales amounts, which is defined as the range between the lower and upper whiskers of the box plot. The query method is used to filter the rows based on a string expression that uses the @ symbol to refer to the Python variable amount_low and amount_high.

Commentary :

This code is used to calculate the number of missing (null) values in each column of a pandas DataFrame df_product. Here is a step-by-step explanation:

df_product: This is the name of the DataFrame being used.

isnull(): This method returns a DataFrame of the same shape as df_product where each element is a boolean value indicating whether the corresponding element in df_product is null or not.

sum(): This method is applied to the boolean DataFrame returned by isnull() and returns the sum of the boolean values for each column. Since boolean values are treated as integers, this effectively counts the number of null values in each column of df_product.

Therefore, df_product.isnull().sum() returns a Series object where the index is the column names of df_product and the values are the number of null values in each column. This can be used to identify which columns have missing values and how many missing values there are in each column.