{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# H&M DATA ANALYSIS","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19"}},{"cell_type":"markdown","source":"### Entities and Their Attributes:\n1. Customers\n   - customer_id (Primary Key)\n   - FN\n   - Active\n   - club_member_status\n   - fashion_news_frequency\n   - age\n   - postal_code\n\n2. Articles\n   - article_id (Primary Key)\n   - product_code\n   - prod_name\n   - product_type_no\n   - product_type_name\n   - product_group_name\n   - graphical_appearance_no\n   - graphical_appearance_name\n   - colour_group_code\n   - colour_group_name\n   - perceived_colour_value_id\n   - perceived_colour_value_name\n   - perceived_colour_master_id\n   - perceived_colour_master_name\n   - department_no\n   - department_name\n   - index_code\n   - index_name\n   - index_group_no\n   - index_group_name\n   - section_no\n   - section_name\n   - garment_group_no\n   - garment_group_name\n   - detail_desc\n\n3. Transactions\n   - t_dat (Primary Key)\n   - customer_id (Foreign Key to Customers)\n   - article_id (Foreign Key to Articles)\n   - price\n   - sales_channel_id\n\nRelationships:\n- Customers (customer_id) relates to Transactions (customer_id).\n- Articles (article_id) relates to Transactions (article_id).\n","metadata":{}},{"cell_type":"markdown","source":"# End to End feature & KPI extraction\n**From the Transactions table:**\n1. **Sales Trends:** Analyze sales trends over time using the \"t_dat\" column. Identify peak sales periods and seasonal variations.\n\n2. **Customer Behavior:** Explore how often customers make purchases by analyzing the \"customer_id\" and \"t_dat\" columns. Identify the most active customers.\n\n3. **Price Analysis:** Investigate the distribution of prices and identify any price outliers or anomalies.\n\n4. **Sales Channels:** Compare sales performance between different sales channels (sales_channel_id) to understand which channels are more successful.\n\n**From the Customers table:**\n5. **Customer Demographics:** Examine the distribution of customer ages to understand the age group of your customers.\n\n6. **Club Membership:** Analyze the distribution of club_member_status to see how many customers are part of the club.\n\n7. **Communication Frequency:** Investigate how often customers want to receive fashion news (fashion_news_frequency).\n\n**From the Articles table:**\n8. **Product Analysis:** Analyze the distribution of product types (product_type_name) and their popularity.\n\n9. **Color Insights:** Explore the most common color groups and their perceived values.\n\n10. **Department and Category Analysis:** Investigate which departments and categories have the most articles and are most popular.\n\n11. **Text Analysis:** Perform text analysis on the \"detail_desc\" column to identify key features or attributes described in the articles.\n\n**Across Tables:**\n\n12. **Customer Purchase Patterns:** Analyze the relationship between customer demographics (age) and their purchasing behavior. Do certain age groups prefer specific products or departments?\n\n13. **Customer Segmentation:** Segment customers based on their club membership status, fashion news frequency, and purchase history to understand different customer groups.\n\n14. **Popular Products by Sales Channel:** Identify which products are popular within different sales channels.\n\n15. **Market Basket Analysis:** Explore associations between products that are often purchased together.\n\n16. **Customer Lifetime Value:** Calculate customer lifetime value based on their transaction history, identifying high-value customers.\n\n17. **Anomaly Detection:** Detect unusual transactions, such as very high or low prices, which may indicate errors or fraud.\n\n18. **Geographical Insights:** If you have additional geographical data related to postal codes, you can analyze sales by region.\n\n19. **Time Series Analysis:** Investigate how sales and customer behavior change over time, including seasonality.\n\n20. **Churn Analysis:** Analyze customer churn based on their club membership and fashion news frequency.\n\n\n\n**From the Transactions table:**\n\n1. **Customer Purchase Frequency:** How often do customers make purchases, and are there any patterns or trends in purchase frequency?\n\n2. **Price Distribution:** Analyze the distribution of prices to identify pricing strategies and assess the price sensitivity of customers.\n\n3. **Sales Channel Performance:** Calculate and compare key performance metrics (e.g., revenue, conversion rate) for different sales channels.\n\n4. **Seasonal Trends:** Identify and analyze any seasonal patterns or trends in sales data. Are there specific months or seasons with higher sales?\n\n**From the Customers table:**\n\n5. **Customer Segmentation:** Segment customers into groups based on demographics (age), club membership status, or other criteria. Analyze the behavior and preferences of each segment.\n\n6. **Customer Acquisition Analysis:** Determine how customers are acquired and assess the effectiveness of marketing and customer acquisition strategies.\n\n7. **Geographic Insights:** If you have geographical data, explore the distribution of customers by region, and assess regional variations in customer behavior.\n\n**From the Articles table:**\n\n8. **Product Association:** Use market basket analysis to identify products frequently bought together. This can inform product bundling or cross-selling strategies.\n\n9. **Color Preferences:** Analyze whether certain color groups or perceived color values are more popular among customers for specific product types.\n\n10. **Inventory Management:** Examine the stock levels of articles to identify overstock or understock situations.\n\n**Across Tables:**\n\n11. **Customer Lifetime Value (CLV):** Calculate CLV for different customer segments and analyze which customer groups contribute the most to revenue.\n\n12. **Marketing Campaign Effectiveness:** Assess the impact of marketing campaigns on customer behavior and sales.\n\n13. **Customer Churn Analysis:** Identify reasons for customer churn (if available in the data) and develop strategies to retain customers.\n\n14. **Customer Feedback Analysis:** If you have customer feedback data, perform sentiment analysis to understand customer satisfaction and areas for improvement.\n\n15. **Product Lifecycle Analysis:** Determine the lifecycle stage of each product and assess its impact on sales.\n\n16. **Promotions and Discounts:** Analyze the effectiveness of promotions and discounts in driving sales and customer acquisition.\n\n17. **Profit Margin Analysis:** Calculate profit margins for articles and assess the profitability of different product types.\n\n18. **Customer Retention:** Track customer retention over time and identify factors influencing customer loyalty.\n\n19. **Inventory Turnover:** Calculate inventory turnover ratios to optimize inventory management.\n\n20. **Recommendation Engine:** Develop a recommendation system based on customer purchase history to increase cross-selling and upselling.\n\n\n\n**From the Transactions Table:**\n\n1. **Customer Loyalty Analysis:** Are there patterns indicating that customers who participate in the loyalty club (club_member_status) tend to make more frequent and higher-value purchases? How does this impact the company's overall revenue?\n\n2. **Pricing Strategy Effectiveness:** Examine how changes in pricing (price) impact transaction volume. Are there optimal price points for certain articles, and do price changes lead to increased or decreased sales?\n\n3. **Channel-Specific Trends:** Investigate whether customers exhibit different behavior on different sales channels (sales_channel_id). Are certain products more popular online than in physical stores? Are there regional variations in channel preferences?\n\n4. **Customer Age and Spending:** Analyze the relationship between customer age and spending. Do older customers tend to spend more, or is there no clear correlation? Are there differences in spending by age group?\n\n**From the Customers Table:**\n\n5. **Customer Acquisition Cost (CAC):** Calculate the cost of acquiring customers, considering factors such as marketing expenses and discounts. Which customer acquisition channels are the most cost-effective?\n\n6. **Segmented Marketing:** Based on club_member_status and fashion_news_frequency, develop personalized marketing strategies. For example, send exclusive offers to club members or tailor email frequency for different customer groups.\n\n7. **Geographic Insights:** If postal_code data includes geographic coordinates, analyze spatial patterns. Are there clusters of high-value customers in specific regions? Can localized marketing strategies be implemented?\n\n**From the Articles Table:**\n\n8. **Inventory Management:** Perform demand forecasting for articles. Identify articles with low inventory levels and high demand. This can help optimize restocking decisions and reduce stockouts.\n\n9. **Product Category Performance:** Categorize articles into different product types (product_type_name). Assess the sales performance of product categories and identify any trends or seasonality within categories.\n\n10. **Color Preferences by Product:** Investigate whether certain color groups or perceived color values are more popular for specific product types. For example, do customers prefer dark colors for underwear and light colors for outerwear?\n\n**Across Tables:**\n\n11. **Customer Lifetime Value (CLV) by Product Type:** Calculate CLV for different customer segments based on their purchase history. Do customers who purchase certain product types have a higher CLV than others?\n\n12. **Marketing Campaign Impact:** Analyze how specific marketing campaigns or promotions affect customer behavior. For instance, determine if a recent campaign led to an increase in the purchase frequency of a specific customer segment.\n\n13. **Customer Feedback Analysis:** If customer feedback or reviews are available, conduct sentiment analysis to identify positive and negative sentiment regarding articles and their impact on sales and customer satisfaction.\n\n14. **Product Performance Over Time:** Examine the lifecycle of products by analyzing when they were introduced and how their sales have evolved. This can help in making decisions about discontinuing or reviving products.\n\n15. **Recommendation Engine Effectiveness:** Assess how effective the recommendation system is in increasing cross-selling and upselling. Evaluate whether customers are responding positively to recommended products.\n\n\n","metadata":{}},{"cell_type":"markdown","source":"# Hypothesis Testing","metadata":{}},{"cell_type":"markdown","source":"1. Hypothesis: Customers who receive fashion news regularly (higher 'fashion_news_frequency') tend to make more frequent purchases or have higher CLV.\n   - Testing: Perform a t-test or ANOVA to compare the mean purchase frequencies of customers with different fashion news frequencies.\n   -Conclusion : Does fashion news influence sales significantly.\n\n2. Descriptive: 1. Prices for products in sales channel 1 are higher on average compared to sales channel 2.\n   - 2. Discount season pattern. Check for price of product during its peak sales vs rest of the year.\n   - 3. Price pattern on YoY basis for each product. Check sales during lowest price point and highest price point. What is the %age amount of discount\n   that incites growth in sales? Are there any patterns that hit low sales even in their most discounted state? Are there any patterns that hit highest sales\n   even at their highest price point? Reasons. (Box Plot)\n\n3. Hypothesis: There is no significant difference in purchase frequency between the first half and second half of the year.\n   - Testing: Perform a paired t-test to compare the purchase frequencies in the two halves of the year. (with caveats)\n\n6. Hypothesis: Highest selling colors in each product category and is it significantly higher than others YoY basis.\n   - Testing: Conduct ANOVA to compare the perceived color values of products across different departments.\n8. Hypothesis: Customers in different age groups have significantly different purchase patterns in terms of product types.\n   - Testing: Perform chi-squared tests to examine the association between age groups and product types.\n\n9. Hypothesis: Club members (club_member_status = 'ACTIVE') have a higher customer lifetime value (CLV) compared to non-club members.\n   - Testing: Conduct a t-test or ANOVA to compare CLV between club members and non-club members.\n\n10. Hypothesis: A certain age group or gender has a color preference.\n    - Testing: Perform chi-squared tests to compare color preferences in these two departments.\n\n15. Hypothesis: The graphical appearance/perceived color (graphical_appearance_name) influences the sale of a certain product.\n- Testing: Perform chi-squared tests to examine the association between graphical appearance and color group preferences.\n\n17. Hypothesis: A certain product is selling significantly more on a particular channel.\n- Testing: Perform chi-squared tests to assess the association between sales channel and product types.\n\n19. Hypothesis: There is a significant difference in purchase frequency between different departments.\nTesting: Use ANOVA to compare purchase frequencies across various departments.","metadata":{}},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:37:15.172476Z","iopub.execute_input":"2023-11-07T06:37:15.172862Z","iopub.status.idle":"2023-11-07T06:37:15.636096Z","shell.execute_reply.started":"2023-11-07T06:37:15.172831Z","shell.execute_reply":"2023-11-07T06:37:15.634708Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = pd.read_csv('/kaggle/input/h-and-m-personalized-fashion-recommendations/articles.csv')\ndf_customers = pd.read_csv('/kaggle/input/h-and-m-personalized-fashion-recommendations/customers.csv')\ndf_transaction_train = pd.read_csv('/kaggle/input/h-and-m-personalized-fashion-recommendations/transactions_train.csv')","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:37:15.638290Z","iopub.execute_input":"2023-11-07T06:37:15.638779Z","iopub.status.idle":"2023-11-07T06:38:44.836553Z","shell.execute_reply.started":"2023-11-07T06:37:15.638742Z","shell.execute_reply":"2023-11-07T06:38:44.835253Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# customers = pd.read_csv('/kaggle/working/customer_.csv')","metadata":{"execution":{"iopub.status.busy":"2023-11-07T07:07:12.443079Z","iopub.execute_input":"2023-11-07T07:07:12.443525Z","iopub.status.idle":"2023-11-07T07:07:12.567534Z","shell.execute_reply.started":"2023-11-07T07:07:12.443492Z","shell.execute_reply":"2023-11-07T07:07:12.566076Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# customers = np.random.choice(df_customers['customer_id'],100000)\ncustomers = pd.read_csv('/kaggle/input/h-and-m-dataset/customer_.csv')\ncustomers = customers['0']\ndf_customers = df_customers[df_customers['customer_id'].isin(customers)]\ndf_transaction_train = df_transaction_train[df_transaction_train['customer_id'].isin(customers)]\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:56:22.191017Z","iopub.execute_input":"2023-11-07T06:56:22.191440Z","iopub.status.idle":"2023-11-07T06:56:23.163486Z","shell.execute_reply.started":"2023-11-07T06:56:22.191405Z","shell.execute_reply":"2023-11-07T06:56:23.162201Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merged_df = df_transaction_train.merge(df, on='article_id', validate='many_to_one')\nmerged_df = merged_df.merge(df_customers,on='customer_id',validate='many_to_one')","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:39:00.276013Z","iopub.execute_input":"2023-11-07T06:39:00.276573Z","iopub.status.idle":"2023-11-07T06:39:17.143724Z","shell.execute_reply.started":"2023-11-07T06:39:00.276529Z","shell.execute_reply":"2023-11-07T06:39:17.141782Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:54:45.189958Z","iopub.execute_input":"2023-11-07T06:54:45.190436Z","iopub.status.idle":"2023-11-07T06:54:45.722538Z","shell.execute_reply.started":"2023-11-07T06:54:45.190402Z","shell.execute_reply":"2023-11-07T06:54:45.721235Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Data Snapshot\n\n*Make a customer segment based on Price/Amount Transactions. Customer Lifetime Value.*\n\n    --> Check average transcation value for each customer for each year/month basis.\n    --> Different Segments : Monetary Facet, Seasonal.\n*Feature Engineering:* *Check the price fluctuations for each product on YoY basis take average of the lowest prices and then reduce the lowest price point by 15% and accept that as the cost price of a product.*\n\n1. Total Unqiue Customers (YoY)\n2. Total Unique Product Items: (YoY)\n    1. By Gender\n    2. By Color\n    3. By Category\n    4. By Department etc.\n    \n3. Sales Trend : Total Transactions By Value (YoY) and Total Number of Transactions (YoY).\n\n4. Trend By Product (YoY) : For each year, extract top 50 selling products and check their patterns. ( Filter by different parameters )\n    \"For example, check top 50 products in 2018 and check their pattern throughout future. Repeat this step for each discrete  \n    year available. Same can be done quarterly/monthly/season(summer, winter, etc) basis. \n    **Feature Engineering to create a new column to state, which product lines up best with which season.\"\n\n5. YoY Pattern of Unique Count of Customers based on: \n    1. Location (ZIP Code)) : Check which areas got an increasing/decreasing trend on YoY basis in count of customers.\n    2. Age : Check which age group of customers got an increasing/decreasing trend on YoY basis.\n    3. Gender : Check which gender of customers got an increasing/decreasing trend on YoY basis.\n    4. Segment : Which cluster/type got an increasing/decreasing trend on YoY basis.\n    \n6. Sales Channel trend on a YoY basis. (2019, 2020 were lockdown periods. Whichever channel of sales spiked during that time, we will assume that it is the online sales channel.\n    1. Sales by value for each year through a channel.\n    2. Which sales channel is preferred mode by which customer segment (zip code, spending, age, gender)? Stacked Bar Chart.\n    3. Sales channel preference based on product type, category, group etc. \n\n\n*Aman Originals*:\n\n##### Create a customer segmentation table, based on frequency and monetary values. Add different columns from different tables such as their communication frequency, their membership status, zip code, age, gender, preferred sales channel by count, by product category/group etc..., identify family/non-family persons based on their age, gender and their purchase pattern.\n\n1. Customer Behavior: Explore how often customers make purchases by analyzing the \"customer_id\" and \"t_dat\" columns. Identify the most active customers.\n    1. What are the most frequent customers on monthly/YoY basis? Create customer bins based on their frequency and segment them.\n    2. What is the average spend of each customer segment?\n    3. What is the average age in each of the customer segment?\n    etc.\n    \n2. Price Analysis: Investigate the distribution of prices and identify any price outliers or anomalies.\n    1. What is the trend of prices for different products on YoY basis?\n    2. For each product, find its peak sales during the year and compare its prices at that time to its average during the year. Do this on YoY/monthly/... level.\n    3. Analyse Customer Behaviour based on point 2 data.\n    \n3. Communication Frequency: Investigate how often customers want to receive fashion news (fashion_news_frequency).\n    1. Communcation Frquency by Age Group/Gender/Customer Segments (ZIP Code, spending, frequency, age, gender) etc.\n    2. Analyze spending habits and patterns between customers for each communciation frequency. Refer customer segments from point 1. \n    \n4. Color Insights: Explore the most common color groups and their perceived values.\n    1. Color by Age/Gender/Customer Segment.\n    2. Color by Product Levels.\n    3. Color trend YoY basis.\n    4. Color by Price.\n    \n5. Department and Category Analysis: Investigate which departments and categories have the most articles and are most popular.\n    1. Highest Number of products in per group. (By Product Group, Index Group Name etc.) (To be done during coding)*\n    2. Category wise spends based on sales channels.\n    \n6. Market Basket Analysis: Explore associations between products that are often purchased together.\n    1. What are the most popular package bundles? Filter them across age, gender, customer segments etc","metadata":{}},{"cell_type":"code","source":"df_transaction_train['price'].describe()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The segmentation of customers into \"Low-Value,\" \"Medium-Value,\" and \"High-Value\" segments was determined based on the distribution of the transaction price data, as described by the provided statistics:\n\n1. **Minimum Value (Min):** The minimum price (2.033898e-04) represents the lowest transaction amount observed in the dataset.\n\n2. **25th Percentile (Q1):** The 25th percentile (1.596610e-02) is the value below which 25% of the data falls. It serves as the lower threshold for the \"Low-Value Customer\" segment.\n\n3. **50th Percentile (Median):** The 50th percentile (2.540678e-02) is also known as the median and represents the middle value in the dataset. It serves as a reference point for segmentation.\n\n4. **75th Percentile (Q3):** The 75th percentile (3.388136e-02) is the value below which 75% of the data falls. It serves as the upper threshold for the \"Medium-Value Customer\" segment.\n\n5. **Maximum Value (Max):** The maximum price (5.915254e-01) represents the highest transaction amount observed in the dataset.\n\nBased on these percentiles, the segmentation was defined as follows:\n\n- \"Low-Value Customers\" are those whose average transaction price is below the 25th percentile, indicating that they tend to make smaller purchases.\n\n- \"Medium-Value Customers\" are customers whose average transaction price falls between the 25th and 75th percentiles, signifying moderate purchase amounts.\n\n- \"High-Value Customers\" are those whose average transaction price exceeds the 75th percentile, implying that they make larger and more significant purchases.\n","metadata":{}},{"cell_type":"code","source":"import pandas as pd\n\n# Calculate the quartiles for \"price\" in the df_transaction_train DataFrame\nq1 = merged_df['price'].quantile(0.25)\nq2 = merged_df['price'].quantile(0.50)\nq3 = merged_df['price'].quantile(0.75)\n\n# Define segmentation thresholds\nlow_value_threshold = q1\nmedium_value_threshold = q2\nhigh_value_threshold = q3\n\n# Create a function to assign a Monetary Facet segment based on the price\ndef assign_monetary_segment(price):\n    if price <= low_value_threshold:\n        return \"Low-Value Customer\"\n    elif price <= medium_value_threshold:\n        return \"Medium-Value Customer\"\n    else:\n        return \"High-Value Customer\"\n\n# Apply the segmentation function to the df_transaction_train DataFrame\nmerged_df['Monetary_Segment'] = merged_df['price'].apply(assign_monetary_segment)\n\n# Display the resulting DataFrame with the Monetary Facet segments\nprint(merged_df[['customer_id', 'price', 'Monetary_Segment']])\n\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:39:28.580484Z","iopub.execute_input":"2023-11-07T06:39:28.580910Z","iopub.status.idle":"2023-11-07T06:39:29.992712Z","shell.execute_reply.started":"2023-11-07T06:39:28.580872Z","shell.execute_reply":"2023-11-07T06:39:29.991369Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\nimport plotly.express as px\n\n\n\n# Extract the year from the transaction date\nmerged_df['t_dat']=pd.to_datetime(merged_df['t_dat'],errors=\"coerce\")\nmerged_df['year'] = merged_df['t_dat'].dt.year\n\n# Group the data by year and calculate the total unique customers for each year\nunique_customers_yoy = merged_df.groupby('year')['customer_id'].nunique().reset_index()\n\n# Calculate the YoY change in the number of unique customers\nunique_customers_yoy['yoy_change'] = unique_customers_yoy['customer_id'].pct_change()\n\n# Create a bar chart for total unique customers\nfig1 = px.bar(unique_customers_yoy, x='year', y='customer_id', title='Total Unique Customers (YoY)')\nfig1.update_xaxes(title_text='Year')\nfig1.update_yaxes(title_text='Total Unique Customers')\n\n# Create a line chart for YoY change\nfig2 = px.line(unique_customers_yoy, x='year', y='yoy_change', title='YoY Change in Unique Customers')\nfig2.update_xaxes(title_text='Year')\nfig2.update_yaxes(title_text='YoY Change (%)')\n\n# Display the charts\nfig1.show()\nfig2.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:39:55.267060Z","iopub.execute_input":"2023-11-07T06:39:55.268288Z","iopub.status.idle":"2023-11-07T06:40:00.150149Z","shell.execute_reply.started":"2023-11-07T06:39:55.268231Z","shell.execute_reply":"2023-11-07T06:40:00.148994Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\nimport plotly.express as px\n\n\n\n\n# Sort the DataFrame by year and customer_id\nmerged_df['t_dat']=pd.to_datetime(merged_df['t_dat'],errors=\"coerce\")\nmerged_df['year'] = merged_df['t_dat'].dt.year\n\nmerged_df.sort_values(by=['year', 'customer_id'], inplace=True)\n\n# Initialize variables to keep track of previous year's customers\nprevious_year_customers = set()\n\n# Create lists to store the results for plotting\nyears = []\nnew_customers_count = []\nlost_customers_count = []\n\n# Iterate through each year\nfor year in merged_df['year'].unique():\n    current_year_customers = set(merged_df[merged_df['year'] == year]['customer_id'])\n    \n    # Calculate new customers (present in the current year but not in the previous year)\n    new_customers = current_year_customers - previous_year_customers\n    \n    # Calculate lost customers (present in the previous year but not in the current year)\n    lost_customers = previous_year_customers - current_year_customers\n    \n    # Update previous_year_customers for the next iteration\n    previous_year_customers = current_year_customers\n    \n    # Store the results for plotting\n    years.append(year)\n    new_customers_count.append(len(new_customers))\n    lost_customers_count.append(len(lost_customers))\n\n# Create a DataFrame for plotting\nplot_data = pd.DataFrame({'Year': years, 'New Customers': new_customers_count, 'Lost Customers': lost_customers_count})\n\n# Create a bar chart using Plotly Express\nfig = px.bar(\n    plot_data,\n    x='Year',\n    y=['New Customers', 'Lost Customers'],\n    title='New and Lost Customers Over the Years',\n    labels={'value': 'Count'},\n)\n\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:40:15.053349Z","iopub.execute_input":"2023-11-07T06:40:15.053931Z","iopub.status.idle":"2023-11-07T06:40:20.451220Z","shell.execute_reply.started":"2023-11-07T06:40:15.053894Z","shell.execute_reply":"2023-11-07T06:40:20.449912Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_data.head()","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:41:32.504354Z","iopub.execute_input":"2023-11-07T06:41:32.505646Z","iopub.status.idle":"2023-11-07T06:41:32.517847Z","shell.execute_reply.started":"2023-11-07T06:41:32.505597Z","shell.execute_reply":"2023-11-07T06:41:32.516658Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The provided data appears to represent a company's customer-related statistics over a three-year period (2018, 2019, and 2020). Here's how to interpret the results:\n\n- In 2018, the company had 40,995 total customers, with no new customers acquired during that year.\n\n- In 2019, the company acquired 37,665 new customers, bringing the total customer count to 68,733 by the end of the year. However, the company also lost 9,927 customers during this period.\n\n- In 2020, the company acquired 18,723 new customers, resulting in a total of 60,680 customers. Unfortunately, they also lost 26,776 customers during this year.\n\nThe data suggests that the company experienced customer acquisition and customer loss in the years 2019 and 2020. In 2019, the company acquired a significant number of new customers but also experienced customer attrition. By 2020, despite acquiring new customers, they faced a substantial loss of customers, resulting in a lower total customer count compared to the previous year.\n\nIt's essential for the company to analyze the reasons for customer losses and to develop strategies to retain and grow their customer base in the future.","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:40:43.652204Z","iopub.execute_input":"2023-11-07T06:40:43.652647Z","iopub.status.idle":"2023-11-07T06:40:43.666851Z","shell.execute_reply.started":"2023-11-07T06:40:43.652611Z","shell.execute_reply":"2023-11-07T06:40:43.665463Z"}}},{"cell_type":"code","source":"import pandas as pd\nimport plotly.express as px\n\n# Sort the DataFrame by year and customer_id\nmerged_df.sort_values(by=['year', 'customer_id'], inplace=True)\n\n# Initialize variables to keep track of previous year's customers\nprevious_year_customers = set()\n\n# Create lists to store the results for plotting\nyears = []\nnew_customers_count = []\nlost_customers_count = []\ntotal_customers_count = []\n\n# Iterate through each year\nfor year in merged_df['year'].unique():\n    current_year_customers = set(merged_df[merged_df['year'] == year]['customer_id'])\n    \n    # Calculate new customers (present in the current year but not in the previous year)\n    new_customers = current_year_customers - previous_year_customers\n    \n    # Calculate lost customers (present in the previous year but not in the current year)\n    lost_customers = previous_year_customers - current_year_customers\n    \n    # Update previous_year_customers for the next iteration\n    previous_year_customers = current_year_customers\n    \n    # Store the results for plotting\n    years.append(year)\n    new_customers_count.append(len(new_customers))\n    lost_customers_count.append(len(lost_customers))\n    \n    # Calculate the total customers for the current year\n    total_customers_count.append(len(current_year_customers))\n\n# Create a DataFrame for plotting\nplot_data = pd.DataFrame({'Year': years, 'New Customers': new_customers_count, 'Lost Customers': lost_customers_count, 'Total Customers': total_customers_count})\n\n# Create a bar chart using Plotly Express\nfig = px.bar(\n    plot_data,\n    x='Year',\n    y=['New Customers', 'Lost Customers', 'Total Customers'],\n    title='New, Lost, and Total Customers Over the Years',\n    labels={'value': 'Count'},\n)\n\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:42:24.527641Z","iopub.execute_input":"2023-11-07T06:42:24.528160Z","iopub.status.idle":"2023-11-07T06:42:27.396822Z","shell.execute_reply.started":"2023-11-07T06:42:24.528124Z","shell.execute_reply":"2023-11-07T06:42:27.395856Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_data","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:43:11.179156Z","iopub.execute_input":"2023-11-07T06:43:11.179623Z","iopub.status.idle":"2023-11-07T06:43:11.192434Z","shell.execute_reply.started":"2023-11-07T06:43:11.179586Z","shell.execute_reply":"2023-11-07T06:43:11.190955Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The provided data represents customer statistics over a three-year period (2018, 2019, and 2020). Here's how to interpret these results:\n\n- In 2018, the company had 40,995 total customers, with no customer losses reported during that year.\n\n- In 2019, the company acquired 37,665 new customers, bringing the total customer count to 68,733 by the end of the year. However, 9,927 customers were lost during the same year.\n\n- In 2020, the company acquired 18,723 new customers, resulting in a total of 60,680 customers. Unfortunately, they lost 26,776 customers during this year.\n\nHere's a breakdown of the changes in each year:\n\n- In 2019, the company experienced significant growth in its customer base due to the acquisition of new customers, but this growth was partially offset by customer losses.\n\n- In 2020, the company continued to acquire new customers, but it also experienced substantial customer losses, resulting in a decrease in the total customer count compared to the previous year.\n\nThe data indicates that the company had strong customer acquisition efforts but also faced challenges in retaining existing customers, leading to fluctuations in the total customer count over the three-year period. This suggests a need to focus on customer retention strategies to maintain and grow the customer base effectively.","metadata":{}},{"cell_type":"code","source":"# Stock","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:46:41.369927Z","iopub.execute_input":"2023-11-07T06:46:41.370405Z","iopub.status.idle":"2023-11-07T06:46:41.375795Z","shell.execute_reply.started":"2023-11-07T06:46:41.370364Z","shell.execute_reply":"2023-11-07T06:46:41.374416Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unique_items_by_color = merged_df.groupby(['colour_group_name', merged_df['t_dat'].dt.year])['article_id'].nunique()\nunique_items_by_category = merged_df.groupby(['product_type_name', merged_df['t_dat'].dt.year])['article_id'].nunique()\nunique_items_by_department = merged_df.groupby(['department_name', merged_df['t_dat'].dt.year])['article_id'].nunique()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:44:46.897846Z","iopub.execute_input":"2023-11-07T06:44:46.898294Z","iopub.status.idle":"2023-11-07T06:44:50.322583Z","shell.execute_reply.started":"2023-11-07T06:44:46.898259Z","shell.execute_reply":"2023-11-07T06:44:50.321224Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n\n# Unique items by color\nfig_color = px.bar(unique_items_by_color.reset_index(), x='t_dat', y='article_id', color='colour_group_name', title='Unique Items by Color (YoY)')\n\n# Unique items by category\nfig_category = px.bar(unique_items_by_category.reset_index(), x='t_dat', y='article_id', color='product_type_name', title='Unique Items by Category (YoY)')\n\n# Unique items by department\nfig_department = px.bar(unique_items_by_department.reset_index(), x='t_dat', y='article_id', color='department_name', title='Unique Items by Department (YoY)')\n\n\nfig_color.show()\nfig_category.show()\nfig_department.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:44:54.566085Z","iopub.execute_input":"2023-11-07T06:44:54.566550Z","iopub.status.idle":"2023-11-07T06:44:56.921134Z","shell.execute_reply.started":"2023-11-07T06:44:54.566512Z","shell.execute_reply":"2023-11-07T06:44:56.919968Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n\n\n# Extract the year from the 't_dat' column\nmerged_df['Year'] = merged_df['t_dat'].dt.year\n\n# Group by year and calculate the total number of transactions and total transaction value\nannual_transactions = merged_df.groupby('Year').agg(\n    Total_Transactions=pd.NamedAgg(column='article_id', aggfunc='count'),\n    Total_Value=pd.NamedAgg(column='price', aggfunc='sum')\n).reset_index()\n\n# Create an interactive line chart for the total transactions by value and number of transactions\nfig = px.line(annual_transactions, x='Year', y=['Total_Transactions', 'Total_Value'],\n              labels={'Total_Transactions': 'Total Number of Transactions', 'Total_Value': 'Total Transaction Value'},\n              title='Sales Trend: Total Transactions (YoY) and Total Value (YoY)')\n\n# Show the plot\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:49:02.948009Z","iopub.execute_input":"2023-11-07T06:49:02.948470Z","iopub.status.idle":"2023-11-07T06:49:03.354300Z","shell.execute_reply.started":"2023-11-07T06:49:02.948437Z","shell.execute_reply":"2023-11-07T06:49:03.352885Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n\n# Initialize a list to store top 50 products for each year\ntop_50_products_yoy = []\n\n# Extract unique years from the transaction data\nunique_years = merged_df['Year'].unique()\n\n# Iterate through each year and perform analysis\nfor year in unique_years:\n    # Filter data for the current year\n    data_year = merged_df[merged_df['Year'] == year]\n\n    # Group by product and calculate total sales or transaction count\n    top_products = data_year.groupby('article_id').size().reset_index(name='transaction_count')\n    \n    # Select the top 50 selling products for the current year\n    top_50_products = top_products.nlargest(50, 'transaction_count')\n\n    # Append the results to the list\n    top_50_products_yoy.append((year, top_50_products))\n\n# Create an interactive line chart to visualize YOY trends\nfig = px.line()\nfor year, top_products in top_50_products_yoy:\n    # Combine article_id and transaction_count for details\n    details = top_products.apply(lambda row: f'Article ID: {row[\"article_id\"]}, Transactions: {row[\"transaction_count\"]}', axis=1)\n    \n    fig.add_scatter(x=[year] * len(top_products), y=top_products['transaction_count'],\n                    mode='markers+lines', name=f'Top 50 Products in {year}', text=details, hoverinfo='text')\n\nfig.update_layout(\n    title='Top 50 Selling Products Year-over-Year',\n    xaxis_title='Year',\n    yaxis_title='Transaction Count'\n)\n\n# Show the interactive plot\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:49:15.609395Z","iopub.execute_input":"2023-11-07T06:49:15.609797Z","iopub.status.idle":"2023-11-07T06:49:16.889789Z","shell.execute_reply.started":"2023-11-07T06:49:15.609754Z","shell.execute_reply":"2023-11-07T06:49:16.888550Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unique_customer_count_ = merged_df.groupby(['postal_code','year'])['customer_id'].nunique()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:51:10.365811Z","iopub.execute_input":"2023-11-07T06:51:10.366279Z","iopub.status.idle":"2023-11-07T06:51:12.001040Z","shell.execute_reply.started":"2023-11-07T06:51:10.366240Z","shell.execute_reply":"2023-11-07T06:51:11.999298Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unique_customer_count_ = unique_customer_count_.sort_values(ascending=False).reset_index()","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:51:13.533799Z","iopub.execute_input":"2023-11-07T06:51:13.534229Z","iopub.status.idle":"2023-11-07T06:51:13.565103Z","shell.execute_reply.started":"2023-11-07T06:51:13.534164Z","shell.execute_reply":"2023-11-07T06:51:13.563877Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unique_customer_count_","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:51:13.985350Z","iopub.execute_input":"2023-11-07T06:51:13.986542Z","iopub.status.idle":"2023-11-07T06:51:14.001390Z","shell.execute_reply.started":"2023-11-07T06:51:13.986498Z","shell.execute_reply":"2023-11-07T06:51:13.999700Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n# Assuming 't_dat' contains the transaction date in your DataFrame\n# Extract the year from the transaction date\nmerged_df['year'] = merged_df['t_dat'].dt.year\n\n# Define your age groups (you can customize these as needed)\nage_groups = {\n    '0-18': (0, 18),\n    '19-35': (19, 35),\n    '36-50': (36, 50),\n    '51+': (51, float('inf'))\n}\n\n# Function to map age to age group\ndef map_age_to_group(age):\n    for group, (lower, upper) in age_groups.items():\n        if lower <= age <= upper:\n            return group\n    return 'Unknown'\n\n# Apply the age group mapping function to create an 'age_group' column\nmerged_df['age_group'] = merged_df['age'].apply(map_age_to_group)\n\n# Group by 'year' and 'age_group', and count unique customers\nage_group_counts = merged_df.groupby(['year', 'age_group'])['customer_id'].nunique().reset_index()\n\n# Calculate YoY changes\nage_group_counts['YoY Change'] = age_group_counts.groupby('age_group')['customer_id'].pct_change().fillna(0) * 100\n\n# Create a Plotly bar chart\nfig = px.bar(\n    age_group_counts,\n    x='year',\n    y='YoY Change',\n    color='age_group',\n    labels={'YoY Change': 'YoY Change (%)'},\n    title='YoY Change in Unique Customer Counts by Age Group',\n)\n\n# Show the chart\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:51:35.060811Z","iopub.execute_input":"2023-11-07T06:51:35.061263Z","iopub.status.idle":"2023-11-07T06:51:39.233373Z","shell.execute_reply.started":"2023-11-07T06:51:35.061227Z","shell.execute_reply":"2023-11-07T06:51:39.232211Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"age_group_counts","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:51:57.861092Z","iopub.execute_input":"2023-11-07T06:51:57.861583Z","iopub.status.idle":"2023-11-07T06:51:57.877844Z","shell.execute_reply.started":"2023-11-07T06:51:57.861535Z","shell.execute_reply":"2023-11-07T06:51:57.876118Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\n**For the year 2018:**\n- In the age group \"0-18,\" there were 125 customers in 2018.\n- In the age group \"19-35,\" there were 22,094 customers.\n- In the age group \"36-50,\" there were 9,807 customers.\n- In the age group \"51+,\" there were 8,526 customers.\n- In the \"Unknown\" age group, there were 443 customers.\n- The \"YoY Change\" column for 2018 is 0.000000, indicating there was no year-over-year change compared to the previous year (as 2018 is the base year for comparison).\n\n**For the year 2019:**\n- In the age group \"0-18,\" there were 946 customers, representing a significant increase of 656.80% compared to 2018.\n- In the age group \"19-35,\" there were 37,671 customers, showing a substantial increase of 70.50%.\n- In the age group \"36-50,\" there were 14,639 customers, reflecting a 49.27% increase.\n- In the age group \"51+,\" there were 14,715 customers, which is a 72.59% increase.\n- In the \"Unknown\" age group, there were 762 customers, indicating a 72.01% increase.\n- The \"YoY Change\" column for 2019 indicates the percentage change in customer counts compared to 2018.\n\n**For the year 2020:**\n- In the age group \"0-18,\" there were 1,837 customers, reflecting a 94.19% increase compared to 2019.\n- In the age group \"19-35,\" there were 34,485 customers, but it showed a decrease of -8.46% compared to 2019.\n- In the age group \"36-50,\" there were 12,065 customers, with a decrease of -17.58%.\n- In the age group \"51+,\" there were 11,890 customers, representing a decrease of -19.20%.\n- In the \"Unknown\" age group, there were 403 customers, showing a significant decrease of -47.11%.\n- The \"YoY Change\" column for 2020 indicates the percentage change in customer counts compared to 2019.\n\nOverall, the data provides insights into the changes in customer counts across different age groups over the three-year period. It shows significant fluctuations in customer counts, with some age groups experiencing substantial increases while others saw declines.","metadata":{}},{"cell_type":"code","source":"\n\n# Assuming you have a DataFrame named merged_df with columns 't_dat' and 'Segment'\n\n# Group data by 'Segment' and 'Year' and count unique customers\nsegment_year_counts = merged_df.groupby(['Monetary_Segment', merged_df['t_dat'].dt.year])['customer_id'].nunique().reset_index()\n\n# Calculate the YoY change\nsegment_year_counts['YoY Change'] = segment_year_counts.groupby('Monetary_Segment')['customer_id'].pct_change() * 100\n\n# Create a Plotly line plot to visualize the YoY change\nfig = px.line(segment_year_counts, x='t_dat', y='YoY Change', color='Monetary_Segment', title='YoY Change in Customer Counts by Monetary_Segment')\nfig.update_layout(xaxis_title='Year', yaxis_title='YoY Change (%)')\n\n# Show the plot\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:56:41.962359Z","iopub.execute_input":"2023-11-07T06:56:41.962761Z","iopub.status.idle":"2023-11-07T06:56:43.430286Z","shell.execute_reply.started":"2023-11-07T06:56:41.962726Z","shell.execute_reply":"2023-11-07T06:56:43.429158Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"segment_year_counts","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:57:10.330860Z","iopub.execute_input":"2023-11-07T06:57:10.331317Z","iopub.status.idle":"2023-11-07T06:57:10.347231Z","shell.execute_reply.started":"2023-11-07T06:57:10.331277Z","shell.execute_reply":"2023-11-07T06:57:10.346120Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\n**For the \"High-Value Customer\" segment:**\n- In 2018, there were 32,475 high-value customers.\n- In 2019, the number of high-value customers increased to 57,718, representing a significant YoY change of 77.73%.\n- In 2020, the number of high-value customers decreased to 48,809, indicating a YoY change of -15.44%. This means there was a 15.44% decrease in high-value customers from 2019 to 2020.\n\n**For the \"Low-Value Customer\" segment:**\n- In 2018, there were 21,799 low-value customers.\n- In 2019, the number of low-value customers increased to 45,929, showing a substantial YoY change of 110.69%.\n- In 2020, the number of low-value customers decreased to 38,220, representing a YoY change of -16.78%. This means there was a 16.78% decrease in low-value customers from 2019 to 2020.\n\n**For the \"Medium-Value Customer\" segment:**\n- In 2018, there were 27,664 medium-value customers.\n- In 2019, the number of medium-value customers increased to 53,827, indicating a YoY change of 94.57%.\n- In 2020, the number of medium-value customers decreased to 48,310, showing a YoY change of -10.25%. This means there was a 10.25% decrease in medium-value customers from 2019 to 2020.\n\nThe \"YoY Change\" column provides insights into the percentage change in customer counts for each monetary segment from one year to the next. It indicates whether the customer count increased or decreased and by how much, helping to assess the performance of each segment over the years.","metadata":{}},{"cell_type":"code","source":"\n\n\n\n# Group the data by year and sales channel\nsales_by_year_channel = merged_df.groupby(['year', 'sales_channel_id'])['price'].sum().reset_index()\n\n# Create a Plotly bar chart\nfig = px.bar(sales_by_year_channel, x='year', y='price', color='sales_channel_id',\n             labels={'year': 'Year', 'price': 'Sales Value', 'sales_channel_id': 'Channel'})\nfig.update_layout(title='Sales by Value for Each Year through Channels')\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:58:56.250005Z","iopub.execute_input":"2023-11-07T06:58:56.251285Z","iopub.status.idle":"2023-11-07T06:58:56.501989Z","shell.execute_reply.started":"2023-11-07T06:58:56.251229Z","shell.execute_reply":"2023-11-07T06:58:56.500647Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n# Assuming you have a DataFrame named 'merged_df' containing the merged data\n# You may need to adjust the column names accordingly based on your data\n\n# Group the data by sales channel and customer segment, and calculate the count of customers\nsales_channel_preference = merged_df.groupby(['sales_channel_id', 'Monetary_Segment'])['customer_id'].count().reset_index()\n\n# Create a stacked bar chart\nfig = px.bar(sales_channel_preference, x='sales_channel_id', y='customer_id', color='Monetary_Segment',\n             labels={'sales_channel_id': 'Sales Channel', 'customer_id': 'Customer Count', 'customer_segment': 'Customer Segment'},\n             title='Sales Channel Preference by Customer Segment')\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:58:59.520959Z","iopub.execute_input":"2023-11-07T06:58:59.521581Z","iopub.status.idle":"2023-11-07T06:59:00.284397Z","shell.execute_reply.started":"2023-11-07T06:58:59.521544Z","shell.execute_reply":"2023-11-07T06:59:00.283233Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n\n# Group the data by transaction year, sales channel, and customer segment\nsales_channel_preference = merged_df.groupby(['year', 'sales_channel_id', 'Monetary_Segment'])['customer_id'].count().reset_index()\n\n# Create a stacked bar chart\nfig = px.bar(sales_channel_preference, x='year', y='customer_id', color='Monetary_Segment',\n             facet_col='sales_channel_id', facet_col_wrap=2, labels={'transaction_year': 'year', 'customer_id': 'Customer Count'},\n             title='Year-on-Year Sales Channel Preference by Customer Segment')\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:59:03.014831Z","iopub.execute_input":"2023-11-07T06:59:03.015583Z","iopub.status.idle":"2023-11-07T06:59:03.908418Z","shell.execute_reply.started":"2023-11-07T06:59:03.015543Z","shell.execute_reply":"2023-11-07T06:59:03.907159Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.set_option('display.max_columns', 500)\nmerged_df.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n\n\n# Group data by product type and sales channel and calculate the total sales value\ngrouped_data = merged_df.groupby(['index_group_name','sales_channel_id'])['price'].sum().reset_index()\n\n# Create a grouped bar chart\nfig = px.bar(grouped_data, x='index_group_name', y='price', color='sales_channel_id',\n             labels={'SalesValue': 'Sales Value', 'ProductType': 'Product Type'},\n             title='Sales Channel Preference by Product Type')\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T06:59:13.131399Z","iopub.execute_input":"2023-11-07T06:59:13.132101Z","iopub.status.idle":"2023-11-07T06:59:13.584236Z","shell.execute_reply.started":"2023-11-07T06:59:13.132053Z","shell.execute_reply":"2023-11-07T06:59:13.583097Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n# Group data by product type and sales channel and calculate the total sales value\ngrouped_data = merged_df.groupby(['index_group_name', 'sales_channel_id'])['price'].sum().reset_index()\n\n# Create a sunburst chart\nfig = px.sunburst(\n    grouped_data,\n    path=['index_group_name', 'sales_channel_id'],\n    values='price',\n    title='Sales Channel Preference by Product Type',\n    color_discrete_sequence=px.colors.qualitative.Set3,  # Set a custom color palette\n    width=900,  # Set the chart width\n    height=600,  # Set the chart height\n    labels={'price': 'Sales Value', 'index_group_name': 'Product Type'},\n)\n\n# Further customize the layout\nfig.update_layout(\n    margin=dict(t=50, l=50, r=50, b=50),  # Set margins\n    title_font=dict(size=20),  # Adjust the title font size\n)\n\nfig.show()\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2023-11-07T06:59:56.083878Z","iopub.execute_input":"2023-11-07T06:59:56.084353Z","iopub.status.idle":"2023-11-07T06:59:56.561980Z","shell.execute_reply.started":"2023-11-07T06:59:56.084315Z","shell.execute_reply":"2023-11-07T06:59:56.560620Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n\n# Group data by product type and sales channel and calculate the total sales value\ngrouped_data = merged_df.groupby(['colour_group_name','sales_channel_id'])['price'].sum().reset_index()\n\n# Create a grouped bar chart\nfig = px.bar(grouped_data, x='colour_group_name', y='price', color='sales_channel_id',\n             labels={'SalesValue': 'Sales Value', 'color': 'color'},\n             title='Sales Channel Preference by Product Type')\nfig.show()\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2023-11-07T07:00:01.494422Z","iopub.execute_input":"2023-11-07T07:00:01.494863Z","iopub.status.idle":"2023-11-07T07:00:01.994639Z","shell.execute_reply.started":"2023-11-07T07:00:01.494824Z","shell.execute_reply":"2023-11-07T07:00:01.992130Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n\n# Group data by product type and sales channel and calculate the total sales value\ngrouped_data = merged_df.groupby(['garment_group_name','sales_channel_id'])['price'].sum().reset_index()\n\n# Create a grouped bar chart\nfig = px.bar(grouped_data, x='garment_group_name', y='price', color='sales_channel_id',\n             labels={'SalesValue': 'Sales Value', 'garment_group_name': 'garment_group_name'},\n             title='Sales Channel Preference by Product Type')\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T07:00:30.649531Z","iopub.execute_input":"2023-11-07T07:00:30.650014Z","iopub.status.idle":"2023-11-07T07:00:31.117327Z","shell.execute_reply.started":"2023-11-07T07:00:30.649976Z","shell.execute_reply":"2023-11-07T07:00:31.116145Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n\n\n# Group data by product type and sales channel and calculate the total sales value\ngrouped_data = merged_df.groupby(['section_name','sales_channel_id'])['price'].sum().reset_index()\n\n# Create a grouped bar chart\nfig = px.bar(grouped_data, x='section_name', y='price', color='sales_channel_id',\n             labels={'SalesValue': 'Sales Value', 'section_name\t': 'section_name'},\n             title='Sales Channel Preference by Product Type')\nfig.show()\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2023-11-07T07:00:33.158988Z","iopub.execute_input":"2023-11-07T07:00:33.159450Z","iopub.status.idle":"2023-11-07T07:00:33.648622Z","shell.execute_reply.started":"2023-11-07T07:00:33.159410Z","shell.execute_reply":"2023-11-07T07:00:33.647051Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n# Group data by product type and sales channel and calculate the total sales value\ngrouped_data = merged_df.groupby(['perceived_colour_value_name','sales_channel_id'])['price'].sum().reset_index()\n\n# Create a grouped bar chart\nfig = px.bar(grouped_data, x='perceived_colour_value_name', y='price', color='sales_channel_id',\n             labels={'SalesValue': 'Sales Value', 'perceived_colour_value_name': 'perceived_colour_value_name'},\n             title='Sales Channel Preference by Product Type')\nfig.show()\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2023-11-07T07:00:33.745393Z","iopub.execute_input":"2023-11-07T07:00:33.745821Z","iopub.status.idle":"2023-11-07T07:00:34.200855Z","shell.execute_reply.started":"2023-11-07T07:00:33.745785Z","shell.execute_reply":"2023-11-07T07:00:34.199378Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n# Group data by perceived colour value and sales channel and calculate the total sales value\ngrouped_data = merged_df.groupby(['perceived_colour_value_name', 'sales_channel_id'])['price'].sum().reset_index()\n\n# Create a sunburst chart\nfig = px.sunburst(grouped_data, path=['perceived_colour_value_name', 'sales_channel_id'], values='price')\n\n# Set the height and width of the chart\nfig.update_layout(\n    height=600,  # Set the height to your desired value (in pixels)\n    width=800,   # Set the width to your desired value (in pixels)\n)\n\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-07T07:00:34.566584Z","iopub.execute_input":"2023-11-07T07:00:34.567113Z","iopub.status.idle":"2023-11-07T07:00:35.042402Z","shell.execute_reply.started":"2023-11-07T07:00:34.567074Z","shell.execute_reply":"2023-11-07T07:00:35.041423Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n\n\n# Group data by product type and sales channel and calculate the total sales value\ngrouped_data = merged_df.groupby(['department_name','sales_channel_id'])['price'].sum().reset_index()\n\n# Create a grouped bar chart\n\nfig = px.bar(grouped_data, x='department_name', y='price', color='sales_channel_id',\n             labels={'SalesValue': 'Sales Value', 'department_name': 'department_name'},\n             title='Sales Channel Preference by Product Type')\nfig.update_layout(\n    height=600,  # Set the height to your desired value (in pixels)\n    width=3000    # Set the width to your desired value (in pixels)\n)\nfig.show()\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2023-11-07T07:00:35.768647Z","iopub.execute_input":"2023-11-07T07:00:35.769094Z","iopub.status.idle":"2023-11-07T07:00:36.259231Z","shell.execute_reply.started":"2023-11-07T07:00:35.769058Z","shell.execute_reply":"2023-11-07T07:00:36.257787Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n# Group data by product type and sales channel and calculate the total sales value\ngrouped_data = merged_df.groupby(['product_type_name', 'sales_channel_id'])['price'].sum().reset_index()\n\n# Create a bar chart with custom styling\nfig = px.bar(\n    grouped_data,\n    x='product_type_name',\n    y='price',\n    color='sales_channel_id',\n    title='Sales Channel Preference by Product Type',\n    color_discrete_sequence=px.colors.qualitative.Set3,  # Set a custom color palette\n    width=4000,  # Set the chart width\n    height=700,  # Set the chart height\n    labels={'price': 'Sales Value'},\n)\n\n# Further customize the layout\nfig.update_layout(\n    margin=dict(t=50, l=50, r=50, b=50),  # Set margins\n    title_font=dict(size=20),  # Adjust the title font size\n)\n\nfig.show()\n","metadata":{"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Aman's Orignal\n\n##### Create a customer segmentation table, based on frequency and monetary values. Add different columns from different tables such as their communication frequency, their membership status, zip code, age, gender, preferred sales channel by count, by product category/group etc..., identify family/non-family persons based on their age, gender and their purchase pattern.\n\n1. Customer Behavior: Explore how often customers make purchases by analyzing the \"customer_id\" and \"t_dat\" columns. Identify the most active customers.\n    1. What are the most frequent customers on monthly/YoY basis? Create customer bins based on their frequency and segment them.\n    2. What is the average spend of each customer segment?\n    3. What is the average age in each of the customer segment?\n    etc.\n    \n2. Price Analysis: Investigate the distribution of prices and identify any price outliers or anomalies.\n    1. What is the trend of prices for different products on YoY basis?\n    2. For each product, find its peak sales during the year and compare its prices at that time to its average during the year. Do this on YoY/monthly/... level.\n    3. Analyse Customer Behaviour based on point 2 data.\n    \n3. Communication Frequency: Investigate how often customers want to receive fashion news (fashion_news_frequency).\n    1. Communcation Frquency by Age Group/Gender/Customer Segments (ZIP Code, spending, frequency, age, gender) etc.\n    2. Analyze spending habits and patterns between customers for each communciation frequency. Refer customer segments from point 1. \n    \n4. Color Insights: Explore the most common color groups and their perceived values.\n    1. Color by Age/Gender/Customer Segment.\n    2. Color by Product Levels.\n    3. Color trend YoY basis.\n    4. Color by Price.\n    \n5. Department and Category Analysis: Investigate which departments and categories have the most articles and are most popular.\n    1. Highest Number of products in per group. (By Product Group, Index Group Name etc.) (To be done during coding)*\n    2. Category wise spends based on sales channels.\n    \n6. Market Basket Analysis: Explore associations between products that are often purchased together.\n    1. What are the most popular package bundles? Filter them across age, gender, customer segments etc","metadata":{}},{"cell_type":"markdown","source":"# Gender Female","metadata":{}},{"cell_type":"code","source":"\n\n\n# Define the female-related product categories\nfemale_categories = ['Ladieswear', 'Lingeries/Tights', 'Ladies Accessories']\n\n# Group the data by customer_id and calculate the percentage of female-related transactions\ngrouped = merged_df.groupby('customer_id').agg(\n    total_transactions=('index_name', 'count'),\n    female_transactions=('index_name', lambda x: sum(category in female_categories for category in x))\n)\n\ngrouped['female_percentage'] = (grouped['female_transactions'] / grouped['total_transactions']) * 100\n\n# Classify customers based on the percentage\ngrouped['gender'] = grouped['female_percentage'].apply(lambda x: 'Female' if x > 60 else 'Male')\n","metadata":{"execution":{"iopub.status.busy":"2023-11-06T17:52:07.743578Z","iopub.execute_input":"2023-11-06T17:52:07.744635Z","iopub.status.idle":"2023-11-06T17:52:11.669045Z","shell.execute_reply.started":"2023-11-06T17:52:07.744603Z","shell.execute_reply":"2023-11-06T17:52:11.668120Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merged_df= merged_df.merge(grouped.reset_index()[['customer_id','gender']],on='customer_id')","metadata":{"execution":{"iopub.status.busy":"2023-11-06T17:52:11.670419Z","iopub.execute_input":"2023-11-06T17:52:11.671390Z","iopub.status.idle":"2023-11-06T17:52:13.354631Z","shell.execute_reply.started":"2023-11-06T17:52:11.671358Z","shell.execute_reply":"2023-11-06T17:52:13.353441Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# import pandas as pd\n# from sklearn.cluster import KMeans\n\n\n# recency_df = merged_df.groupby('customer_id')['t_dat'].max().reset_index()\n# recency_df.columns = ['customer_id', 'LastPurchaseDate']\n\n# recency_df['Recency'] = (pd.to_datetime('today') - pd.to_datetime(recency_df['LastPurchaseDate'])).dt.days\n\n# frequency_df = merged_df.groupby('customer_id')['t_dat'].count().reset_index()\n# frequency_df.columns = ['customer_id', 'Frequency']\n\n# monetary_df = merged_df.groupby('customer_id')['price'].sum().reset_index()\n# monetary_df.columns = ['customer_id', 'Monetary']\n\n# # Merge RFM data\n# rfm_data = recency_df.merge(frequency_df, on='customer_id')\n# rfm_data = rfm_data.merge(monetary_df, on='customer_id')\n# merged_df = merged_df.merge(rfm_data,on='customer_id')\n\n# # Group customers into age groups\n# bins = [0, 30, 40, 50, 60, 100]\n# labels = ['<30', '30-40', '40-50', '50-60', '60+']\n# merged_df['AgeGroup'] = pd.cut(merged_df['age'], bins=bins, labels=labels)\n\n\n# # Customer Segmentation with K-Means\n# kmeans = KMeans(n_clusters=3, random_state=0)  # Specify the number of clusters\n# kmeans.fit(rfm_data[['Recency', 'Frequency', 'Monetary']])\n# rfm_data['Segment'] = kmeans.labels_\n\n# # Analysis\n# # You can analyze and interpret the segments here\n\n# # Family/Non-Family Identification\n# # Define criteria for identifying family customers (e.g., age and purchase pattern)\n# merged_df['Family'] = 'Non-Family'\n# merged_df.loc[(merged_df['AgeGroup'] == '30-40') & (merged_df['Frequency'] >= 10), 'Family'] = 'Family'\n\n# # Save the results to a new DataFrame or file\n# # result_data = merged_df[['customer_id', 'Recency', 'Frequency', 'Monetary', 'AgeGroup', 'Segment', 'Family']]\n\n# # You can save this DataFrame or use it for further analysis\n\nimport pandas as pd\nfrom sklearn.cluster import KMeans\n\n# Assuming 'Gender' column is available in 'merged_df'\nrecency_df = merged_df.groupby('customer_id')['t_dat'].max().reset_index()\nrecency_df.columns = ['customer_id', 'LastPurchaseDate']\nrecency_df['Recency'] = (pd.to_datetime('today') - pd.to_datetime(recency_df['LastPurchaseDate'])).dt.days\n\nfrequency_df = merged_df.groupby('customer_id')['t_dat'].count().reset_index()\nfrequency_df.columns = ['customer_id', 'Frequency']\n\nmonetary_df = merged_df.groupby('customer_id')['price'].sum().reset_index()\nmonetary_df.columns = ['customer_id', 'Monetary']\n\n# Merge RFM data\nrfm_data = recency_df.merge(frequency_df, on='customer_id')\nrfm_data = rfm_data.merge(monetary_df, on='customer_id')\nmerged_df = merged_df.merge(rfm_data,on='customer_id')\n\n# Include 'Gender' in rfm_data\ngender_df = merged_df[['customer_id', 'gender']]\nrfm_data = rfm_data.merge(gender_df, on='customer_id')\n\n\n\n# Group customers into age groups\nbins = [0, 30, 40, 50, 60, 100]\nlabels = ['<30', '30-40', '40-50', '50-60', '60+']\nmerged_df['AgeGroup'] = pd.cut(merged_df['age'], bins=bins, labels=labels)\n\n# Customer Segmentation with K-Means\nkmeans = KMeans(n_clusters=3, random_state=0)  # Specify the number of clusters\nkmeans.fit(rfm_data[['Recency', 'Frequency', 'Monetary']])\nrfm_data['Segment'] = kmeans.labels_\n\n# Family/Non-Family Identification\n# Define criteria for identifying family customers (e.g., age, gender, and purchase pattern)\nmerged_df['Family'] = 'Non-Family'\n\nfamily_condition = (merged_df['AgeGroup'] == '30-40') & (merged_df['Frequency'] >= 10)\nmerged_df.loc[family_condition, 'Family'] = 'Family'\n\n# family_condition = (merged_df['AgeGroup'] == '30-40') & (merged_df['Frequency'] >= 10) & (merged_df['gender'] == 'Female')\n# merged_df.loc[family_condition, 'Family'] = 'Family'\n\n# Save the results to a new DataFrame or file\n# result_data = merged_df[['customer_id', 'Recency', 'Frequency', 'Monetary', 'AgeGroup', 'gender', 'clu', 'Family']]\n\n# You can save this DataFrame or use it for further analysis\n","metadata":{"execution":{"iopub.status.busy":"2023-11-06T17:52:13.359441Z","iopub.execute_input":"2023-11-06T17:52:13.359781Z","iopub.status.idle":"2023-11-06T17:52:38.006812Z","shell.execute_reply.started":"2023-11-06T17:52:13.359753Z","shell.execute_reply":"2023-11-06T17:52:38.005692Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"rfm_data.head()","metadata":{"execution":{"iopub.status.busy":"2023-11-06T17:56:41.101871Z","iopub.execute_input":"2023-11-06T17:56:41.102808Z","iopub.status.idle":"2023-11-06T17:56:41.116152Z","shell.execute_reply.started":"2023-11-06T17:56:41.102722Z","shell.execute_reply":"2023-11-06T17:56:41.115180Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import matplotlib.pyplot as plt\n\n# Assuming you have a DataFrame 'rfm_data' with 'Recency', 'Frequency', 'Monetary', and 'cluster' columns\nplt.figure(figsize=(10, 6))\n\n# Define the colors for each cluster\ncolors = ['r', 'g', 'b', 'y']  # You can extend this list for more clusters\n\n# Scatter plot\nfor cluster_num in rfm_data['Segment'].unique():\n    cluster = rfm_data[rfm_data['Segment'] == cluster_num]\n    plt.scatter(cluster['Recency'], cluster['Frequency'], s=cluster['Monetary'] * 10, c=colors[cluster_num], label=f'Cluster {cluster_num}')\n\nplt.xlabel('Recency')\nplt.ylabel('Frequency')\nplt.title('RFM Analysis by Clusters')\nplt.legend()\nplt.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-06T17:52:41.433784Z","iopub.execute_input":"2023-11-06T17:52:41.434202Z","iopub.status.idle":"2023-11-06T17:53:28.516299Z","shell.execute_reply.started":"2023-11-06T17:52:41.434169Z","shell.execute_reply":"2023-11-06T17:53:28.515123Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import plotly.graph_objects as go\n\n# Create a bar chart for family/non-family identification\nage_group_counts = merged_df.groupby(['AgeGroup', 'Family']).size().reset_index(name='Counts')\nfig = px.bar(age_group_counts, x='AgeGroup', y='Counts', color='Family', title='Family/Non-Family Identification by Age Group')\nfig.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-06T17:54:19.878736Z","iopub.execute_input":"2023-11-06T17:54:19.879932Z","iopub.status.idle":"2023-11-06T17:54:20.167456Z","shell.execute_reply.started":"2023-11-06T17:54:19.879879Z","shell.execute_reply":"2023-11-06T17:54:20.166130Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# import pandas as pd\n# import plotly.express as px\n\n# # Sample data (replace with your data)\n# # Assuming the data is already loaded into merged_df\n\n# # 1. Calculate Monthly and Yearly Purchase Frequency\n# merged_df['t_dat'] = pd.to_datetime(merged_df['t_dat'])\n# merged_df['Year'] = merged_df['t_dat'].dt.year\n# merged_df['Month'] = merged_df['t_dat'].dt.month\n# purchase_frequency_monthly = merged_df.groupby(['customer_id', 'Year', 'Month'])['t_dat'].count()\n# purchase_frequency_yearly = merged_df.groupby(['customer_id', 'Year'])['t_dat'].count()\n\n# # 2. Reset the Index of purchase_frequency_yearly\n# purchase_frequency_yearly = purchase_frequency_yearly.reset_index()\n\n# # 3. Create Customer Bins\n# # Define bin labels and cut the data into segments\n# bin_labels = ['Low', 'Medium', 'High']\n# merged_df['FrequencySegment'] = pd.qcut(purchase_frequency_yearly['t_dat'], q=3, labels=bin_labels)\n\n# # 4. Calculate Average Spend\n# average_spend = merged_df.groupby('FrequencySegment')['price'].mean()\n\n# # 5. Calculate Average Age\n# average_age = merged_df.groupby('FrequencySegment')['age'].mean()\n\n# # 6. Create Visualizations\n# fig1 = px.histogram(merged_df, x='FrequencySegment', title='Customer Frequency Segmentation')\n# fig2 = px.bar(average_spend, x=average_spend.index, y=average_spend.values, title='Average Spend by Segment')\n# fig3 = px.bar(average_age, x=average_age.index, y=average_age.values, title='Average Age by Segment')\n\n# # Show the plots\n# fig1.show()\n# fig2.show()\n# fig3.show()\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2","metadata":{}},{"cell_type":"code","source":"\n# Step 1: Calculate Purchase Frequency\nmerged_df['t_dat'] = pd.to_datetime(merged_df['t_dat'])\nmerged_df['month'] = merged_df['t_dat'].dt.to_period('M')\nmerged_df['year'] = merged_df['t_dat'].dt.to_period('Y')\n\n# Calculate monthly purchase count for each customer\nmonthly_purchase_count = merged_df.groupby(['customer_id', 'month'])['t_dat'].count().reset_index()\nmonthly_purchase_count.columns = ['customer_id', 'month', 'monthly_purchase_count']\n\n# Calculate yearly purchase count for each customer\nyearly_purchase_count = merged_df.groupby(['customer_id', 'year'])['t_dat'].count().reset_index()\nyearly_purchase_count.columns = ['customer_id', 'year', 'yearly_purchase_count']\n\n# Step 2: Create Customer Bins Based on Frequency\nmonthly_bins = [0, 10,  20, float('inf')]\nyearly_bins = [0,  100, 200, float('inf')]\n\n# Create customer bins based on monthly and yearly purchase frequency\nmonthly_purchase_count['monthly_bin'] = pd.cut(monthly_purchase_count['monthly_purchase_count'], bins=monthly_bins, labels=['Low', 'Medium', 'High'])\nyearly_purchase_count['yearly_bin'] = pd.cut(yearly_purchase_count['yearly_purchase_count'], bins=yearly_bins, labels=['Low', 'Medium', 'High'])\n\n# Step 3: Average Spend of Each Customer Segment\n# Merge customer data with purchase frequency bins\n# Replace 'customer_data' with your customer data source\nmonthly_purchase_count = monthly_purchase_count.merge(merged_df, on='customer_id')\nyearly_purchase_count = yearly_purchase_count.merge(merged_df, on='customer_id')\n\n# Calculate the average spend for each customer segment\nmonthly_avg_spend = monthly_purchase_count.groupby('monthly_bin')['price'].mean().reset_index()\nyearly_avg_spend = yearly_purchase_count.groupby('yearly_bin')['price'].mean().reset_index()\n\n# Step 4: Average Age in Each Customer Segment\n# Calculate the average age for each customer segment\nmonthly_avg_age = monthly_purchase_count.groupby('monthly_bin')['age'].mean().reset_index()\nyearly_avg_age = yearly_purchase_count.groupby('yearly_bin')['age'].mean().reset_index()\n\n# Visualize the results using Plotly\nfig1 = px.bar(monthly_avg_spend, x='monthly_bin', y='price', title='Average Spend by Monthly Purchase Frequency')\nfig2 = px.bar(yearly_avg_spend, x='yearly_bin', y='price', title='Average Spend by Yearly Purchase Frequency')\nfig3 = px.bar(monthly_avg_age, x='monthly_bin', y='age', title='Average Age by Monthly Purchase Frequency')\nfig4 = px.bar(yearly_avg_age, x='yearly_bin', y='age', title='Average Age by Yearly Purchase Frequency')\n\n# Show the figures\nfig1.show()\nfig2.show()\nfig3.show()\nfig4.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-11-06T18:01:22.711062Z","iopub.execute_input":"2023-11-06T18:01:22.711430Z","iopub.status.idle":"2023-11-06T18:01:58.939793Z","shell.execute_reply.started":"2023-11-06T18:01:22.711403Z","shell.execute_reply":"2023-11-06T18:01:58.938779Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#2 \nimport pandas as pd\nimport matplotlib.pyplot as plt\n\n\n\n# Step 2: Identify Price Outliers\nQ1 = merged_df['price'].quantile(0.25)\nQ3 = merged_df['price'].quantile(0.75)\nIQR = Q3 - Q1\nlower_bound = Q1 - 1.5 * IQR\nupper_bound = Q3 + 1.5 * IQR\n\noutliers = merged_df[(merged_df['price'] < lower_bound) | (merged_df['price'] > upper_bound)]\n\n\n# Step 3: Price Distribution Analysis\nplt.figure(figsize=(8, 6))\nplt.hist(merged_df['price'], bins=30, edgecolor='k')\nplt.xlabel('Price')\nplt.ylabel('Frequency')\nplt.title('Price Distribution')\nplt.show()\n\n# Step 4: Price Trend Analysis on a YoY Basis\nmerged_df['t_dat'] = pd.to_datetime(merged_df['t_dat'])\nmerged_df['year'] = merged_df['t_dat'].dt.year\nmerged_df['month'] = merged_df['t_dat'].dt.month\n\nprice_trend = merged_df.groupby(['year', 'article_id'])['price'].mean().reset_index()\nprice_trend_monthly = merged_df.groupby(['month', 'article_id'])['price'].mean().reset_index()\n\n# Step 5: Finding Peak Sales and Comparing Prices\npeak_sales = merged_df.groupby(['year', 'article_id'])['price'].idxmax()\npeak_sales_prices = merged_df.loc[peak_sales]\n\npeak_sales_monthly = merged_df.groupby(['month', 'article_id'])['price'].idxmax()\npeak_sales_prices_monthly = merged_df.loc[peak_sales_monthly]\n\n# Step 6: Visualize Results\n# plt.figure(figsize=(12, 6))\n# for article_id in peak_sales_prices['article_id'].unique():\n#     subset = peak_sales_prices[peak_sales_prices['article_id'] == article_id]\n#     plt.plot(subset['year'], subset['price'], label=f'Product {article_id}')\n\n# plt.xlabel('year')\n# plt.ylabel('Price')\n# plt.title('Peak Sales Prices Over Years')\n# plt.legend()\n# plt.show()\n\n# You can now perform statistical tests, interpretation, and reporting as needed.\n","metadata":{"execution":{"iopub.status.busy":"2023-11-06T18:34:24.711507Z","iopub.execute_input":"2023-11-06T18:34:24.712027Z","iopub.status.idle":"2023-11-06T18:35:08.293786Z","shell.execute_reply.started":"2023-11-06T18:34:24.711988Z","shell.execute_reply":"2023-11-06T18:35:08.292418Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"price_trend.head()","metadata":{"execution":{"iopub.status.busy":"2023-11-06T18:26:39.478045Z","iopub.execute_input":"2023-11-06T18:26:39.478465Z","iopub.status.idle":"2023-11-06T18:26:39.492792Z","shell.execute_reply.started":"2023-11-06T18:26:39.478429Z","shell.execute_reply":"2023-11-06T18:26:39.491240Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# peak sales yearly","metadata":{}},{"cell_type":"code","source":"peak_sales_df = peak_sales.reset_index()\npeak_sales_df[peak_sales_df['article_id']==108775015]","metadata":{"execution":{"iopub.status.busy":"2023-11-06T18:32:38.407017Z","iopub.execute_input":"2023-11-06T18:32:38.407429Z","iopub.status.idle":"2023-11-06T18:32:38.424138Z","shell.execute_reply.started":"2023-11-06T18:32:38.407399Z","shell.execute_reply":"2023-11-06T18:32:38.422853Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# peak sales monthly","metadata":{}},{"cell_type":"code","source":"peak_sales_df_m = peak_sales_monthly.reset_index()\nz = peak_sales_df_m[peak_sales_df_m['article_id']==108775015]\nz","metadata":{"execution":{"iopub.status.busy":"2023-11-06T18:41:15.839954Z","iopub.execute_input":"2023-11-06T18:41:15.840373Z","iopub.status.idle":"2023-11-06T18:41:15.863255Z","shell.execute_reply.started":"2023-11-06T18:41:15.840343Z","shell.execute_reply":"2023-11-06T18:41:15.862143Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import plotly.express as px\nfig = px.line(z, x='month', y='price', title='Price Trend Over Months', markers=True)\n\n# Show the plot\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2023-11-06T18:41:48.001778Z","iopub.execute_input":"2023-11-06T18:41:48.002242Z","iopub.status.idle":"2023-11-06T18:41:49.367342Z","shell.execute_reply.started":"2023-11-06T18:41:48.002205Z","shell.execute_reply":"2023-11-06T18:41:49.366099Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\n1. **Price Fluctuations**: The price of the article with 'article_id' 108775015 exhibits significant fluctuations over the year. These fluctuations are evident from the varying prices recorded each month.\n\n2. **Seasonal Variation**: The data suggests that there may be some seasonality in the pricing, as the price tends to rise and fall over the months. Notably, the price is highest in the 7th month and the 8th month, which may indicate a peak in demand during those months.\n\n3. **Peak Price**: The highest price recorded during the year is in the 7th month, where the price reaches 1,027,897. This could be due to factors like increased demand, promotions, or changes in supply.\n\n4. **Price Stability**: Although there are fluctuations, the price appears to stabilize towards the end of the year, particularly in the 11th and 12th months.\n\n5. **Month-to-Month Variability**: It's interesting to note that the price in the 2nd month is considerably lower compared to other months. This could be due to special events, discounts, or changes in market conditions.\n\n6. **Consistency**: The data shows that the price in the 11th and 12th months is almost the same. This suggests that there might be a price floor or minimum price level that the product maintains.\n\nIn conclusion, the data reveals substantial price variations and potential seasonality for the article with 'article_id' 108775015. Further analysis and understanding of the underlying factors driving these price changes, such as demand patterns, promotions, and market conditions, are necessary for a more comprehensive understanding of the observed price trends. ","metadata":{}},{"cell_type":"code","source":"df[df['article_id']==108775015]","metadata":{"execution":{"iopub.status.busy":"2023-11-06T18:39:39.994782Z","iopub.execute_input":"2023-11-06T18:39:39.995608Z","iopub.status.idle":"2023-11-06T18:39:40.021233Z","shell.execute_reply.started":"2023-11-06T18:39:39.995564Z","shell.execute_reply":"2023-11-06T18:39:40.020078Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport matplotlib.pyplot as plt\n\n\n# Step 2: Communication Frequency Analysis\nfrequency_distribution = merged_df['fashion_news_frequency'].value_counts()\n\n# Step 3: Create Customer Segments (e.g., age groups)\nmerged_df['AgeGroup'] = pd.cut(merged_df['age'], bins=[0,18, 30, 40, 50, 60, float('inf')],\n                                   labels=['<18','<20-30', '30-40', '40-50', '50-60', '60+'])\n\n# Step 4: Analyze Communication Frequency by Age Group\ncommunication_by_age = merged_df.groupby('AgeGroup')['fashion_news_frequency'].value_counts(normalize=True)\n\n# Step 5: Spending Habits Analysis\nspending_by_segment = merged_df.groupby('AgeGroup')['price'].max()\n\n# Step 6: Visualization and Reporting\n# Create visualizations and provide insights based on the analysis.\n","metadata":{"execution":{"iopub.status.busy":"2023-11-06T19:34:56.620159Z","iopub.execute_input":"2023-11-06T19:34:56.620588Z","iopub.status.idle":"2023-11-06T19:34:57.129454Z","shell.execute_reply.started":"2023-11-06T19:34:56.620556Z","shell.execute_reply":"2023-11-06T19:34:57.128220Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"communication_by_age = communication_by_age.reset_index()","metadata":{"execution":{"iopub.status.busy":"2023-11-06T19:35:01.654118Z","iopub.execute_input":"2023-11-06T19:35:01.655156Z","iopub.status.idle":"2023-11-06T19:35:01.661971Z","shell.execute_reply.started":"2023-11-06T19:35:01.655109Z","shell.execute_reply":"2023-11-06T19:35:01.660835Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"spending_by_segment = spending_by_segment.reset_index()","metadata":{"execution":{"iopub.status.busy":"2023-11-06T19:35:01.933850Z","iopub.execute_input":"2023-11-06T19:35:01.934282Z","iopub.status.idle":"2023-11-06T19:35:01.941584Z","shell.execute_reply.started":"2023-11-06T19:35:01.934251Z","shell.execute_reply":"2023-11-06T19:35:01.940075Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"spending_by_segment","metadata":{"execution":{"iopub.status.busy":"2023-11-06T19:35:02.214457Z","iopub.execute_input":"2023-11-06T19:35:02.214872Z","iopub.status.idle":"2023-11-06T19:35:02.226047Z","shell.execute_reply.started":"2023-11-06T19:35:02.214839Z","shell.execute_reply":"2023-11-06T19:35:02.224887Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = px.pie(spending_by_segment, names='AgeGroup', values='price', hole=0.7, title=\"Price by Age Group\")\n\n# Show the donut chart\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2023-11-06T19:35:02.636853Z","iopub.execute_input":"2023-11-06T19:35:02.637282Z","iopub.status.idle":"2023-11-06T19:35:02.688619Z","shell.execute_reply.started":"2023-11-06T19:35:02.637249Z","shell.execute_reply":"2023-11-06T19:35:02.687500Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\n1. **Age Distribution**: The data has been categorized into different age groups. The highest proportion of customers falls within the '20-30' age group, followed by '30-40'. The '<18' age group has the lowest representation.\n\n2. **Communication Frequency by Age Group**: The analysis of communication frequency by age group shows that there is a clear pattern. Customers in the '20-30' age group have the highest frequency of receiving fashion news, while the '<18' age group has the lowest. It's interesting to note that the '30-40' and '40-50' age groups have an equal proportion of customers with 'Regularly' as their fashion news frequency.\n\n3. **Spending Habits by Age Group**: The analysis of spending habits by age group indicates that the maximum price paid by customers varies by age group. It seems that the '30-40' and '40-50' age groups have the same maximum price, as do the '50-60' and '60+' age groups. This suggests that there might be similarities in spending behavior between these age groups.\n\nOverall, the analysis provides insights into the distribution of customers by age group, their communication frequency, and their maximum spending behavior. These insights can be valuable for tailoring marketing and communication strategies to different customer segments based on their age and preferences.","metadata":{}},{"cell_type":"code","source":"communication_by_age","metadata":{"execution":{"iopub.status.busy":"2023-11-06T19:39:02.155565Z","iopub.execute_input":"2023-11-06T19:39:02.156564Z","iopub.status.idle":"2023-11-06T19:39:02.168442Z","shell.execute_reply.started":"2023-11-06T19:39:02.156527Z","shell.execute_reply":"2023-11-06T19:39:02.167193Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\n1. **<18 Age Group**:\n   - The majority of customers in the '<18' age group prefer to receive fashion news 'NONE' (no regular frequency).\n   - A significant proportion of this age group, though smaller than 'NONE', also opts for 'Regularly' receiving fashion news.\n\n2. **<20-30 Age Group**:\n   - The '<20-30' age group has a higher preference for receiving fashion news 'NONE', with nearly 59% of customers in this category.\n   - Around 41% of customers in this age group prefer to receive fashion news 'Regularly', and there is a small fraction that opts for 'Monthly'.\n\n3. **30-40 Age Group**:\n   - The '30-40' age group is similar to the '<20-30' group in their preference for 'NONE', with around 59% of customers choosing this option.\n   - About 41% of customers in this age group also prefer 'Regularly', and there is a very small proportion who prefer 'Monthly'.\n\n4. **40-50 Age Group**:\n   - The '40-50' age group exhibits a similar pattern to the '30-40' group, with around 54% preferring 'NONE' and 46% choosing 'Regularly'.\n\n5. **50-60 Age Group**:\n   - The '50-60' age group has a slight preference for 'NONE', with approximately 53% choosing this option.\n   - Around 46% of customers in this age group prefer 'Regularly', and there is a very small fraction who prefer 'Monthly'.\n\n6. **60+ Age Group**:\n   - The '60+' age group stands out with the majority preferring 'Regularly' for receiving fashion news, accounting for about 55%.\n   - Approximately 45% of customers in this age group prefer 'NONE', and a small fraction opts for 'Monthly'.\n\nOverall, the insights suggest that there are distinct patterns in communication frequency preferences based on age groups. The '<18' and '<20-30' groups tend to have a higher preference for receiving fashion news 'NONE'\n\nwhile the '60+' group is more inclined to choose 'Regularly'.","metadata":{}},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# hypothesis testing","metadata":{}},{"cell_type":"markdown","source":"1. Hypothesis: Customers who receive fashion news regularly (higher 'fashion_news_frequency') tend to make more frequent purchases or have higher CLV.\n   - Testing: Perform a t-test or ANOVA to compare the mean purchase frequencies of customers with different fashion news frequencies.\n   -Conclusion : Does fashion news influence sales significantly.\n\n2. Descriptive: 1. Prices for products in sales channel 1 are higher on average compared to sales channel 2.\n   - 2. Discount season pattern. Check for price of product during its peak sales vs rest of the year.\n   - 3. Price pattern on YoY basis for each product. Check sales during lowest price point and highest price point. What is the %age amount of discount\n   that incites growth in sales? Are there any patterns that hit low sales even in their most discounted state? Are there any patterns that hit highest sales\n   even at their highest price point? Reasons. (Box Plot)\n\n3. Hypothesis: There is no significant difference in purchase frequency between the first half and second half of the year.\n   - Testing: Perform a paired t-test to compare the purchase frequencies in the two halves of the year. (with caveats)\n\n6. Hypothesis: Highest selling colors in each product category and is it significantly higher than others YoY basis.\n   - Testing: Conduct ANOVA to compare the perceived color values of products across different departments.\n8. Hypothesis: Customers in different age groups have significantly different purchase patterns in terms of product types.\n   - Testing: Perform chi-squared tests to examine the association between age groups and product types.\n\n9. Hypothesis: Club members (club_member_status = 'ACTIVE') have a higher customer lifetime value (CLV) compared to non-club members.\n   - Testing: Conduct a t-test or ANOVA to compare CLV between club members and non-club members.\n\n10. Hypothesis: A certain age group or gender has a color preference.\n    - Testing: Perform chi-squared tests to compare color preferences in these two departments.\n\n15. Hypothesis: The graphical appearance/perceived color (graphical_appearance_name) influences the sale of a certain product.\n- Testing: Perform chi-squared tests to examine the association between graphical appearance and color group preferences.\n\n17. Hypothesis: A certain product is selling significantly more on a particular channel.\n- Testing: Perform chi-squared tests to assess the association between sales channel and product types.\n\n19. Hypothesis: There is a significant difference in purchase frequency between different departments.\nTesting: Use ANOVA to compare purchase frequencies across various departments.","metadata":{"execution":{"iopub.status.busy":"2023-11-01T17:24:52.606376Z","iopub.execute_input":"2023-11-01T17:24:52.606949Z","iopub.status.idle":"2023-11-01T17:24:52.616561Z","shell.execute_reply.started":"2023-11-01T17:24:52.606906Z","shell.execute_reply":"2023-11-01T17:24:52.614939Z"}}},{"cell_type":"code","source":"#1\nimport pandas as pd\nimport scipy.stats as stats\n\n# Assuming you have a DataFrame 'customer_data' with columns 'fashion_news_frequency' and 'Frequency'\n# Extract data for customers with regular fashion news frequency and those without\nregular_fashion_news = merged_df[merged_df['fashion_news_frequency'] == 'Regularly']['Frequency']\nno_fashion_news = merged_df[merged_df['fashion_news_frequency'] == 'NONE']['Frequency']\n\n# Perform a two-sample t-test\nt_stat, p_value = stats.ttest_ind(regular_fashion_news, no_fashion_news, equal_var=False)\n\n# Define significance level (alpha)\nalpha = 0.05\n\n# Print the results\nprint(f'T-Statistic: {t_stat}')\nprint(f'P-Value: {p_value}')\n\n# Compare the p-value to the significance level\nif p_value < alpha:\n    print(\"Reject the null hypothesis. There is a significant difference.\")\nelse:\n    print(\"Fail to reject the null hypothesis. There is no significant difference.\")\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T11:31:11.225607Z","iopub.execute_input":"2023-11-03T11:31:11.226273Z","iopub.status.idle":"2023-11-03T11:31:12.481954Z","shell.execute_reply.started":"2023-11-03T11:31:11.226240Z","shell.execute_reply":"2023-11-03T11:31:12.480716Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2","metadata":{}},{"cell_type":"code","source":"\n# Assuming you have a DataFrame named 'transactions' with relevant columns\naverage_prices_by_channel = merged_df.groupby('sales_channel_id')['price'].mean()\nprint(average_prices_by_channel)\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T11:33:30.373737Z","iopub.execute_input":"2023-11-03T11:33:30.374589Z","iopub.status.idle":"2023-11-03T11:33:30.423201Z","shell.execute_reply.started":"2023-11-03T11:33:30.374556Z","shell.execute_reply":"2023-11-03T11:33:30.421963Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Assuming you have a DataFrame named 'transactions' with relevant columns\nmerged_df['t_dat'] = pd.to_datetime(merged_df['t_dat'])  # Convert 't_dat' to a datetime type\nmerged_df['year'] = merged_df['t_dat'].dt.year  # Extract the year from 't_dat'\nmerged_df['price_change'] = merged_df.groupby('article_id')['price'].pct_change() * 100  # Calculate YoY price change\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T11:34:43.092487Z","iopub.execute_input":"2023-11-03T11:34:43.092905Z","iopub.status.idle":"2023-11-03T11:34:43.861565Z","shell.execute_reply.started":"2023-11-03T11:34:43.092864Z","shell.execute_reply":"2023-11-03T11:34:43.860058Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"lowest_price_sales = merged_df[merged_df['price_change'] == merged_df.groupby('article_id')['price_change'].transform('min')]\nhighest_price_sales = merged_df[merged_df['price_change'] == merged_df.groupby('article_id')['price_change'].transform('max')]\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T11:35:25.700839Z","iopub.execute_input":"2023-11-03T11:35:25.701154Z","iopub.status.idle":"2023-11-03T11:35:26.384860Z","shell.execute_reply.started":"2023-11-03T11:35:25.701130Z","shell.execute_reply":"2023-11-03T11:35:26.384080Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate the percentage amount of discount that incites growth in sales\ndiscount_sales_growth = merged_df[(merged_df['price_change'] < 0) & (merged_df['Frequency'] > 0)]\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T11:35:52.499468Z","iopub.execute_input":"2023-11-03T11:35:52.499840Z","iopub.status.idle":"2023-11-03T11:35:52.848145Z","shell.execute_reply.started":"2023-11-03T11:35:52.499811Z","shell.execute_reply":"2023-11-03T11:35:52.847164Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import plotly.express as px\n\n# Assuming you have DataFrames discount_sales_growth and highest_price_sales\n# You may need to adjust these DataFrames based on your dataset\n\nfig = px.box(discount_sales_growth, x='Frequency', labels={'Frequency': 'Sales Frequency'}, title='Discounted Sales Box Plot')\nfig.update_xaxes(title_text='Discounted Sales')\nfig.show()\n\nfig = px.box(highest_price_sales, x='Frequency', labels={'Frequency': 'Sales Frequency'}, title='Highest Price Sales Box Plot')\nfig.update_xaxes(title_text='Highest Price Sales')\nfig.show()\n\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T11:37:01.342843Z","iopub.execute_input":"2023-11-03T11:37:01.343168Z","iopub.status.idle":"2023-11-03T11:37:03.607147Z","shell.execute_reply.started":"2023-11-03T11:37:01.343143Z","shell.execute_reply":"2023-11-03T11:37:03.606264Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nfrom scipy import stats\n\n\n\n# Extract the year and the month from the 't_dat' column\nmerged_df['year'] = merged_df['t_dat'].dt.year\nmerged_df['month'] = merged_df['t_dat'].dt.month\n\n# Divide the data into the first and second halves of the year\nfirst_half = merged_df[(merged_df['month'] <= 6) & (merged_df['month'] >= 1)]\nsecond_half = merged_df[(merged_df['month'] > 6) & (merged_df['month'] <= 12)]\n\n# Calculate purchase frequency for each half of the year for customers with data in both halves\npurchase_frequency_first_half = first_half.groupby('customer_id')['Frequency'].sum()\npurchase_frequency_second_half = second_half.groupby('customer_id')['Frequency'].sum()\n\n# Perform a paired t-test only on customers with data in both halves\ncommon_customers = list(set(purchase_frequency_first_half.index) & set(purchase_frequency_second_half.index))\ncommon_customers_first_half = purchase_frequency_first_half.loc[common_customers]\ncommon_customers_second_half = purchase_frequency_second_half.loc[common_customers]\n\n# Perform the paired t-test\nt_stat, p_value = stats.ttest_rel(common_customers_first_half, common_customers_second_half)\n\n# Define significance level (alpha)\nalpha = 0.05\n\n# Print the results\nprint(f'T-Statistic: {t_stat}')\nprint(f'P-Value: {p_value}')\n\n# Compare the p-value to the significance level\nif p_value < alpha:\n    print(\"Reject the null hypothesis. There is a significant difference.\")\nelse:\n    print(\"Fail to reject the null hypothesis. There is no significant difference.\")\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T11:50:06.047487Z","iopub.execute_input":"2023-11-03T11:50:06.047865Z","iopub.status.idle":"2023-11-03T11:50:08.367476Z","shell.execute_reply.started":"2023-11-03T11:50:06.047840Z","shell.execute_reply":"2023-11-03T11:50:08.366253Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 4","metadata":{}},{"cell_type":"code","source":"\n\n\n# Group the data by department, color, and year and calculate the sales or revenue\ngrouped_data = merged_df.groupby(['department_name', 'perceived_colour_value_name', 'year'])['price'].sum().reset_index()\n\n# Perform the ANOVA\nresult = stats.f_oneway(*[grouped_data[grouped_data['department_name'] == department]['price'] for department in grouped_data['department_name'].unique()])\n\n# Define significance level (alpha)\nalpha = 0.05\n\n# Print the results\nprint(\"ANOVA F-statistic:\", result.statistic)\nprint(\"P-value:\", result.pvalue)\n\n# Compare the p-value to the significance level\nif result.pvalue < alpha:\n    print(\"Reject the null hypothesis. There are significant differences in perceived color values between departments.\")\nelse:\n    print(\"Fail to reject the null hypothesis. There are no significant differences in perceived color values between departments.\")\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T11:54:43.442991Z","iopub.execute_input":"2023-11-03T11:54:43.443409Z","iopub.status.idle":"2023-11-03T11:54:43.960769Z","shell.execute_reply.started":"2023-11-03T11:54:43.443380Z","shell.execute_reply":"2023-11-03T11:54:43.959852Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 5","metadata":{}},{"cell_type":"code","source":"# import pandas as pd\nfrom scipy.stats import chi2_contingency\n\n\n\n# Create a contingency table of age groups and product types\ncontingency_table = pd.crosstab(merged_df['AgeGroup'], merged_df['product_type_name'])\n\n# Perform the chi-squared test\nchi2, p, _, _ = chi2_contingency(contingency_table)\n\n# Define significance level (alpha)\nalpha = 0.05\n\n# Print the results\nprint(\"Chi-Squared Statistic:\", chi2)\nprint(\"P-value:\", p)\n\n# Compare the p-value to the significance level\nif p < alpha:\n    print(\"Reject the null hypothesis. There is a significant association between age groups and product types.\")\nelse:\n    print(\"Fail to reject the null hypothesis. There is no significant association between age groups and product types.\")\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T12:00:36.568893Z","iopub.execute_input":"2023-11-03T12:00:36.570118Z","iopub.status.idle":"2023-11-03T12:00:36.955933Z","shell.execute_reply.started":"2023-11-03T12:00:36.570075Z","shell.execute_reply":"2023-11-03T12:00:36.954918Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 6","metadata":{}},{"cell_type":"code","source":"clv_data =merged_df.groupby('customer_id')['price'].sum().reset_index()\nclv_data.rename(columns={'price': 'historical_CLV'}, inplace=True)\n\nmerged_df = merged_df.merge(clv_data, on='customer_id', how='left')","metadata":{"execution":{"iopub.status.busy":"2023-11-03T12:09:28.179403Z","iopub.execute_input":"2023-11-03T12:09:28.179771Z","iopub.status.idle":"2023-11-03T12:09:29.756734Z","shell.execute_reply.started":"2023-11-03T12:09:28.179743Z","shell.execute_reply":"2023-11-03T12:09:29.755789Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# import pandas as pd\n# from scipy.stats import f_oneway\n\n# # Assuming you have a DataFrame 'customer_data' with relevant columns\n# # You may need to adjust column names and data structures as per your dataset.\n\n# # Group data by club_member_status and calculate CLV for each group\n# groups = [merged_df[merged_df['club_member_status'] == status]['historical_CLV'] for status in merged_df['club_member_status'].unique()]\n\n# # Perform an ANOVA to compare CLV between the groups\n# f_stat, p_value = f_oneway(*groups)\n\n# # Define significance level (alpha)\n# alpha = 0.05\n\n# # Print the results\n# print(\"F-Statistic:\", f_stat)\n# print(\"P-value:\", p_value)\n\n# # Compare the p-value to the significance level\n# if p_value < alpha:\n#     print(\"Reject the null hypothesis. There is a significant difference in CLV between club membership statuses.\")\n# else:\n#     print(\"Fail to reject the null hypothesis. There is no significant difference in CLV.\")\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T12:12:22.032745Z","iopub.execute_input":"2023-11-03T12:12:22.033104Z","iopub.status.idle":"2023-11-03T12:12:22.038456Z","shell.execute_reply.started":"2023-11-03T12:12:22.033079Z","shell.execute_reply":"2023-11-03T12:12:22.037428Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 6","metadata":{}},{"cell_type":"code","source":"\n\n\n# Separate data into two groups: 'ACTIVE' club members and non-club members\nclub_members = merged_df[merged_df['club_member_status'] == 'ACTIVE']\nnon_club_members = merged_df[merged_df['club_member_status'] != 'ACTIVE']\n\n# Perform a t-test to compare CLV between the two groups\nt_stat, p_value = stats.ttest_ind(club_members['historical_CLV'], non_club_members['historical_CLV'])\n\n# Define significance level (alpha)\nalpha = 0.05\n\n# Print the results\nprint(\"T-Statistic:\", t_stat)\nprint(\"P-value:\", p_value)\n\n# Compare the p-value to the significance level\nif p_value < alpha:\n    print(\"Reject the null hypothesis. Club members with 'ACTIVE' status have a higher CLV.\")\nelse:\n    print(\"Fail to reject the null hypothesis. There is no significant difference in CLV.\")\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T12:11:54.717479Z","iopub.execute_input":"2023-11-03T12:11:54.717863Z","iopub.status.idle":"2023-11-03T12:11:56.104625Z","shell.execute_reply.started":"2023-11-03T12:11:54.717836Z","shell.execute_reply":"2023-11-03T12:11:56.103311Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 7","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nfrom scipy.stats import chi2_contingency\n\n# Assuming you have a DataFrame 'customer_data' with relevant columns\n# You may need to adjust column names and data structures as per your dataset.\n\n# Create a contingency table of color preferences for age group and gender\ncontingency_table = pd.crosstab(merged_df['AgeGroup'], merged_df['colour_group_name'])\n\n# Perform the chi-squared test\nchi2, p, _, _ = chi2_contingency(contingency_table)\n\n# Define significance level (alpha)\nalpha = 0.05\n\n# Print the results\nprint(\"Chi-Squared Statistic:\", chi2)\nprint(\"P-value:\", p)\n\n# Compare the p-value to the significance level\nif p < alpha:\n    print(\"Reject the null hypothesis. There is a significant association between age group (or gender) and color preference.\")\nelse:\n    print(\"Fail to reject the null hypothesis. There is no significant association between age group (or gender) and color preference.\")\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T12:24:17.800641Z","iopub.execute_input":"2023-11-03T12:24:17.801076Z","iopub.status.idle":"2023-11-03T12:24:18.159262Z","shell.execute_reply.started":"2023-11-03T12:24:17.801043Z","shell.execute_reply":"2023-11-03T12:24:18.158206Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 8","metadata":{}},{"cell_type":"code","source":"\n\n# # Group data by 'graphical_appearance_name' and calculate sales for each group\n# groups = [merged_df[merged_df['graphical_appearance_name'] == appearance]['price'] for appearance in merged_df['graphical_appearance_name'].unique()]\n\n# # Perform the ANOVA test to compare sales across graphical appearances\n# f_stat, p_value = f_oneway(*groups)\n\n# # Define significance level (alpha)\n# alpha = 0.05\n\n# # Print the results\n# print(\"F-Statistic:\", f_stat)\n# print(\"P-value:\", p_value)\n\n# # Compare the p-value to the significance level\n# if p_value < alpha:\n#     print(\"Reject the null hypothesis. There is a significant relationship between graphical appearance and sales.\")\n# else:\n#     print(\"Fail to reject the null hypothesis. There is no significant relationship between graphical appearance and sales.\")\n","metadata":{"execution":{"iopub.status.busy":"2023-11-03T12:26:38.646932Z","iopub.execute_input":"2023-11-03T12:26:38.647313Z","iopub.status.idle":"2023-11-03T12:26:44.291962Z","shell.execute_reply.started":"2023-11-03T12:26:38.647287Z","shell.execute_reply":"2023-11-03T12:26:44.290856Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}