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
markschahl
Level V

Unstacking a Stacked Excel Cell or Parsing Free-form Text to Generate Multiple Rows

We all run into some version of this: Excel table formatted to look nice, no thought given to analyzability.

In the free-form text cell, it looks like this:

 

m/d: text [alt+enter]
m/d: text [alt+enter]
m/d: text [alt+enter]
m/d: text [alt+enter]
...
m/d: text [alt+enter]

 

When the data are exported to JMP, the cell gets collapsed into: m/d: text m/d: text m/d: text ... m/d: text.
I want to parse that long string with n elements into a separate row for each element, not split into separate columns. n is variable. This would create a new datatable that copies the other columns and adds the parsed element. Instead of Text-to-Columns, this is Text-to-More-Rows.

Thankfully #/# is used consistently, so I could count those and loop n times.

Has anyone out there done something similar?

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: Unstacking a Stacked Excel Cell or Parsing Free-form Text to Generate Multiple Rows

I do this sort of thing a lot; you just have to slog through it.  Here's an example where Column 1 has multiple values, and Column 2 is static.

dt = New Table( "Test", Add Rows( 3 ), Set Cell Height( 83 ),
	New Column( "Column 1", Character, "Nominal",
		Set Values(
{"5/3: ABCDE
5/4: DEF
5/5: GHI",
"5/6: xyz
5/7: foo
5/8: bar
5/9: Hello
5/10: World",
"4/16: The
4/17: Entire
4/18: Scheme
4/19: Of
4/20: Things
4/21: Is broken"} ),
		Set Display Width( 156 )
	),
	New Column( "Column 2", Character, "Nominal", Set Values( {"A", "B", "C"} ) )
);

dtnew = new table("Text_to_Rows",
	new column("Column 1", Character, "Nominal"),
	new column("Column 2", Character, "Nominal"),
);

delim = "
";
m = 0;
for (i = 1, i <= nrows(dt), i++,
	c1_words = words(dt:column 1[i], delim);
	one_c2   = dt:column 2[i];

	for (k = 1, k <= nitems(c1_words), k++,
		dtnew << add rows(1);
		m++;
		dtnew:Column 1[m] = c1_words[k];
		dtnew:Column 2[m] = one_c2;
	);
);

 

View solution in original post

3 REPLIES 3
pmroz
Super User

Re: Unstacking a Stacked Excel Cell or Parsing Free-form Text to Generate Multiple Rows

I do this sort of thing a lot; you just have to slog through it.  Here's an example where Column 1 has multiple values, and Column 2 is static.

dt = New Table( "Test", Add Rows( 3 ), Set Cell Height( 83 ),
	New Column( "Column 1", Character, "Nominal",
		Set Values(
{"5/3: ABCDE
5/4: DEF
5/5: GHI",
"5/6: xyz
5/7: foo
5/8: bar
5/9: Hello
5/10: World",
"4/16: The
4/17: Entire
4/18: Scheme
4/19: Of
4/20: Things
4/21: Is broken"} ),
		Set Display Width( 156 )
	),
	New Column( "Column 2", Character, "Nominal", Set Values( {"A", "B", "C"} ) )
);

dtnew = new table("Text_to_Rows",
	new column("Column 1", Character, "Nominal"),
	new column("Column 2", Character, "Nominal"),
);

delim = "
";
m = 0;
for (i = 1, i <= nrows(dt), i++,
	c1_words = words(dt:column 1[i], delim);
	one_c2   = dt:column 2[i];

	for (k = 1, k <= nitems(c1_words), k++,
		dtnew << add rows(1);
		m++;
		dtnew:Column 1[m] = c1_words[k];
		dtnew:Column 2[m] = one_c2;
	);
);

 

markschahl
Level V

Re: Unstacking a Stacked Excel Cell or Parsing Free-form Text to Generate Multiple Rows

Peter:

Thanks! That did the trick. I owe you a drink of your choice.
I am working on the creators of the data to input the data in flat format...

pmroz
Super User

Re: Unstacking a Stacked Excel Cell or Parsing Free-form Text to Generate Multiple Rows

Ha ha sounds good.  Hope to see you at Discovery 2020.