MyBox: Easy Tools Set

User Guide – Data Tools

Author: Mara

Version: 6.7.1

Date: 2023-3-13

1 Introduction

This is desktop application based on JavaFx to provide simple and easy functions. It's free and open sources.

1.1 Main Interface

1.2 Resources Addresses

1.3 Documents

1.4 Menu of Tools

2 Data in Two-dimensional Storage Structure

2.1 Data Objects

1. Following objects can be edited in consistent way:

• Data files, including CSV File, Excel file, texts file.

• Data of MyBox Clipboard

• Matrices

• Database tables.

2. Data are represented as two-dimensional storage structure:

• "Columns" define dimensions of data in horizontal direction.

• "Rows" save values of data in vertical direction..

• Data should be in same width. That is all rows have equal number of columns.

2.2 Functions Menu

Hover or click button “Functions” to pop Functions Menu.

2.3 Define Columns

2.3.1 Interface of Columns Management

Under tab “Columns”, add/delete/change columns in table view:

1. Column names should not be null nor duplicated.

2. Click table cell to edit value directly.

3. Select one row and click button “Edit” to open edit window.

4. Can rename all columns with sequence numbers.

5. Can set random colors.

6. Can adjust orders of columns.

7. Click button "OK" to apply modifications of columns to "Table" of current data.

8. Click button "Cancel" to discard modifications of columns and pick data from "Table" of current data.

9. When changes have not been saved, if modifications have not been applied to data, “**” is displayed in tab header, or else “*” is displayed in tab header.

2.3.2 Types of Columns

1. Types of columns include: String, Double, Float, Long, Integer, Short, Boolean, Datetime, Date, Era, Longitude, Latitude, Enumeration, Color.

2. This attribute is used to display, edit, calculate, and save data.

3. Longitutde and Latitude are defined together generally.

2.3.3 Format of Column

1. This attribute is mainly for display. When data are inputted/edited, formats are not applied automatically and original inputs are kept.

2. In some interfaces, like "Copy" or "Export", options "Save date/time/era and numbers as columns' formats" can be checked.

2.3.3.1 Format for Numbers

To numbers, format can be: group in thousands, group in ten thousands, scientific notation, and no format.

2.3.3.2 Format for Datetime/Date

To Datetime/Date, following are supported in formats: MM/dd/yy, yy-MM-dd, milliseconds, time zone, T separator, patch century, etc..

2.3.3.3 Format for Era

To Era, following are supported in formats: MM/dd/yy, yy-MM-dd, milliseconds, time zone, T separator, patch century, prefix/suffix of “AD” and “BC” in Chinese and English,etc..

2.3.3.4 Define Enumeration

To Enumeration, list of values can be defined.

2.3.4 Handle Invalid Values

1. How columns handle invalid values, including: skip, count as empty, and count as zero.

2. In some context, “count as empty” equals to “skip”.

3. This attribute is only used for display or calculation. When data are inputted/edited, invalid values are not handled automatically.

2.4 Usages of Columns

Principle of column usages is “Most tolerability and least manufacture”.

2.4.1 Load Data

When load data, types of columns are not checked, and original values are read and imported.

2.4.2 Display Data

1. Parse values as columns' types.

2. Handle invalid values as columns' definitions.

3. Rewrite values as columns' formats.

4. Displayed values may be different from current actual values.

5. Example, Column type is Double and value “abc” is read:

• If count invalid as zero, then display it as zero(actual value is still “abc”).

• If skip or count invalid as empty, then display it as “abc”.

2.4.3 Controls for editing

• To Boolean, checkbox is provided.

• To Enumeration, list view is provided with selections.

• To Color, palette is provided.

• To Longitude and Latitude, map can be popped to locate coordinate.

2.4.4 Edit Data Cell

1. When user clicks a data cell in table view, editing is started. Its original value is displayed, while both type and format of column are ignored.

2. While user inputs and modifies the value, value in the edit control is checked by column type:

• If value is invalid, then edit contorl is displayed in abnormal color.

• If value is valid, then edit control is displayed in normal color.

• The value is always kept as what user has inputted.

3. When focus leaves the data cell, value in the edit control is checked:

• If value is not changed, then no checking of column type nor saving.

• If value is changed, then check the changed value as column type:

  • To invalid value, discard it, and then restore and display original value.

  • To valid value, submit and save as new value, and then dsiplay saved value as type and format of the column.

4. Example, column type is Double, count invalid as empty, and decimal scale is 2. When read “abc”:

• The data cell is displayed as “abc”.

