Column Aggregations

The Column Aggregations feature in the Edilitics Transform module empowers users to perform sophisticated data transformations with ease and precision, all without requiring any coding expertise. This no-code functionality supports a comprehensive array of operations, enabling seamless data manipulation and aggregation to enhance your analytical capabilities.

Step-by-Step Guide to Utilizing Column Aggregations#

Column Selection#

Begin by selecting the primary column you wish to transform.

Operator Selection#

Choose the appropriate operator from the list of supported operations.

Add +

• Increases each value by a specified amount.

• Example: Adjusting prices to include a fixed tax or fee.

Subtract -

• Decreases each value by a specified amount.

• Example: Applying a discount on product prices.

Multiply *

• Scales each value by a given factor.

• Example: Calculating percentage-based growth (e.g., increasing revenue by 10%).

Divide /

• Divides each value by a specified number.

• Example: Converting total revenue into per-unit revenue.

Modulo %

• Returns the remainder when dividing each value.

• Example: Identifying even vs. odd numbers or grouping items into fixed batches.

Exponent ^

• Raises each value to a specified power.

• Example: Calculating compound interest over time.

Log log

• Computes the logarithm of each value, typically in base 10 or natural log.

• Example: Transforming skewed data for better trend analysis.

Percentage %

• Computes a percentage of each value based on a given rate.

• Example: Determining commission amounts from total sales.

Operand Specification#

Select the secondary column(s) or input a constant value to be used in the operation.

Naming the New Column#

Assign a name to the newly created column that will result from this operation.

Execute Operation#

Click on the "Submit" button to execute and apply the transformation.

Executing Multiple Aggregations#

Edilitics enables users to perform multiple aggregation operations in a single workflow, ensuring flexibility and operational efficiency.

Chaining Operations#

You can utilize newly created columns from previous operations in subsequent ones. For example:

• Operation 1:

  • Create a column "Total_Revenue" by adding "Unit Price" and "Quantity Sold."

• Operation 2:

  • Use "Total_Revenue" in another operation, such as multiplying it with "Discount Rate" to create "Discounted_Revenue."

• Operation 3:

  • Use "Discounted_Revenue" in a division operation with "Number of Transactions" to create "Avg_Revenue_Per_Transaction."

Batch Operations#

Queue multiple aggregation tasks and execute them collectively in a single submission, reducing setup time and streamlining the data transformation process.

Managing Data Type Mismatches#

In the event of data type mismatches with newly created columns, Edilitics offers a robust Cast Data Types feature to resolve these issues.

• Navigate to the "Cast Data Types" Section:

  • Select the column with the data type mismatch.

  • Convert the column to the appropriate data type to ensure compatibility for subsequent operations.

This feature ensures that all columns are correctly formatted, maintaining data integrity and operational flexibility.

Practical Applications#

Retail#

• Objective: Calculate total sales revenue and average revenue per transaction.

• Operations:

  • Select "Unit Price" and "Quantity Sold," choose the "Multiply (*)" operator, name the new column "Total_Revenue," and click Submit.

  • Select "Total_Revenue" and "Number of Transactions," choose the "Divide (/)" operator, name the new column "Avg_Revenue_Per_Transaction," and click Submit.

Healthcare#

• Objective: Adjust medication dosage and calculate patient BMI.

• Operations:

  • Select "Current Dosage" and "Adjustment Factor," choose the "Multiply (*)" operator, name the new column "Adjusted_Dosage," and click Submit.

  • Select "Weight (kg)" and "Height (m)," choose the "Exponent (^)" operator with a constant of 2, name the new column "Height_Squared," and click Submit.

  • Select "Weight (kg)" and "Height_Squared," choose the "Divide (/)" operator, name the new column "BMI," and click Submit.

Finance#

• Objective: Calculate compound interest and annual growth rate.

• Operations:

  • Select "Principal Amount" and "Rate of Interest," choose the "Exponent (^)" operator with a constant representing the number of periods, name the new column "Compound_Interest," and click Submit.

  • Select "End Value" and "Start Value," choose the "Divide (/)" operator, name the new column "Growth_Factor," and click Submit.

  • Select "Growth_Factor," choose the "Log (log)" operator, name the new column "Log_Growth_Factor," and click Submit.

Manufacturing#

• Objective: Calculate total production cost and efficiency ratio.

• Operations:

  • Select "Material Cost" and "Labor Cost," choose the "Add (+)" operator, name the new column "Total_Cost," and click Submit.

  • Select "Total_Cost" and "Units Produced," choose the "Divide (/)" operator, name the new column "Cost_Per_Unit," and click Submit.

  • Select "Units Produced" and "Hours Worked," choose the "Divide (/)" operator, name the new column "Efficiency_Ratio," and click Submit.

Education#

• Objective: Calculate weighted grades and average score.

• Operations:

  • Select "Assignment Score" and "Weight," choose the "Multiply (*)" operator, name the new column "Weighted_Score," and click Submit.

  • Select "Exam Score" and "Weight," choose the "Multiply (*)" operator, name the new column "Weighted_Exam_Score," and click Submit.

  • Select "Weighted_Score" and "Weighted_Exam_Score," choose the "Add (+)" operator, name the new column "Total_Weighted_Score," and click Submit.

  • Select "Total_Weighted_Score" and "Total Weight," choose the "Divide (/)" operator, name the new column "Average_Score," and click Submit.

Column Aggregations in Edilitics provide a robust, no-code solution for executing advanced data transformations. With a wide range of operators and the ability to chain multiple operations, users can efficiently process and analyze their data. The capability to manage data type mismatches further enhances the feature's flexibility and ensures data integrity, making data analysis both intuitive and accessible for all users.