Searching tables, for modification

In short, I need to find specific data in a table, and change cells based on that search.

I’m attempting to append one table’s columns to another table, and this needs to be done at rows matching at two certain columns (name and date). So, to the first table, that has name, date and other columns, I want to add columns from another table and the names and date needs to match between the first and second table.

So, my strategy is to search up the index in the first table that has the name of that for the second table, and use that index to insert data from the second table into the first. Same about the dates, for each name.

I've made the dimensions of the first table correct by appending zeroed columns and added column names, making room for the new data. So far so good. Now I need to figure out the indexes such that I can with T(rows, vars) insert the data from the second table into the first. But T(rows, vars) seems to return a new table, while I want to modify the original.

Matlab has in this area high-level functionality (see "Access Data in Tables"), but I want to index into a table in order to change it. That is, it is searches with the purpose to modify, not read-only operations. Any ideas how to approach this? If my approach is right, the first step is to find the indices in the first table where the data is to be inserted.

(It’s unbalanced panel data; all data is in CSV files. I actually have about hundred of the second table(s), but the principle is the same I think, I just will have to loop.)

[Stephen23]

"’m attempting to append one table’s columns to another table, and this needs to be done at rows matching at two certain columns (name and date)."

Use join or innerjoin or outerjoin.

Try outerjoin with the MergeKeys option set to true.

Answer 1

[Lei Hou]

Hi Frans,

To index into a table, you should use T{rows, vars} instead of T(rows, vars).

May I ask a question about why didn't you use join in your use case? Was that because there are rows in the second table that don't match any rows of the first table? Or, the key variables for the second table don't contain all unique combinations of values in the key variables for the first table?

[Frans]

Hi Stephen and Lei, thanks a lot for your replies. Matlab's join functions are indeed intereresting. I had no idea about them, because my Google searches hadn't come near them. It seems my script gets the job done, at around 125 lines of code, but will look at the these join functions for future problems.

Though, one problem is that my keys doesn't match exactly. The key is date, and I need to match the date that is closest to the date in the first table. Currently I've solved it by in a loop adding one day to the original date, until I get an exact match. How would you solve this with any of the join functions?

To me it feels like I want to supply a "custom comparison operator" to the join function. I guess one way to hack around it is to change the dates in the table such that the join will work.

[Stephen23]

"one problem is that my keys doesn't match exactly."

The keys need to match exactly.

"How would you solve this with any of the join functions?"

I would not change the dat data. Find the closest dates using min etc. Add new columns/variables to both tables containing the matching indices (to be used as keys). Use whichever join serves your needs.