• User modifies it as “abc123”:

  • While user inputs the change, the text field is always in abnormal color.

  • After user clicks Enter or other control, this data cell comes back as “abc” in normal color.

• User modifies it as “123.4567”:

  • While user inputs the change, the text field is always in normal color.

  • After user clicks Enter or other control, this data cell is saved as “123.4567” and displayed as “123.46”.

5. Other data cells are not affected. That is, data cells are always in originial values if they are not changed.

2.4.5 Save Data

1. Values are written into file of CSV/Texts/Excel as strings.

2. Values are written into database table as nearest types:

Column Type of MyBox	Data Type of JDBC
String	VARCHAR
Double	DOUBLE
Float	FLOAT
Long	BIGINT
Integer	INT
Short	SMALLINT
Boolean	BOOLEAN
Datetime	TIMESTAMP
Date	DATE
Era	BIGINT
Longitude	DOUBLE
Latitude	DOUBLE
Enumeration	VARCHAR
Color	VARCHAR

Notice: derby does not support negative date, so Ear is saved as long.

3. All values in Matrix are saved as Double.

2.4.6 Calculate Data

1. Data are handled as original values, without concern about types and formats of columns.

2. Values are parsed as need. Example, if the calculation requires double values, then try to convert values as doubles.

3. If value conversion fails, then invalid value is handled as definition of column.

4. Calculation itself can define how to handle invalid values. Definition of calculation priors to definition of column.

5. Both column and calcultion can define decimal scale. Definition of calculation priors to definition of column.

6. Example, Column type is String, and descriptive statistic is running against it:

• Try to convert each value as double.

• If “count invalid value as zero”, then invalid values are calculated into “Count”, and participate in calculations of “Mean” and “Sum”.

• If “skip invalid value”, then invalid values are not involved in any calculation.

• If “count invalid value as empty”, then invalid values cause results of all calculations as invalid(Double.NaN).

2.4.7 Sort Data

1. For any calculations involved in sorting, data will be translated into a temporary database table, and be sorted by database system.

2. Results of sorting are related to columns' types. Example, string “124” is smaller than string “18”, while number “124” is bigger than number “18”.

2.4.8 Color of Column

Column color is mainly used for data charts.

When chart is generated, elements in it are displayed in colors as their columns' definitions.. Then user can set chart in random colors.

2.5 Edit Data

2.5.1 Mode "Table"

"Table" is the master edit mode:

1. Its modifications are applied to other panes automatically.

2. It is the final data to save.

3. To string values, except for text file, multiple lines can be edited and saved:

• When the value is single line(not contain line break):

  • Text field is shown when click the data cell.

  • Write "\n" as line break in the value and commit the change(return or click other place).

• When the value contains line breaks:

  • Text area is shown when click the data cell.

  • Write the text in multiple lines directly.

2.5.2 Mode "CSV"

"CSV" is the assist edit mode.

1. Click button "OK" to apply its modifications to "Table".

2. Click button "Cancel" to discard its modifications and pick data from "Table".

3. Click button "Delimiter" to pick data from "Table" and apply new delimiter while its modifications are discarded.

4. If value contains delimeter or line break, it should be surrounded by quotes.

2.5.3 Save and Recover

1. When changes are not saved, * is displayed in tab header.

2. Click button "Save" to write modifications to file and database:

• Changes of rows in "Table", including modify/add/delete/sort, affect rows of current page in file.

• Changes in "Columns" tab, including modify/add/delete/sort, affect all rows in file.

• Changes of attributes and columns are saved in database.

3. Click button "Recover" to discard all modifications and load data from file and database.

2.6 View Data

2.6.1 View Html

1. View data of current data.

2. Options: Form, Title, Column Names, Row Numbers.

2.6.2 View CSV

1. View data of current data.

2. Options: Form, Title, Column Names, Row Numbers.

3. Set delimiter.

2.7 Define Data Attributes

Data name, decimal scale, and maximum value of random.

2.8 Row Expression

2.8.1 Usages of Row Expression

JavaScript expression can be data values when manufacture/trim/calculate data or generate chart.

2.8.2 Edit Row Expression

1. If the script is blank, then return empty string.

2. It can include any valid elments which Nashorn can parse(ECMAScript 5.1).

3. It should be a value finally.

4. It can include following placeholders:

#{TableRowNumber}

#{DataRowNumber}

#{<column_name>}

#{<column_name>- <statistic-name>}

5. All valid placeholders are listed in left.

6. To save the script, click "Edit" button.

7. Hover or click button “Examples” to paste example codes.

8. Hover or click button “Histories” to paste codes in histories.

2.8.3 Calculate Row Expression

