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
saitcopuroglu
Level IV

text to columns but in a neat way

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
KarenC
Super User (Alumni)

Re: text to columns but in a neat way

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.

View solution in original post

6 REPLIES 6
Hegedus
Level IV

Re: text to columns but in a neat way

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.

KarenC
Super User (Alumni)

Re: text to columns but in a neat way

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
Level IV

Re: text to columns but in a neat way

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

Re: text to columns but in a neat way

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

JMP Systems Engineer, Health and Life Sciences (Pharma)
saitcopuroglu
Level IV

Re: text to columns but in a neat way

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

Re: text to columns but in a neat way

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

JMP Systems Engineer, Health and Life Sciences (Pharma)