The Math transformation in Nexadata Pipelines allows you to perform a wide range of statistical operations across selected numeric columns. With options such as summation, averaging, standard deviation, and more, this transformation offers flexibility and depth for data analysis. The output is structured in CSV format, featuring each selected column's name with computed values for each selected math operation displayed below. You can configure the transformation using Natural Language Mode or Advanced Mode, depending on the level of control required.
Inputs for the Math Transformation
Name of the Transformation: In Natural Language Mode, the transformation name is automatically generated but can be renamed later in Advanced Mode. For instance, you might rename it to "Calculate Sales Metrics" or "Analyze Quarterly Data".
Columns to Transform: Select one or multiple numeric columns to analyze. Only numeric columns are valid for this transformation.
Math Operations: Choose one or more mathematical operations to perform on the selected columns. Available operations include:
Sum: Adds up all values in the column.
Mean (Average): Calculates the mean of the values in the column.
Median: Finds the middle value when sorted.
Mode: Returns the most frequently occurring value.
Standard Deviation: Measures how spread out the values are.
Count: Counts the number of entries in the column.
Max: Finds the highest value in the column.
Min: Finds the lowest value in the column.
Number Distinct: Counts the unique values in the column.
Using Natural Language Mode
In Natural Language Mode, describe the math transformation you want to perform, and Nexadata will automatically configure it. The transformation name is auto-generated but can be refined in Advanced Mode.
Example Instructions in Natural Language Mode
Calculate the mean and standard deviation for Sales_Q1, Sales_Q2, and Sales_Q3 columns.
Sum the Expense_Total and calculate its median.
Compute the average, max, and min for Total_Revenue.
Find the mode and count of values in Revenue_Q1 and Revenue_Q2.
Compute the mean and standard deviation for columns Travel_Cost and Lodging_Cost.
Note: If Natural Language Mode doesn’t fully meet your requirements, switch to Advanced Mode for further customization.
Using Advanced Mode
In Advanced Mode, you can configure the Math transformation with specific selections for columns and operations, allowing for precise customization.
Steps in Advanced Mode
Name of the Transformation: Enter or update a custom name, such as "Quarterly Sales Analysis" or "Expense Summary".
Columns to Transform: Select one or more numeric columns to apply the mathematical operations, such as "Revenue_Q1", "Revenue_Q2", or "Expense_Total".
Math Operations: Choose one or more operations to perform, such as Sum, Mean, Median, Mode, Standard Deviation, Count, Max, Min, or Number Distinct.
Advanced Mode allows for detailed control, ensuring the transformation precisely aligns with your analytical needs.
Output Format
The Math transformation outputs results in a structured CSV format with the following layout:
First Column: Lists each selected math operation (e.g., Sum, Mean, Median) as row labels.
Selected Columns as Headers: Each column selected for transformation appears as a header.
Computed Value Rows: For each column, the computed value for each math operation is displayed directly below the header.
This organized structure allows for easy review and comparison across multiple metrics.
Error Handling and Data Validation
The Math transformation validates that only numeric columns are selected for the transformation. If non-numeric columns are selected, an error message will prompt users to adjust their selection, ensuring accurate computations.
Example Use Case
Analyzing Quarterly Sales
The Math transformation is perfect for summarizing and analyzing columns with numeric values, such as sales data. For example, suppose you have columns for Sales_Q1, Sales_Q2, Sales_Q3, and Sales_Q4 and want to calculate the Total Sales and Average Sales for each quarter. For example:
Transformation Name: Sales Analysis
Columns to Transform: Sales_Q1, Sales_Q2, Sales_Q3, Sales_Q4
Math Operations: Sum, Mean
Output: A CSV file with rows showing Sum and Mean for each selected quarter column, providing an at-a-glance summary of total and average sales.
Sample Output in CSV Format
Operation | Sales_Q1 | Sales_Q2 | Sales_Q3 | Sales_Q4 |
Sum | 125,000 | 130,000 | 140,000 | 135,000 |
Mean | 31,250 | 32,500 | 35,000 | 33,750 |
Summary
The Math transformation in Nexadata Pipelines supports a full range of statistical operations across numeric columns, including summation, average, standard deviation, and more. Use Natural Language Mode for quick setup or Advanced Mode for precise configuration. This transformation complements the Sum transformation, providing versatile options for aggregating and analyzing numeric data.