When MyBox evaluates the expression:

1. Placeholders are replaced with actual values of each data row.

2. Statistic values are calculated by all data.

3. '#{xxx}' is handled as string while #{xxx} is handled as number.

4. When handles all pages, script fails when it includes “#{TableRowNumber}” .

2.8.4 Examples

2.9 Row Filter

2.9.1 Usages of Row Filter

“Row Filter” is special “Row Expression”, and can be condition to filter data rows.

2.9.2 Edit Row Filter

1. It should be boolean value("true" or "false") finally.

2. Can set maximum rows to take.

3. Can be saved in tree.

2.9.3 Exmaples

2.10 Group Rows

2.10.1 Before and after Grouping

1. Before grouping, rows can be filtered. That is, objects of grouping are filtered rows.

2. In order to implement sorting and statistic with database system, data are converted as temporary database table automatically before grouping.

3. Sorting condition and maximum data number of each group can be set for grouped data.

2.10.2 Usages of grouping

With grouped data, following can be handled:

1. Add grouping information in source data, which are 2 new columns: Group Number and Group Parameter.

2. Split data into multiple files of groups.

3. Calculate descriptive statistic of groups.

4. Display following types of charts in sequence of groups dynamically: XY chart, pie chart, comparison bars chart, self comparison bars chart, box-and-whisker chart.

2.10.3 Ways of Data Grouping

MyBox supports six ways to group data:

Following are their descriptions.

2.10.4 Group by Equal Values

2.10.4.1 Way of Grouping

Select several columns and put rows into same group when they have same values of these columns.

2.10.4.2 Implementation of Grouping

1. Convert data as a temporary database table

2. Read each row and group them by equal values of selected columns. The results like executing SQL of “group by”.

2.10.4.3 Results of Grouping

1. Complete: Each row belongs to a group.

2. Non-intersect: Each row only belongs to one group.

2.10.5 Group by Value Ranges

2.10.5.1 Way of Grouping

Select one column and divide data by value ranges of this column.

Following 3 selections can be for the split:

• Interval of values

• Number of split

• List of "StartValue-EndValue"

2.10.5.2 Split Date/Era

When split rows of date/era:

1. Values of the column are converted as milliseconds.

2. Time unit can be set.

3. For items of “Start-End”, ranges should be in valid formats of date/era.

2.10.5.3 Edit List of “Start-End”

1. Add/Delete/Update start-end items in table.

2. Set whether includes “start”/“end” for “start-end” items.

2.10.5.4 Implementation of Grouping

• Convert data as a temporary database table.

• Ignore type of selected column, and parse its values as double.

• Calcuate maximu value and minimum value of this column.

• Count ranges of each group.

• Compose query statements for each group. The conditions are like “column-name >= start-value-of-range AND column-name < end-value-of-range”.

2.10.5.5 Results of Grouping

• If split as size or number, then:

• Complete: Each row belongs to a group.

• Non-intersect: Each row only belongs to one group.

• If split as start-end list, then:

  • Possible incomplete: Some rows may not belong to any group.

  • Possible intersect: Some rows may belongs to several groups.

2.10.6 Group by Time

2.10.6.1 Way of Grouping

Only when data include date/time columns, this way can be taken:

1. Select a column of date/time type.

2. Select time unit: century/year/month/day/hour/minute/second.

Examples:

• When time unit is “century”, data are calculated to get century values and grouped by them.

• When time unit is “month”, data are divided as same groups when they have same values of year and month.

• When time unit is “hour”, data are divided as same groups when they have same values of year, month, day, and hour.

• When time unit is “second”, data are divided as same groups when they have same values of year, month, day, hour, minute, and second.

2.10.6.2 Implementation of Grouping

1. Convert data as a temporary database table.

2. As time unit, save values are in same groups.

2.10.6.3 Results of Grouping

1. Complete: Each row belongs to a group.

2. Non-intersect: Each row only belongs to one group.

2.10.7 Group by Expression

2.10.7.1 Way of Grouping

Define a row expresion, and rows are in same groups when they have same values of this expression.

Examples:

• Expression “new Date('#{StartTime}').getDay()”, group as the week values of column “StartTime”.

• Expression “#{description}'.charAt(2)”, group as the 2th character of column “description”.

• Expression “'#{StartTime}'.substring(0, 7)”, group as the first 7 characters of column “StartTime”.

2.10.7.2 Implementation of Grouping

1. Convert data as a temporary database table.

2. Calculate the expression against each row, save values are in same groups.

2.10.7.3 Results of Grouping

1. Complete: Each row belongs to a group.

