Subscribe Bookmark RSS Feed

Re: text to columns but in a neat way

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

Hello all,

I have a column of character data set which consist of codes of discounts we give our agencies. Also there are rows with empty data (no discount)

8371_Screen Shot 2015-03-15 at 10.39.35.png

I would like to get a result as below without generating any additional row:

8372_Screen Shot 2015-03-15 at 10.46.04.png

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

If I was Julian I would make a cool video, however, this solution is easier than it appears so hang in there as I explain.

As is often the case, there is more than one way to solve your data cleaning.  The option suggested will work well if you do not have too many codes as you will have to manually set up each column.  However, if I were to use formulas in manually set up columns I would use the formula:

If( Contains( :Packet, "CT" ),

  "CT",

  Empty()

)

This would give you the structure you are looking for.  If you have lots of codes then another way would be to use the categorical platform (Analyze>Consumer Research>Categorical). 

You will need an ID column (I just added a new column and used the "initialize data" option under column info to create a column of unique numbers.  Next use the categorical response analysis, select the "Multiple" tab, select your packet column and then choose "Multiple Delimited" and use your ID column as the "X Grouping Category".  (see below)

[Missing image]

Now your output (which is just an intermediate step) will look like:

[Missing image]

Now under the main red triangle menu select Save Tables>Save Frequencies to get a table that looks like:

[Missing image]

Finally, if you need to you could use the column recode to change 1's to the codes and the 0's to blanks.  Also, you could join this back to the original table using the ID to match on.

6 REPLIES
Highlighted
Hegedus

Community Trekker

Joined:

Jun 23, 2011

Hi,

One way to do this is to create the different discount column as in your preferred output table and then give each of them a formula that tests if the column "packets" contains that code such as:

8377_Untitled.png

or in JMP script

Contains( Packets, "CT" );

This will give a 1 if test (does the Packet row contain "CT") and a 0 if it does not.

Solution

If I was Julian I would make a cool video, however, this solution is easier than it appears so hang in there as I explain.

As is often the case, there is more than one way to solve your data cleaning.  The option suggested will work well if you do not have too many codes as you will have to manually set up each column.  However, if I were to use formulas in manually set up columns I would use the formula:

If( Contains( :Packet, "CT" ),

  "CT",

  Empty()

)

This would give you the structure you are looking for.  If you have lots of codes then another way would be to use the categorical platform (Analyze>Consumer Research>Categorical). 

You will need an ID column (I just added a new column and used the "initialize data" option under column info to create a column of unique numbers.  Next use the categorical response analysis, select the "Multiple" tab, select your packet column and then choose "Multiple Delimited" and use your ID column as the "X Grouping Category".  (see below)

[Missing image]

Now your output (which is just an intermediate step) will look like:

[Missing image]

Now under the main red triangle menu select Save Tables>Save Frequencies to get a table that looks like:

[Missing image]

Finally, if you need to you could use the column recode to change 1's to the codes and the 0's to blanks.  Also, you could join this back to the original table using the ID to match on.

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

Thank you Karen,

Köszönöm Hegedus,

Even the solution of Hegedus is also ok, the help of Karen saved me a lot time hence the packets are coded in 67 different ways.

Many many thanks all!

Byron_JMP

Staff

Joined:

Apr 26, 2012

These are some good suggestions for solving this problem....

There is a really nice solution in JMP 12 and it almost made me cry.

In JMP 12 this is a menu item and it only takes about 5 clicks.

Cols/Utilities/Text to Columns, then add the delimiter, and check the "Indicator Columns" box. Click OK

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

Really nice! But the data table is being updated every hours, I can not automate it by scripting, or can I? I guess not

Byron_JMP

Staff

Joined:

Apr 26, 2012

or maybe you can...

Straight from the scripting index:

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Consumer Preferences.jmp" );

dt << Text To Columns(

  delimiter( "," ),

  columns( :Brush Delimited ),

  makeindicatorcolumns(1)//ok, so this line wasn't in the scripting index, but it was an intuitive guess

);

--Note: This is a new function in JMP12