Editing tabular data using the grid editor

ArgonStudio provides a grid editor for editing CSV tabular data. The interface is somewhat similar to Excel and supports a number of features intended to ease the task of preparing data for visualization. In this article, we cover the basics of using the grid editor for editing data.

Loading CSV data into the grid editor

  • Click the open-folder button. A file-selection box appears.
  • Navigate to the directory where the CSV is located and select it and click Open.
  • The CSV is loaded into the grid editor. The first row of the CSV file must contain column headers. Type identification is automatic and uses the first few rows of the CSV data to decide on the type - number, text, etc.

Saving grid contents as CSV

  • Once the grid has been edited and manipulated to your satisfaction, you may want to save the contents as CSV data.
  • Click the save button. A file-saver dialog appears.
  • Choose where you want to save the CSV file and click Save.
  • The file is saved with the CSV headers in the first row as it appears on the grid.
  • The data is saved as it appears in the grid. That means if a filter is in effect, only the filtered rows are saved.

Saving the selection

  • You can select rows in the grid by using the mouse and moving it while holding the mouse button down.
  • You can also select individual rows by using Control-click and Shift-click.
  • To save the selected rows, click on the hamburger menu icon and choose Save selected from the menu.
  • A file-saver dialog appears and you can save the data as CSV.
  • The column headers are also written to the CSV file.

Deleting the selection

  • Select rows from the grid.
  • Add and remove rows from the selection using Shift-Mouse and Control-Mouse.
  • Choose Delete selection from the hamburger menu.

Showing and hiding columns

  • To hide columns or show columns that are hidden, click on the hamburger menu.
  • Scroll down to the Columns section and show or hide columns by clicking on the appropriate checkbox.
  • Saving the grid data to CSV when a column is hidden skips the column from the export.

Filtering rows using javascript

  • Once data is loaded into the grid editor, you can filter it. Click the filter button (1) to show the filter panel.
  • Filtering can be done using a javascript function or an excel expression. Let us start with the javascript function; ensure that the Javascript function tab (2) is active.
  • Let us filter for rows where the column 2015-Added is more than 1000. Enter the following expression into the text box (3).
    row => row["2015-Added"] > 1000
    This code defines a function which accepts a row argument and returns a true or false for selecting the row.
  • Click the execute filter button (4).
  • The filter is applied and the matching rows are shown in the grid, and the count of matching rows is updated.

Filtering rows using an excel-like expression

  • Some people may find it easier to filter using an excel-like expression rather than javascript. For such circumstances, we have the excel-like filtering functionality.
  • Click the filter button (1) to show the filter panel.
  • Ensure that the Excel expression tab (2) is selected.
  • Let us filter for rows where the second column value (2015-Added) is more than 1000. Note that this column corresponds to column B in excel notation. Enter the following expression into the text box (3).
    B1 > 1000
    This code selects rows for which the column B is more than 1000. Note that even though we have used B1, the code uses the value from column B in each row (since the cell reference is a relative reference). This is similar to how Excel works in certain circumstances - appropriately adjusting relative cell references.
  • Click the execute filter button (4).
  • The filter is applied and the matching rows are shown in the grid, and the count of matching rows is updated.

Column search and replace

  • ArgonStudio supports column-based search and replace in the grid editor. A column is searched for a regular expression pattern and the matches replaced.
  • Click on the search-and-replace button (1) in the top header bar.
  • Select the column to be searched (2)
  • Enter the search pattern (3) which must be a regular expression pattern.
  • Click to open the options dropdown (4).
  • Choose whether to replace the first match only or all matches within a single column value.
  • To ignore case when matching text, check the Ignore case checkbox.
  • Enter the replacement text, if any (5). If this field is left blank, the pattern matches will be deleted.
  • Finally, click the run button (6) to execute the search and replace.

Adding a new column using an excel-like expression

  • Let us now learn how to add a new column in the grid editor. A new column can be added to compute a value depending on other existing columns.
  • Click on New Column in the hamburger menu as shown.
  • The Add Column dialog is shown. Enter a name for the column as shown (1).
  • We will enter an excel expression for computing the column value. Ensure that the Excel expression tab is selected (2).
  • For this example the value is very simple: we want to compute the difference between the columns BeforeTax and AfterTax. Enter the expression =B1 - C1 (3) into the text box.
  • The textbox for entering the expression supports auto-completion (or Intellisense). Enter a partial text as shown and a menu shows suggestions, and a brief help (4). (If the menu does not show, type Control-space.)
  • Click Add Column, and the column will be added. Click Close to close the dialog.
  • The new column with the values are as shown.

Adding a new column using a javascript expression

  • It is also possible to add a new column using a javascript expression (actually a function accepting a row as the parameter).
  • Ensure that the Javascript tab is selected (1).
  • Enter the expression row => row.BeforeTax - row.AfterTax into the text box (2).
  • Note that as you type the expression, the textbox offers suggestions, including column names (which you can select by typing Tab.) (3 and 4).
  • Click Add Column, and the column will be added. Click Close to close the dialog.

Change column name

  • Sometimes you may want to change the name of a column from a long unwieldy name to a shorter name.
  • This can be accomplished quickly and easily. Hover over the column header and click the dropdown menu icon (1).
  • From the dropdown menu, choose Change Name (2).
  • The Change Column Name dialog appears. Edit the column name as desired (1).
  • Click Apply to apply the change (2).
  • The grid shows the updated column name.

Updating a column value

  • Editing a column value is a frequently required operation to massage the value using a function. To do this, hover over the column and click the dropdown menu icon (1).
  • Choose Update Value from the dropdown menu.
  • The Update Column Value dialog is shown. It shows the column in 1.
  • Ensure that the Javascript tab is selected (2).
  • We want to remove the $ sign and the comma from the value to ensure it is recognized as a number. So enter the following expression to delete these characters from the value.
    value => value.replace(/[\$\,]+/g,'')
  • Click Update to apply the change.
  • The column values are updated as shown below.

Creating a chart

  • Once the data has been cleaned up, manipulated and massaged to our satisfaction, we can proceed to create a chart.
  • Creating a chart from the grid editor is easy. Just click on the chart button (1) and the data from the grid editor (2) will be transferred to the charting module.

Summary

In this article, we looked at some features of the ArgonStudio grid editor. With this information, you should be able to comfortably use the grid editor to clean-up, manipulate and whip your data to shape easily.

Click here to get started.

See Also