2. Non-intersect: Each row only belongs to one group.

2.10.8 Group by Conditions

2.10.8.1 Way of Grouping

Define some row filters and data rows are divided in groups by these row filters.

2.10.8.2 Implementation of Grouping

3. Convert data as a temporary database table.

4. For each row filter, scan data rows to pick out group members which satisfy condition.

2.10.8.3 Results of Grouping

• Possible incomplete: Some rows may not belong to any group.

• Possible intersect: Some rows may belongs to several groups.

2.10.9 Group by Row Number

2.10.9.1 Way of Grouping

Divide data rows by their row numbers. Following 3 selections can be for the split:

• Size of split(interval of row numbers)

• Number of split

• List of "StartNumber-EndNumber"

2.10.9.2 Implementation of Grouping

1. Convert data as a temporary database table.

2. Count range of row numbers for each group.

3. Scan data rows, and set groups as row numbers.

2.10.9.3 Results of Grouping

• If split as size or number, then:

• Complete: Each row belongs to a group.

• Non-intersect: Each row only belongs to one group.

• If split as start-end list, then:

  • Possible incomplete: Some rows may not belong to any group.

  • Possible intersect: Some rows may belongs to several groups.

2.11 Modify Data

Hover or click button “Function” to select functions under menu item “Modify”.

2.11.1 Add Rows

1. Select where to add new rows: Front, End, Above some row, Below some row.

2. Set rows numberto be added.

3. Set values for new rows. Edit controls are provided as types of columns:

• If data contains both Longitude and Latitude, display button “Location”.

• For datetime, date, or era, display button “Example”.

• For boolean, display selection buttons.

• For enumeration, display value list.

• For color, display button “Color”.

2.11.2 Set Values

1. Select data rows. Row filter can be set.

2. Select columns to be set.

3. Select way to set values:

• Constant：0, 1, blank, or inputted value

• Random, random of non-negative

• Add prefix, append suffix, set decimal scale, or add sequence numbers

• When selected data are sqaure, whose rows number equals to columns number, they can be set as following: gaussian distribution, identify matrix, upper triangular matrix, lower triangular matrix.

• Row expression

4. If handle all pages of data file, then auto-backup before set values.

2.11.3 Delete

1. Select data rows. Row filter can be set.

2. Option: Whether continue when error.

3. If handle all pages of data file, then auto-backup before delete.

2.11.4 Set styles / Mark Abnormal Values

2.11.4.1 Manage Styles

1. Add/Edit/Delete/Rename styles.

2. Define title and sequence number of the style.

3. Set whether the style marks abnormal values.

2.11.4.2 Data Scope

Define conditions to determine which data cells to apply the style:

1. Range of data rows

2. Column names.

3. Row filter.

Notice, data of a row number may be changed when some rows are added or deleted. Example, when insert 2 rows before "row 6", original "row 12" becomes "row 14" while current "row 12" was "row 10".

So "row number" is not right way to locate a specific data row while rows number is changing. A way to refer sepcial rows is the expression composed of column values.

2.11.4.3 Define Styles

1. Font color, font size, background color, bold, etc.

2. More values in format of JavaFx CSS.

2.11.4.4 Apply Styles

When load data page, all styles of the data are applied to the rows one by one in order of their sequence numbers(from small to large).

When data scopes of styles are interlaced, style with larger number overrides the one with smaller number for the interlaced part.

2.11.5 Paste Content in System Clipboard

1. Read contents in System Clipboard.

2. Guess delimeter and parse data.

3. User can select a delimeter to parse data.

4. Select how to parse:

• CSV parser: Values can contain delimeter and line break. But not support parsing “Blanks” and “Regular Expression”.

• Text parser: Values can not contain delimeter and line break. Support parsing “Blanks” and “Regular Expression”.

5. Select whether first line defines column names.

6. Based on parsed data, select rows and columns to be pasted.

7. Select location to paste data: row and column in target data.

8. Select the way to paste data: Replace, Insert Above, or Append Below.

2.11.6 Paste Content in MyBox Clipboard

1. Select data item in MyBox Clipboard.

2. Select rows and columns to be pasted.

3. Select location to paste data: row and column in target data.

4. Select the way to paste data: Replace, Insert Above, or Append Below.

2.12 Trim Data

Hoever or click button “Function” and select functions under menu item “Trim”.

2.12.1 Copy/Filter/Query/Convert

1. Select data rows. Row filter can be set.

2. Select columns to be copied.

3. Select wthether:

• Copy row numbers

• Copy column names

• Save date/time/era and numbers as columns' formats

