Data in Two-dimensional Storage Structure

1 Data Objects

1.1 External Formats

Following objects can be edited in consistent way:

• Data files, including CSV File, Excel file, texts file.
• Data of MyBox Clipboard
• Matrices
• Database tables.

1.2 Storage Structure

Data are represented as two-dimensional storage structure:

• "Columns" define attributes/fields of data and extend data dimensions in horizontal direction.
• "Rows" store instances of data and extend data set in vertical direction.
• Data should be in same width. That is all rows have equal number of columns.

2 Create Data

Data can be created by one of following ways:

• Click or hover button "Create Data", and select data type: CSV, Excel, Texts, MyBox Clipboard, Matrix, Database Table.
• Click button "Load contents in System Clipboard", and select rows and columns.
• Click or hover button "Examples", and select example data. Option "Only import definition".

New data are temporary data and need necessary parameters, like file name or table name, inputted when saved.

3 Open Data

Existed data can be opened by one of following ways:

• Click or hover button "Select File" to load data in extrenal data file.
• Click button "Select"(CTRL+T or ALT+T) to load data managed by MyBox.

4 Four Modes

4.1 Page data in html - read only

• Display data of current page in html.
• Can read data while can not modify data.
• Can view data definition.

4.2 Table - edit

• Display data of current page in table.
• Can modify data.
• Can modify data definition.

Except for texts file, multiple lines can be edited and saved to string values:

• When the value is single line(not include 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 includes line breaks: Text field is shown when click the data cell; Write the text in multiple lines directly.

4.3 CSV - edit

• Display data of current page in CSV format.
• Can modify data.
• Can modify data definition.
• Click button "Delimiter" to reload data and apply different delimiter. This delimiter does not affect source file.
• If a value contains delimiter or line break, the value should be surrounded by quotes.

4.4 Page data in texts - read only

• Display data of current page in texts format.
• Can read data while can not modify data.
• Can view data definition.
• Click button "Delimiter" to reload data and apply different delimiter. This delimiter does not affect source file.

5 Define Data

In mode "Table - edit" or mode "CSV - edit", click button "Define Data".

5.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 "Recover" to discard modifications of columns and pick data from "Table" of current data.

5.2 Types of Columns

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

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

3. Longitutde and Latitude are defined together generally.

5.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.

5.3.1 Format for Numbers

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

5.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..

5.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..

5.3.4 Define Enumeration

To Enumeration, list of values can be defined.

5.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.

5.5 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.

5.6 Attributes of Data

Under tab "Attributes", set: data name, decimal scale, maximum value of random, and description.

6 Verify Data Values

1. The verified objects can be either rows in current page or all rows.

2. Following are checked:

• If a column is defined as "not null", then null values are invalid for this column.

• If a value is not satisfied with the column type, then the value is invalid.

• Click button "Verify data in current page".

• Option: Verify values automatically when save data.

3. Options:

   • Validate data when edit.

   • Validate data when save.

7 Edit Data

Principle of column usages is "Most tolerability and least manufacture".

7.1 Load Data

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

7.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".

7.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.

7.4 Edit Data Cell

1. Click editable cell to start editing.

2. When editing is started, its original value is displayed, while both type and format of column are ignored.

3. 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.

4. Press key ENTER to commit modification, and press key ESC to cancel editing.

5. Option: Auto-commit modification when cell loses focus(click other control).

6. When modification is committed, 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.

7. 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".

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

9. Option: Validate data when edit. That is, data are not validated when edit if this option is not selected.

8 Save Data

8.1 Interface

• Click button "Save"(CTRL+S or ALT+S) to write modification in data.
• Changes of rows in "Table", including modify/add/delete/sort, affect rows of current page in file.
• Changes in columns and attributes, including modify/add/delete/sort, affect all rows in file.
• Changes of attributes and columns are saved in database.
• Click button "Save As"(F5 or CTRL+B or ALT+B) to write data in other format: CSV, Excel, Texts, MyBox Clipboard, Matrix, Database Table, System Clipboard, JSON, XML, HTML, PDF.

8.2 Save Data File

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

Option: Validate data when save. That is, data are not validated when save if this option is not selected.

8.3 Save Database Table

Values in database table are always validated.

Values are written into database table as nearest types:

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

8.4 Save Matrix

Values in matrix are always validated.

All values in Matrix are saved as Double.

9 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).

10 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".

11 Manage Data

MyBox records definition of data objects in its internal table:

12 Data File

Data files are external data. MyBox records their definition and keeps their independence.

After read/write by MyBox, data files should be able to read/write by other ways properly.

12.1 CSV File

In CSV file:

• In general, the first line(header) defines column names, and each of followed lines defines a row of data.
• Values are separated by "delimiter" which can be string.
• If a value contains delimiter or line break, it should be surrounded by quotes.
• If delimiter is not "#", then lines started with "#" will be skipped(as comments).

When MyBox handles CSV file:

• When the file is opened for the first time, the tool guesses its delimiter and charset.
• If file is read abnormally, use menu "File - Format" to change options and click button "OK".
• Data can be saved as different charsets and delimiters.

12.2 Excel File

In Excel file:

• In general, the first line(header) defines column names, and each of followed lines defines a row of data.

When MyBox handles Excel file:

• If file is read abnormally, use menu "File - Format" to change options and click button "OK".
• With menu "File - Sheet", select one sheet to handle, or add/rename/edlete sheets.
• Data can be saved with current sheet only or all worksheets.

Notice: Tool can only handle base data in Excel file. If file includes format, style, formula, or chart, suggest to save changes as new file to avoid data loss.

12.3 Texts File

In texts file:

• In general, the first line(header) defines column names, and each of followed lines defines a row of data.
• Values are separated by "delimiter" which can be string. Regular expression is supported when parse the file.
• Values should not contain delimiter nor line break.
• If a line starts with "#", it will be skipped.

When MyBox handles texts file:

• When the file is opened for the first time, the tool guesses its delimiter and charset.
• If file is read abnormally, use menu "File - Format" to change options and click button "OK".
• Not support multiple lines in values.
• Data can be saved as different charsets and delimiters.

13 Temporary Data File

Temporary data files are generated when:

• Import example.
• Create data chart.
• Caculate data.

Temporary data are saved in CSV format, under internal temporary files path of MyBox by default.

If files are under MyBox temporary files path:

• When MyBox exits, this path will be cleared automatically. So please save temporary data under other path if need.
• They will not be backed up automatically when edit.
• They will not be recorded when read or write, and will not appear in the list of "Recently accessed files".

Option: Save temporary data under "generated" path.
When this option is checked, temporary data files are not treated as "temporary files"。

More details can be referred in "User Guide - Data Tools"