Importing Data into Ravel

Importing Data into Ravel

Any data can be loaded into a spreadsheet, because the rows and columns in a spreadsheet are just placeholders. Ravel is pickier, since its dimensions—the equivalent of a spreadsheet’s rows and columns—specify a unique piece of information in your data. Therefore, every cell must have a unique identifier in terms of the dimensions of the data. The first task in using Ravel is to choose the columns in a CSV file that give you a unique specification of your data.

Ravel advantageous, in that virtually every program can output CSV. It is also problematic, because there is no pre-defined format, and no error correction for bad data, in CSV files. A file can have no header at all, or many lines of header. What is supposed to be a date field can have characters accidentally stored in them—a “B” rather than an “8” from a file created by an OCR program, for example—or the formatting can change from one date format to another without warning. Some knowledge of the source data is therefore necessary when importing a file.

Excel — transactional database, since there are numerous columns—such as _BORROWERS_CTY_ which takes codes—such as _HU_ —then generate the full text country names— _Hungary_ —in the _Borrowers' country_ column. Only one of these columns-- _BORROWERS_CTY_ or _Borrowers' country_ —is needed to uniquely specify the Country, so it is sensible to import one column and ignore the other.

Figure 73: The BIS data in Excel

Choosing which columns to import and which to ignore, until a unique key is generated for each data point, is the role of the Import Data form.

. You can also activate this function by choosing “Import Data” from the File menu. Either action will (a) attach a parameter named dataImport on your mouse pointer, and (b) call up the Import Data form shown below (the precise layout of this form may change as we improve the process in future releases).

Figure 74: The data importing form

import (alternately, you can enter an URL into the next text window and Ravel will download that data file from the web). Then click Load and the form will load the file for import processing. The figure below shows the BIS file loaded.

Figure 75: The data importing form

Ravel Ravel ’s interpretation of a file will be accurate, but sometimes it will, for example, identify the wrong row

data entry in the _BORROWERS_CTY_ field as the name of the field (HU, the code for Hungary).

As you might expect by now, there are several right-click menu items that help you correct these errors. Right click on the row where _BORROWERS_CTY_ appears in the file, and the first option is “Set as header row”.

Figure 76: Right-mouse button menu items in the data importing form

The colour of the row will change from red to green; if you now follow up with “Populate column labels”, all entries for that row will become the Name of the relevant column. There’s still an error here—check the figure below to see if you can spot it—but for now we’ll move on to the next stage of importing data, deciding which columns to convert into Dimensions, and which column (or columns) to identify as data.

Figure 77: Steps in importing data

should be ignored, while it has interpreted the other fields visible here as axes. We know that the _BORROWERS_CTY_ and _Borrowers' country_ fields are mirror images of each other, so one should be ignored.

the “other” multiple times (if there are several contiguous columns that you wish to ignore, or

relevant action—ignore, axis or data—from one of the Dimension dropdowns, and this choice will be applied to all selected columns).

imported as Dimensions, while the data in the red-coloured columns will be ignored.

Figure 78: Steps in importing data

final step is to specify which columns contain data. Often that will be a single column, but sometimes—as in this case—there will be multiple columns.

Ravel calls these the “Horizontal Dimension”. Often, they will be unrelated to each other, but in this case, the Horizontal Dimension is the time dimension of this data, which is Quarterly, starting with 1940-Q2. In this import, we named the Horizontal Dimension “Date”, specified that it was a time dimension, and specified its format as 1999-Q4 from the top-down menu on the format field: the year with 4 digits, followed by a dash – and the letter Q, and then the number of the quarter.

Figure 79: Steps in importing data

red—see “Adjusted for breaks” in the first column. To show that that row is in fact the first row of data, and that the date data starts where 1940-Q2, right click on that line in the cell below 1940Q2 and choose “Set start of data row, and column”.

We are now ready to import the data, so click on “Import”. If this process succeeds (we’ll cover what to do if it doesn’t succeed shortly), the form will close, and the parameter dataImport will be renamed to that of the file being imported—in this case, _WS_TC_csv_col_ , which Ravel formats as _WSTCcsvcol_ because of LaTeX’s formatting rules:

Figure 80: A Ravel data-storing parameter

Aggregating data on import

loan in each quarter into a unique number for that quarter. But if you are loading a transactional database, like the sales data shown in Figure 3 and reproduced here in Figure 81, then you will want to aggregate sales for each day (or other timestep—week, month, quarter, year) when you import.

Figure 81: Transactional data has several entries per day

This is handled by altering the default “Duplicate Key Action” from “throw Exception” to “sum”, as shown in Figure 82. This will then add up any entries with duplicated entries for the data’s four dimensions.

Figure 82: Aggregating data on import using “sum” as the action to perform if there are duplicate keys

Page 70 www.patreon.com/Ravelation

Other options include returning the product of duplicated entries, the minimum, maximum, and average.

Figure 83: Control options on the data importing form

Data Reduction with Ravel

To analyse your newly-loaded data, you have to connect this parameter to a Ravel. Once you do this, you will see something like the figure below (this Ravel has 7 dimensions, and at present Ravel hides the axis names with more than 6 dimensions; to see the name, you hover the mouse over an axis).

Figure 84: Attaching a data-storing parameter to a Ravel

At this point, the data may need further reduction. Sometimes this will be because something unimportant is coded that was still necessary to get a unique key for each element in the Ravel—like, for example, whether the data was collected by a government agency or a private body. Other times it will be because data on an axis needs to be aggregated to get a complete file for analysis.

The latter is the case for this Ravel. There is a dimension called Valuation method which has two entries, Market Value and Nominal Value—the former being the market price of debt and bonds

etc., the latter being the face value of bonds. Every country in the database reports government debt data in Nominal Value terms— except South Korea . So, to include South Korean government debt in the analysis, we collapse this axis with the aggregation set to Sum (Max or Min would also have worked). There is also data which has not been adjusted for structural breaks, and data which has. To use just adjusted data, we move the selector dot to “Adjusted for breaks” on this axis. We also set Lending Sector to “All”, since this matches data previously published by the US Federal Reserve. Ravel then outputs a 4D object, as shown in the next figure.

Figure 85: Reducing unneeded detail using Ravel

If Data Importing Fails

can include non-numeric data in fields that are supposed to control only numbers, date formats that alter within a date field, and extraneous information stored at the end (or sometimes the beginning) of a CSV file, and so on…

Ravel can identify problematic rows and columns in a CSV vile, and produce an error-report version of that file for examination in a text or spreadsheet editor. It is also possible to import valid data from a file and exclude invalid data, by ticking on the “Don't fail on invalid data” option on the Import form. That will load valid data and exclude invalid data.