4. Results can be written as external data, including: new csv/excel/text file, matrix, system clipboard, MyBox clipboard, database table, json, xml, html, or pdf.

5. When handle rows for current page or selected ones, results can be written in current page: insert, append, or replace data in defined location of the table.

2.12.2 Sort

1. Select data rows. Row filter can be set.

2. Select columns to sort and whether descending.

   Data type of column affects sorting results.

3. Maximum rows number of results can be set.

4. Select columns to be copied.

5. Select whether copy row number or column name.

6. Results can be written as external data, including: new csv/excel/text file, matrix, system clipboard, MyBox clipboard, database table, json, xml, html, or pdf.

7. When handle rows for current page or selected ones, results can be written in current page: insert, append, or replace data in defined location of the table.

2.12.3 Transpose

1. Select data rows. Row filter can be set.

2. Select columns to be calculated.

3. Select whether:

• Copy row number

• Copy column name

• Set first column as column names.

4. Results can be written as external data, including: new csv/excel/text file, matrix, system clipboard, MyBox clipboard, database table, json, xml, html, or pdf.

5. When handle rows for current page or selected ones, results can be written in current page: insert, append, or replace data in defined location of the table.

2.12.4 Normalization

1. Select data rows. Row filter can be set.

2. Select columns to be calculated.

3. Select objects: Accroding to Columns/rows/all.

4. Select algorithms: MinMax(Range can be set), sum(L1), Zscore(L2).

5. To non-numeric, skip or count as zero.

6. Select columns to be copied.

7. Results can be written as external data, including: new csv/excel/text file, matrix, system clipboard, MyBox clipboard, database table, json, xml, html, or pdf.

8. When handle rows for current page or selected ones, results can be written in current page: insert, append, or replace data in defined location of the table.

2.12.5 Split/Group

1. Select data rows. Row filter can be set.

2. Set parameters of grouping.

3. Select columns to be copied.

4. Set sort conditions and maximum data rows in each group.

5. Select whether include row numbers.

6. Select target:

• All groups in single file

• One file for each group

• Database table

2.13 Data Calculation

Hover or click button “Function” to select functions under menu item “Calculation”.

2.13.1 Row Expression

1. Select data row. Data filter can be set.

2. Select columns to be copied.

3. Input row expression and its name.

4. Results can be written as external data, including: new csv/excel/text file, matrix, system clipboard, MyBox clipboard, database table, json, xml, html, or pdf.

5. When handle rows for current page or selected ones, results can be written in current page: insert, append, or replace data in defined location of the table.

2.13.2 Descriptive Statistic

1. Select data rows. Data filter can be set.

2. Select columns to be calculated.

3. Select statistic items:

count, sum, mean, geometric mean, sum of squares, mode, skewness,

population variance, sample variance, population standard deviation, sample standard deviation,

minimum(Q0), lower quartile(Q1), median(Q2), upper quartile(Q3), maximum(Q4),

upper extreme outlier line(E4), upper mild outlier line(E3), lower mild outlier line(E2), lower extreme outlier line(E1)

4. Select objects: Accroding to columns/rows/all.

5. Set decimal scale.

6. To non-numeric: skip or count as zero

7. Results can be written as external data, including: new csv/excel/text file, matrix, system clipboard, MyBox clipboard, database table, json, xml, html, or pdf.

8. When handle rows for current page or selected ones, results can be written in current page: insert, append, or replace data in defined location of the table.

2.13.3 Statistic in Groups

2.13.3.1 Options of Calculation

1. Select data rows. Data filter can be set.

2. Set parameters of grouping.

3. Select columns to be calculated.

4. Select statistic items. “Count” is always selected.

5. Set decimal scale.

6. To invalid numbers: set as blank or zero.

2.13.3.2 Grouped Data

After data are grouped, 2 columns are added: “Group id”, “Group Parameter”.

2.13.3.3 Statistic Data

Based on grouped data, calculate statistic values for each group.

2.13.3.4 Data in Chart

1. Statistic data are trimmed into columns and rows for chart.

2. If select “Display all data in chart”, then data of all pages are displayed in chart. Or else only data of current page are disaplyed in the chart.

2.13.3.5 XY Chart

Statistic data of groups can be displayed as XY chart:

1. Select columns to be displayed

2. Select statistic values to be displayed.

3. Select category axis: group parameter, or group id.

4. Select type of XY chart.

5. Select whether transpose the chart.

6. Set maximum data number in the chart.

2.13.3.6 Pie Chart

Groups and their “Count” are displayed as Pie chart.

1. Select category axis: group parameter, or group id.

2. Set maximum data number in the chart.

