Skip to content
How to link index w...
 
Notifications
Clear all

How to link index with a variable from a table

4 Posts
2 Users
0 Likes
211 Views
Avatar
Posts: 2
Topic starter
(@nshanbog-1)
New Member
Joined: 2 months ago

I have imported a csv containing time series data. I then exported the columns into a new variable. I need to link the variable to an index. For ex, the index is 'Year' and the variable is 'Demand'z. How do I do this?

Demand - Grid[Column="Demand"]

Year - [2018,2019,2020]

Any help will be appreciated!

Topic Tags
3 Replies
Lonnie Chrisman
Posts: 40
Admin
(@lchrisman)
Member
Joined: 14 years ago

Your description is pretty vague and there are tons of variations on what you might have done so far, and exactly what you mean by "link to an index".

Here is one interpretation:

  1. You selected File / Import..., and then selected your *.csv file.
    This resulted in two index nodes (say Row and Column) and a variable node named Grid.
  2. You right-clicked on each of the column headers and selected "New variable from column...".
    So now you have several variables, each indexed by Row.
  3. Each Row of your CSV corresponds to one year, and you already have a Year index. Your challenge is how to change the Grid variable, and each of the variables representing one column of the original data, to be indexed by the existing Year index rather than the Row index that the import created.

In this case, I would swap the order of steps 2 & 3. First, reindex your original full table by Year, and then split that into individual variables. That way you only need to do the reindexing once.

The exact form of your CSV table will depend on the specifics of your CSV file. But let's assume you ended up with Row := 1..3, and your Year index is 2018..2020. If you are willing to make the assumption that there is a one-to-one correspondence -- i.e., the first row is the first year, then positional re-indexing is the way to go. Create a new variable, Grid_by_Year defined as:

Grid[@Row=@Year]

Then you can use "New variable from column..." on this new variable, and all your separated variables will be indexed by Year.

Because there are so many other possible variations on this, my interpretation of where you are at could be the wrong one. In that case, let me know.

 

 

Reply
Avatar
Posts: 2
Topic starter
(@nshanbog-1)
New Member
Joined: 2 months ago

A better explanation of what I'm trying to do is a MdTable

 

My Row and Column of my table are

1..31999

and

['Day','Time','Timestamp','YEAR','MONTH','Hydro','Thermal','Gas','Solar','Wind','Demand','Nuclear','Net demand','TOD']

 

The table T, is indexed by Row and Column. 

 

I now need to create an MdTable whose indexes are Year, Month and TOD while the 'measure' values are Timestamp and Hydro. I need similar MdTables for Thermal, Gas, Solar, Wind, Nuclear and Net demand. 

 

 

Reply
Lonnie Chrisman
Posts: 40
Admin
(@lchrisman)
Member
Joined: 14 years ago

Double check you want TOD as an index, and at what resolution. A timestamp would not be a good candidate for an index, for example, since you'd almost never have two records with the same timestamp. If your TOD is 24 buckets, and you have pretty complete coverage (most combinations of Day and TOD are represented in your incoming relational table), then that would be fine as an index. I'm just checking...

Let's assume you already have your Year, Month and TOD indexes set up for the final result(s).  You made the statement that " the 'measure' values are Timestamp and Hydro". You could use MdTable to create a "fact table" with a Measure index having these two items (so the result is 4-D, with indexes Year, Month, TOD and Measure), but I would recommend against that. Instead, you should break these out into two separate variables because they are two different types of information with different units, and which would be used in different formulas. Thus, the basic MdTable transformation is like this:

Variable Timestamp ::=
    LocalIndex L := [ 'YEAR', 'MONTH', 'TOD', 'Timestamp' ];
    MdTable( Data[ Column=L ], Row, L )

This expression makes a few assumptions: 

  • Your relational table is named Data and its indexes are named Row and Column.
  • The column labels are as written for L -- these are case-sensitive
  • You have set up indexes with the same names for the result (i.e., named Year, Month and TOD). The index names are not case sensitive.
  • Those result indexes are already defined with the possible values.

You would, of course, repeat this for Hydro:

Variable Hydro ::=
   LocalIndex L := [ 'YEAR', 'MONTH', 'TOD', 'Hydro' ];
   MdTable( Data[ Column=L ], Row, L )

Since you will repeat this for every measure column, you could make this a UDF:

Function DataColumnAsArray( colName ) ::=
    LocalIndex L := [ 'YEAR', 'MONTH', 'TOD', colName ];
    MdTable( Data[ Column=L ], Row, L )

So now you could instead define these as, e.g.,

Variable Hydro ::= DataColumnAsArray( 'Hydro' )

 

 

Reply
Share: