Group By

The Group By functionality in Edilitics empowers users to aggregate data efficiently, delivering powerful insights from their datasets. This feature supports a diverse range of aggregation methods tailored to different data types, ensuring a comprehensive and flexible data analysis process. Below is an in-depth guide on utilizing the Group By functionality, including step-by-step instructions, explanations of all aggregation options, and practical considerations.

Step-by-Step Guide to Utilizing Group By#

1. Select Column to Group By#

• Choose the column you wish to group by from the dropdown list provided.

Note: Columns with data types such as Object, List, Dict, or Struct will be automatically excluded from this list. Edilitics recommends users flatten these data types before applying the Group By operation to avoid data loss. Users will be informed of the existence of such columns. \

2. Default Aggregation Applied#

• Upon selecting the group by column, Edilitics automatically applies default aggregation to all other columns in the table: _ Categorical and Datetime Columns: Aggregated by Count. _ Numerical Columns: Aggregated by Sum.

3. Adjust Aggregations#

• Modify these aggregations from the dropdown options provided. Edilitics ensures only relevant aggregation options are displayed for the corresponding data types.

4. Aggregation Options#

Categorical and Datetime Columns:

• Count (Default): Counts the number of occurrences.
  • When to Use: To determine the frequency of each unique value in a column.
• Count Distinct: Counts the number of unique values.
  • When to Use: To ascertain how many distinct values exist in a column.
• Mode: Returns the most frequent value.
  • When to Use: To identify the most common value in a column.

Numerical Columns:

• Sum (Default): Sums all values.
  • When to Use: To calculate the total of all values in a column.
• Count: Counts the number of values.
  • When to Use: To determine the number of entries in a column.
• Count Distinct: Counts the number of unique values.
  • When to Use: To identify the number of distinct values in a column.
• Min: Returns the minimum value.
  • When to Use: To find the smallest value in a column.
• Max: Returns the maximum value.
  • When to Use: To find the largest value in a column.
• Mean: Returns the average value.
  • When to Use: To calculate the average value of a column.
• Median: Returns the median value.
  • When to Use: To find the middle value when the column is sorted.
• Mode: Returns the most frequent value.
  • When to Use: To identify the most common value in a column.
• Variance: Returns the variance of the values.
  • When to Use: To measure the dispersion of the values in a column.
• Standard Deviation: Returns the standard deviation of the values.
  • When to Use: To measure the amount of variation or dispersion of the values in a column.

5. Adjust New Column Names#

• Modify new column names as needed. Edilitics auto-assigns new column names based on the aggregation applied (e.g., sum_column_name or mode_column_name).

Note: "Column names should only include alphabetic characters, numbers, and underscores (). They cannot start with an underscore () or numeric values." This validation prevents errors while loading data to the destination database.

6. Optional - Drop Columns#

• Users can opt to drop any columns they do not want to include in the final destination table.

7. Optional - Add Fields#

• Users can add existing columns with different aggregations. This feature allows multiple perspectives on the same data, enabling more comprehensive analysis.
• Description: Adding fields with different aggregations allows simultaneous views of sum, average, and maximum values, providing deeper insights. This is especially useful for complex data analysis tasks requiring various aspects of data distribution.
• When to Use: Utilize this feature for multiple aggregated views to better understand trends and patterns. It is beneficial for: _ Financial reporting to see total, average, and peak values. _ Sales analysis to understand sales sum and transaction frequency. * Performance metrics to combine mean, median, and standard deviation for a complete data variability picture.

8. Submit#

• Submit the operation to apply the Group By function and create the aggregated table.

Real-World Applications of Group By#

Here are five real-world scenarios across various industries:

1. Retail Industry#

• Objective: Analyze total sales by product category.
• Scenario:
  • Group By: ProductCategory
  • Default Aggregations Applied:
    • SalesAmount: Sum
    • TransactionDate: Count
  • Adjusted Aggregations:
    • SalesAmount: Sum (default), Count, Mean
    • TransactionDate: Count (default), Count Distinct
  • New Column Names: sum_SalesAmount, count_TransactionDate
  • Use Case: Understand total and average sales by product category for inventory and sales strategy planning.

2. Healthcare Industry#

• Objective: Aggregate patient visits by department.
• Scenario:
  • Group By: Department
  • Default Aggregations Applied:
    • VisitID: Count
    • VisitDate: Count
  • Adjusted Aggregations:
    • VisitID: Count (default), Count Distinct
    • VisitDate: Count (default), Count Distinct
  • New Column Names: count_VisitID, count_VisitDate
  • Use Case: Track the number of visits per department to allocate resources efficiently.

3. Finance Industry#

• Objective: Summarize transaction amounts by account type.
• Scenario:
  • Group By: AccountType
  • Default Aggregations Applied:
    • TransactionAmount: Sum
    • TransactionDate: Count
  • Adjusted Aggregations:
    • TransactionAmount: Sum (default), Max, Mean
    • TransactionDate: Count (default), Count Distinct
  • New Column Names: sum_TransactionAmount, max_TransactionAmount
  • Use Case: Monitor transaction volumes and average transaction values by account type for financial analysis.

4. Manufacturing Industry#

• Objective: Calculate total production by factory.
• Scenario:
  • Group By: Factory
  • Default Aggregations Applied:
    • ProductionUnits: Sum
    • ProductionDate: Count
  • Adjusted Aggregations:
    • ProductionUnits: Sum (default), Min, Max
    • ProductionDate: Count (default), Count Distinct
  • New Column Names: sum_ProductionUnits, min_ProductionUnits
  • Use Case: Assess production efficiency and output by factory to optimize operations.

5. Education Industry#

• Objective: Aggregate student scores by class.
• Scenario: _ Group By: Class _ Default Aggregations Applied: _ Score: Sum _ TestDate: Count _ Adjusted Aggregations: _ Score: Sum (default), Mean, Standard Deviation _ TestDate: Count (default), Count Distinct _ New Column Names: sum_Score, mean_Score, stddev_Score * Use Case: Evaluate class performance and identify areas needing improvement based on student scores.

The Group By functionality in Edilitics provides a robust, user-friendly solution for aggregating and analyzing data. With support for various aggregation methods tailored to different data types, users can efficiently derive meaningful insights from their datasets. This feature enhances data accessibility and usability, making it an indispensable tool for all users.