2.13.4 Simple Linear Regression

2.13.4.1 Base of Implementation

This tool is based on Apache Commons Math.

The regression does not store data, so calculation itself has not memory limitation when handle lots of rows.

2.13.4.2 Options of Calculation

1. Select data rows. Data filter can be set.

2. Select one column as independent variable.

3. Select another column as dependent variable. It should not be the same column of independent variable.

4. Set desired significance level(alpha).

5. Select whether includes intercept.

6. Select whether display all data in chart.

7. Set decimal scale.

2.13.4.3 Regression

Display values status of regression steps in table, including number of observations, slope, intercept, coefficient of determination(R-Square), correlation coefficient(R), mean of squared error(MSE) , sum of squared errors(SSE), total sum of squares(SSTO), sum of squares about regression(SSM/SSR), etc.

2.13.4.4 Model

1. Display fitted liear model.

2. Display data status of last regression step.

3. Input value fo independent variable, and generate predicted value.

2.13.4.5 Fitted Chart

1. When handle all data rows(all pages), option to display all values in chart.

When display all values in chart, need concern memory limitation when load lots of data.

Or else only values in current page will be displayed in chart while all pages involve regression, and no memory limitation.

2. Set parameters of plot, X axis and Y axis.

3. Options to display fitted points, fitted line, or model description in chart.

4. Options to display data labels.

5. Set random colors to fitted points/line.

6. Fitted chart can be popped.

7. Html including fitted chart and its data can be created.

8. Display fitted chart's data in table.

2.13.4.6 Residual Chart

1. X axis can be set as: predicted value, independent varaible, or actual value.

2. Option whether standardize residuals.

When standardize residuals, upper line and lower line of Sigma2(95%) will be displayed.

3. Set random color to points and lines.

4. Display residual chart's data in table.

2.13.5 Simple Linear Regression - Combination

This tool helps to generate data of simple linear regression:

1. Select some columns as candidates of independent variables.

2. Select some columns as candidates of dependent variables.

3. Select options like decimal scale, alpha, whether include intercept.

4. When click button "OK":

• Make pairs from candidates : one as independent variable and another as dependent variable.

• Calculate the regression models.

• Sort the modes by their coefficient of determination(R-Square) in descending order.

5. Select one model and click button "View" to view its regression data, fitting chart, and residual chart.

2.13.6 Multiple Linear Regression

2.13.6.1 Regression

This tool helps to generate data of multiple linear regression based on Apache Commons Math:

1. Select some column as independent variables, whose data should be numbers.

2. Select a column as dependent variable, whose data should be numbers.

3. Select whether include intercept.

4. Click button "OK":

• Tool normalizes involved data by Z-Score.

• Tool calculates multiple linear regression by Ordinary Least Squares(OLS).

• Results include intercept, coefficients, R-Squre, adjusted R-Squre.

2.13.6.2 Model

User can input values of independent variables and predict value of dependent variable.

2.13.7 Multiple Linear Regression - Combination

This tool helps to generate data of multiple linear regression based on Apache Commons Math:

1. Select some columns as candidates of independent variables.

2. Select some columns as candidates of dependent variables.

3. Select whether include intercept.

4. When click button "OK", the tool does following:

• Make combination of candidates as independent variables and dependent variable .

• Calculate the regression models.

• Sort the modes by their adjusted coefficient of determination(R-Square) in descending order.

5. Select one model and click button "View" to view its regression data

2.13.8 Frequency Distributions

1. Select data row. Data filter can be set.

2. Select one column to count frequency.

3. Option whether case-insensitive

4. Set decimal scale.

5. Results can be written as external data, including: new csv/excel/text file, matrix, system clipboard, MyBox clipboard, database table, json, xml, html, or pdf.

6. When handle rows for current page or selected ones, results can be written in current page: insert, append, or replace data in defined location of the table.

2.13.9 Values Percentage

1. Select data row. Data filter can be set.

2. Select columns to be calculated.

3. Select objects: Accroding to columns/rows/all.

4. Set decimal scale.

5. To negative values: skip, zero, or absoluate value.

6. To non-numeric, skip or count as zero.

7. Select columns to be copied

8. Results can be written as external data, including: new csv/excel/text file, matrix, system clipboard, MyBox clipboard, database table, json, xml, html, or pdf.

9. When handle rows for current page or selected ones, results can be written in current page: insert, append, or replace data in defined location of the table.

2.14 Data Charts

Hover or click button “Function” to select functions under menu item “Charts”.

2.14.1 XY Chart

2.14.1.1 Data

1. Select data rows. Data filter can be set.

