cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
RonB
Level III

Copy column subset or cell range from table to table

I have two data tables. Some columns on table2 are identical to columns table1.

 

I wasnt to copy the data of the identical columns from table2 to table1.

 

That means, of course, that (1) table1 will be expanded with the number or rows in table2 and, (2) there will be missing data in table1. That's OK.

 

My issue is how to copy the data from table2 to table1. 

 

E.g. - both tables have column "NAME". Table1 has 1000 rows, table2 has 2000 rows.

The results will be: table1 with 3000 rows, and "NAME" on table1 will be a "concatenation" of both NAME columns. Other columns in table1 will have 2000 missing/empty data.

 

BTW, in reality, tables are much larger and there are 10-20 identical columns (out of ~100). I will deal with it once I figure out the basic function. I mention it in case there's an issue with such size.

 

Many thanks

15 REPLIES 15
RonB
Level III

Re: Copy column subset or cell range from table to table

I'll give it a try and report back (may take a while due to unrelated circumstances).

Many tx
RonB
Level III

Re: Copy column subset or cell range from table to table

Worked. Many thanks. Now I just have to figure out how to copy the non-matching columns but I think I can manage.

Jeff_Perkinson
Community Manager Community Manager

Re: Copy column subset or cell range from table to table


@RonB wrote:

Now I just have to figure out how to copy the non-matching columns but I think I can manage.


Non-matching columns from which table? The example above keeps all the columns from the Fitness table and concatenates only the columns that have the same name from Big Class. It does this by only subsetting Big Class to get the columns with the same names and then concatenating that to Fitness (which still has all of its columns). There shouldn't be any work to keep the non-matching columns.

-Jeff
RonB
Level III

Re: Copy column subset or cell range from table to table

Yes, I understand that. But the data structure I'm dealing with requires to copy also columns in Big Class that do not have the same name in Fitness. I.e., these columns will be "vertically concatenated" to the output table as new columns. These columns will begin with missing data (as many rows as are in Fitness) followed by the data from Big Class.
I think I can do it by first adding to Fitness new columns with all the names in Big Class that are different. Than use the same method you showed me, subsetting Big Class with only these columns and doing the concatenation.
It's a lot of work but I see no other way to deal with the large number of tables and columns I have.
Thanks yet again
Jeff_Perkinson
Community Manager Community Manager

Re: Copy column subset or cell range from table to table


@RonB wrote:
Yes, I understand that. But the data structure I'm dealing with requires to copy also columns in Big Class that do not have the same name in Fitness. I.e., these columns will be "vertically concatenated" to the output table as new columns. These columns will begin with missing data (as many rows as are in Fitness) followed by the data from Big Class.

How is this different from simply concatenating the two tables together to begin with? Doing that you'll end up with a table with all the rows and all the columns from both tables. The columns that don't have matching names in both tables will be empty for the rows that didn't have those columns originally.

 

Let's stop talking abstractly and be more concrete. Here are two tables:

 

Table1

A B C
1 1 1
2 2 2
3 3 3

 

Table2

A B D
4 4 4
5 5 5
6 6

6

 

Using Tables->Concatenate on these two tables, you'll end up with this table.

 

Table3

A B C D
1 1

1

2 2 2
3 3 3
4 4 4
5 5 5
6 6 6

 

The way I interpret your description above makes me think that's the table you want. If it's not, I'm sorry I've misunderstood your requirement. 

-Jeff
RonB
Level III

Re: Copy column subset or cell range from table to table

Something didn't work for me with the example.
But, never mind, I got the idea and it seemed to work with running a test on few input files..
Seems like I complicated things unnecessarily while a straight forward way was under my nose.
Thanks for your patience