turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Combining entries of N columns into single column ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 8, 2017 6:41 PM
(1915 views)

Hi,

I have N columns, which have character data that I want to consolidate into a single column. For a given row many of the columns can be blank or contain repeated/same values ( as compared to the entry in another column ).

I know there is the function under Cols --> Utilities --> Combine Columns & this will generate a new column with all the fields side by side & delimited. I need advise on how to do this process while maintaining uniqueness ( i.e. only append the values for that column if there are no other columns w/in that row which have the same value )

Ideally I'd like to get guidance on how to script this in JSL. However, clever ( yet more tedious ) approaches w/in JMP data tables/functions are welcome also!

Thanks.

Solved! Go to Solution.

3 ACCEPTED SOLUTIONS

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 8, 2017 7:52 PM
(3709 views)

Solution

Here is one way to solve this question. I have created a formula that combines the unique values from a list of columns. I have attached a sample data table with the formula applied to the last column

Below is the formula used

```
VarList = {"One", "Two", "Three", "Four"};
DataList = {};
CountList = {};
DataValue = "";
For( i = 1, i <= N Items( VarList ), i++,
Insert Into( DataList, As Column( VarList[i] ) );
Insert Into( CountList, 0 );
);
For( i = 1, i <= N Items( VarList ) - 1, i++,
For( k = i + 1, k <= N Items( VarList ), k++,
If( DataList[i] == DataList[k],
CountList[k] = CountList[k] + 1
)
)
);
For( i = 1, i <= N Items( VarList ), i++,
If( CountList[i] == 0 & DataList[i] != "",
DataValue = DataValue || "," || DataList[i]
)
);
DataValue = Substr( DataValue, 2 );
```

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 9, 2017 1:10 AM
(3701 views)

Solution

Here's an alternative to Jim's formula. The difference is that the unique values of the combined string are sorted alphabetically rather than in order of occurence.

The number of columns can generalized by setting the local parameter *N*.

```
dt = Current Data Table();
dt << New Column("Combined",
Character,
Formula(
Local({dt = Current Data Table(), N = 4},
Concat Items(Associative Array(dt[Row(), 1 :: N]) << get keys, ",")
)
)
);
```

(It uses data table matrix notation, and will thus only in JMP 13 or later)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 13, 2017 4:41 PM
(3628 views)

Solution

I prefer the previous answers, but you can also do this via point-and-click, which is attractive to those who don't script (Apparently, there exist people who don't script!!)

As with the associative array approach, the resulting column has the values sorted least to greatest.

1) Select the original columns, and using the combine columns utility, create a multiple response column.

2) Select the new column and apply the text to columns utility, being sure to check the check the "make indicators" box.

3) After selecting the 10 new indicator columns, apply the combine columns utility, being sure to check the "columns are indicator columns" box.

6 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 8, 2017 7:52 PM
(3710 views)

Here is one way to solve this question. I have created a formula that combines the unique values from a list of columns. I have attached a sample data table with the formula applied to the last column

Below is the formula used

```
VarList = {"One", "Two", "Three", "Four"};
DataList = {};
CountList = {};
DataValue = "";
For( i = 1, i <= N Items( VarList ), i++,
Insert Into( DataList, As Column( VarList[i] ) );
Insert Into( CountList, 0 );
);
For( i = 1, i <= N Items( VarList ) - 1, i++,
For( k = i + 1, k <= N Items( VarList ), k++,
If( DataList[i] == DataList[k],
CountList[k] = CountList[k] + 1
)
)
);
For( i = 1, i <= N Items( VarList ), i++,
If( CountList[i] == 0 & DataList[i] != "",
DataValue = DataValue || "," || DataList[i]
)
);
DataValue = Substr( DataValue, 2 );
```

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 13, 2017 8:55 PM
(1815 views)

Thank you Txnelson for the quick reply & solution!

This works fine, however, my column names are somewhat long & I have many so prefer not to have to manually specify the list.

I could get the column names using a simple dt << Get Column Names and store it.

I also really liked how super user "ms" arranged the entries in alphabetical order which reduces the # of permutations ( though I didn't ask for it )

Sorry for the delayed response.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 9, 2017 1:10 AM
(3702 views)

Here's an alternative to Jim's formula. The difference is that the unique values of the combined string are sorted alphabetically rather than in order of occurence.

The number of columns can generalized by setting the local parameter *N*.

```
dt = Current Data Table();
dt << New Column("Combined",
Character,
Formula(
Local({dt = Current Data Table(), N = 4},
Concat Items(Associative Array(dt[Row(), 1 :: N]) << get keys, ",")
)
)
);
```

(It uses data table matrix notation, and will thus only in JMP 13 or later)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 13, 2017 8:59 PM
(1810 views)

Thanks a lot Ms!!

I like the flexibility of this solution including being able to specify the start and end column # to be used. I really like that you also made the sorting in alphabetical order to reduce the # of permutations!

Sorry for the delayed response. I initially thought there was a corner case problem where it was not working but after revisiting it I don't see an issue.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 13, 2017 4:41 PM
(3629 views)

I prefer the previous answers, but you can also do this via point-and-click, which is attractive to those who don't script (Apparently, there exist people who don't script!!)

As with the associative array approach, the resulting column has the values sorted least to greatest.

1) Select the original columns, and using the combine columns utility, create a multiple response column.

2) Select the new column and apply the text to columns utility, being sure to check the check the "make indicators" box.

3) After selecting the 10 new indicator columns, apply the combine columns utility, being sure to check the "columns are indicator columns" box.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nov 13, 2017 9:15 PM
(1804 views)