2. Select type of XY Chart.

3. Select one column as "Category Axis", to define data names.

4. Select several columns as "Value Axis". Different value series are shown in different colors or shapes.

5. By default, "Category Axis" is the horizontal axis and "Number Axis" is the vertical axis.

6. To invalid data:, skip or count as zero.

7. When display all data rows(all pages), need concern memory limitation.

8. Click button “Menu” to set parameters of chart.

9. Click button “Pop” to display current chart as image in popped window.

10. Click button “Data” to display data of the chart in data table.

11. Click button “Html” to display data of the chart in html page.

2.14.1.2 Bar Chart

1. Represents data size with bars' heights.

2. "Category" column is always counted as strings.

2.14.1.3 Stacked Bar Chart

1. Represents data size with bars' heights.

2. "Category" column is always counted as strings.

2.14.1.4 Line Chart

1. Represents data trend with lines connecting points.

2. "Category" column can be counted as strings or numbers.

2.14.1.5 Scatter Chart

1. Represents data distribution with symbols.

2. "Category" column can be counted as strings or numbers.

2.14.1.6 Bubble Chart

1. Represents data size with circles of different radius：

2. "Category Column" and "Value Column" define coordinates of data.

3. Select several columns as "Size Columns" to defines data size.

4. All columns should be numbers. Size columns should be non-negative.

2.14.1.7 Area Chart

1. Represents data size with area size.

2. "Category" column is always counted as strings.

2.14.1.8 Stacked Area Chart

1. Represents data size with area size.

2. "Category" column is always counted as strings.

2.14.1.9 Parameters of Data in Chart

1. Options about label: not display, point, value, category, etc.

2. Location of labels.

3. Font size of labels.

4. Decimal scale.

2.14.1.10 Layout

Set parameters of plot: title, font size, location of legend, zero-lines, grid lines, line width, etc.

2.14.1.11 Category Axis

Set parameters of category axis: label, font size, location, tick, count values as string or number, coordinate, etc.

2.14.1.12 Number Axis

Set parameters of number axis: label, font, location, tick, coordinate, etc.

2.14.2 Pie Chart

Data numbers are represented as percentages with a circle divided into segments.

1. Select data rows. Data filter can be set.

2. Select one column as "Category Axis", to define data names..

3. Select another column as "Number Axis". Values should be non-negative.

4. When display all data rows(all pages), need concern memory limitation

5. Click button “Menu” to set parameters of chart.

6. Click button “Pop” to display current chart as image in popped window.

7. Click button “Data” to display data of the chart in data table.

8. Click button “Html” to display data of the chart in html page.

2.14.3 Box-and-whisker Chart

2.14.3.1 Way of Calculation

Box-and-whisker chart represents data distribution:

1. Sort data according to column/row/all in ascending order.

2. Following items can show aggregation and discreteness of data:

Minimum Q0 = in 0%(start) of the data list

Lower quartile Q1 = in 25% of the data list

Median Q2 = in 50%(middle) of the data list

Upper quartile Q3 = in 75% of the data list

Maximum Q4 = in 100%(end) of the data list

3. Following values can be used to mark ourliers of data:

Lower extreme outlier line E1 = Q1 - 3 * ( Q3 - Q1 )

Lower mild outlier line E2 = Q1 - 1.5 * ( Q3 - Q1 )

Upper mild outlier line E3 = Q3 + 1.5 * ( Q3 - Q1 )

Upper extreme outlier line E4 = Q3 + 3 * ( Q3 - Q1 )

4. Following values can be referred for discreteness:

Mean = avarage of the data list

2.14.3.2 Options of Calculation

1. Select data rows. Data filter can be set.

2. Select objects: According to columns, rows(select category column), or all.

3. Select columns to be calculated.

4. Set conditions of sorting, and maximum data number to take.

5. To invalid values: skip, or count as zero.

2.14.3.3 Statistic Data

Generated statistic data can be viewed and edited.

2.14.3.4 Options of Chart

1. Box width

2. Whether display outliers lines or mean

3. Whether display connection lines of values, and whether dotted lines

4. Whether transpose

5. Maximum data number in chart

6. Random colors

7. Click button “Menu” to set parameters of chart.

8. Click button “Pop” to display current chart as image in popped window.

9. Click button “Data” to display data of the chart in data table.

10. Click button “Html” to display data of the chart in html page.

2.14.4 Self Comparison Bars Chart

2.14.4.1 Way of Calculation

Self comparison bars show difference between data and reference values. Bars are calculated by following rulers:

1. If value is zero, no bar

2. When compare as absoluate values:

