merging two databases

[Anonymous]

I was wondering how to merge two databases that have the same cases.  For example, I have climate data by state for a specific year and data about ticks and incidence of lyme disease by state in a different database.  How do I merge those two databases to give me one database?

 

[Jonathan Sandoe]

There is no explicit way to merge data sets at this time in CODAP, but you can create an attribute that refers to the value of an attribute in another data set.

Suppose you have data set “Climate” as above with “Year” as an attribute, and you have another, “Lyme Disease”, with attributes, “Year” and “Incidence”. You can create an attribute “occurrences” in “Climate” and bring over the values from the other data set with a formula:

lookupByKey(“Lyme Disease”, “occurrences”, “Year”, Year)

You can do this with as many attributes as necessary to, effectively, merge the data sets.

Jonathan

• This reply was modified 9 months, 3 weeks ago by Bill Finzer. Reason: Fix typo

[Anonymous]

Thanks! I’ll give that a try!

[Anonymous]

So first, how does the function find the other database if it isn’t a database that is already loaded and part of CODAP?  I have tried to bring in two databases and the first just gets deleted and the new one replaces it.

Second, I’ve been working with the lookUpByKey(“database1″, attribute from database 1 with the values I want”, “attribute in database 2 where I want values imported to”, keyValue) function.  I’m assuming I have correctly interpreted what the first three terms refer to, but I’m not sure what keyValue refers to.  I am assuming the function needs to know how cases are identified so that the data is imported to line up with the cases properly, but maybe I’m suppose to provide some different bit of info here.  In laymen terms, what is “keyValue”?   I’m definitely doing something wrong because I’m just getting the function written in, not the values. Thanks!

[Jonathan Sandoe]

Hello Traci,

I was assuming that you had the two data sets already in a single CODAP document, but apparently that is not the case. It seems that you have two separate CODAP documents, one with a Climate data set and the other with the Lyme Disease data set.

A single CODAP document can have many datasets. Each data set corresponds with exactly one Case Table, so you can see how many data sets you have by opening the Case Table menu in the CODAP tool bar.

There are many ways to get a data set into a CODAP document. It is easiest if the source data is in a comma separated values (“CSV”, usually a file with a ‘.csv’ extension) file or a tab delimited text file (usually with a ‘.txt’ extension.) To bring one of these files into CODAP simply drag the file and drop it in the CODAP workspace. You can also drag and drop a URL that refers to a CSV or tab delimited file. You can drag and drop as many of these files as you like and they will be added to the current collection of data sets. If you drag a ‘.codap’ file over the workspace that will replace the current CODAP document with the one in the file.

So, if you have the data in two separate CODAP documents, and don’t have an equivalent CSV or tab delimited file, what do you do? Well, that is easy! CODAP can export the data from its tables into a CSV file. Select the Case Table for the data set and click on the ruler icon on the bottom of the right-hand inspector bar. Look for “Export Case Data…” in the menu that opens up.

Concerning the “lookUpByKey” function: it is not the easiest of functions to grasp. I have made a simple example document to demonstrate, here. I hope it makes clearer how it is set up.

Jonathan

[Tom Button]

I’ve just spent quite a while getting this to work. It took me a long time to work out that it was lookupByKey and not lookUpByKey

[Bill Finzer]

Hello Tom,

Since the time the original posts in this thread were written CODAP added a “join” capability that does all the heavy lifting for you. See this help page.

I’ll also fix the typo in Jonathan’s post.

Bill

[Author]

Posts