maximum_value = maximum_absolute_value_of_column/row/all

precentage = absolute_value / maximum_value

width = maximum_width * precentage

color = If value is larger than zero, color_of_column.

If value is less than zero, inverted_color_of_column

3. When compare as range of minimum and maximum:

maximum_value = maximum_value_of_column/row/all

minimum_value = minimum_value_of_column/row/all

precentage = (value - minimum_value) / (maximum_value - minimum_value)

width = maximum_width * precentage

color = color_of_column

2.14.4.2 Options of Calculation

1. Select data rows. Data filter can be set.

2. Select columns to be calculated.

3. Select columns to be copied.

4. Set conditions of sorting, and maximum data number to take.

5. Select how to handle invalid data.

6. Set decimal scale.

7. When display all data rows(all pages), need concern memory limitation.

2.14.4.3 Options of Chart

1. Select objects to compare: columns/rows/all.

2. Select comparison way: absoluate values, or range of minimum and maximum.

3. Set maximum width of bars.

4. Set maximum data number in chart.

5. Select whether display row numbers, values, percentages, categories, calculated values.

6. Edit data in chart.

7. Edit html of chart.

2.14.5 Comparison Bars Chart

2.14.5.1 Way of Calculation

Comparison bars show difference between two series of data. Bars are calculated by following rulers:

1. If value is zero, no bar

2. When compare as absoluate values:

maximum_value = maximum_absolute_value_of_value_columns

precentage = absolute_value / maximum_value

width = maximum_width * precentage

color = If value is larger than zero, color_of_column.

If value is less than zero, inverted_color_of_column

3. When compare as range of minimum and maximum:

maximum_value = maximum_value_of_value_columns

minimum_value = minimum_value_of_value_columns

precentage = (value - minimum_value) / (maximum_value - minimum_value)

width = maximum_width * precentage

color = color_of_column

2.14.5.2 Options of Calculation

1. Select data rows. Data filter can be set.

2. Select one column as category column(unnecessary).

3. Select two columns to be compared.

4. Select columns to be copied.

5. Set conditions of sorting, and maximum data number to take.

6. Set decimal scale.

7. When display all data rows(all pages), need concern memory limitation.

2.14.5.3 Options of Chart

1. Select comparison way: absoluate values, or range of minimum and maximum.

2. Set maximum width of bars.

3. Set maximum data number in chart.

4. Select whether display row numbers, values, percentages, categories, calculated values.

5. Edit data in chart.

6. Edit html of chart.

2.14.6 XYZ Chart

1. Base on echarts-gl, 3D charts can be displayed in html with technic of WebGL.

2. Webview of JavaFX does not support WebGL, so generated html will be displayed by system web browser.

3. Pleas make sure your system web browser supports WebGL and does not limit local JavaScript files.

2.14.6.1 3-D Scatter

1. Select data rows. Data filter can be set.

2. Select data axises:

• Select one column as axis X. Select as string or number.

• Select one column as axis Y. Select as string or number.

• Select several columns as axis Z. Select as string or number.

3. Select other columns to be popped in labels

4. Set parameters of the chart: projection, color, width, height, whether dark, point size.

2.14.6.2 Surface Chart

1. Select data rows. Data filter can be set.

2. Select data axises:

• Select one column as axis X. Select as string or number.

• Select one column as axis Y. Select as string or number.

• Select one column as axis Z. Select as string or number.

3. Select other columns to be popped in labels.

4. Set parameters of the chart: projection, color, width, height, whether dark, wire frame.

2.14.7 Locations Distribution

Display location data in map dynamically.

2.14.7.1 Options of Data

1. Select data rows. Data filter can be set.

2. Select one column for each og following: Label, Longtitude, Latitude, and Size.

3. Select other columns to be displayed in popped labels.

4. Select coordinate system of data.

5. Set conditions of sorting, and maximum data number to take.

2.14.7.2 Data in Map

Data referred by map are displayed in table.

2.14.7.3 Options of Map

1. Map type: TianDi map, or GaoDe map.

2. Select projection and size of map.

3. Set image and text of markers.

4. Select whether pop information.

5. Select controls of map.

2.14.7.4 Options of Playing

1. Select timer:

• Delay: After display current frame, wait for defined time, and then display next frame.

• Interval: Keep defined time between current frame and next frame.

2. Select whether: Loop, Reverse, Accumulate, Move Center, Link.

3. Auto-play.

4. Display specific frame.

5. Buttons of navigation.

6. Snapshot:

• Select background color and dpi.

• Snap frame by frame, and display them in “Edit Images List”.

7. Set maximum data number in chart.