59180_en-US

liliconcom

Importing Excel Sheets with wrapped cells content

Hi,

 

though I know it's better to avoid wrapped cells in Excel for Analysis, I have a customer who has this kind of data. Importing with the Excel import wizard imports also the wrapped cells, though you'd have to span the rows larger to see all the entries. These columns are character and nominal by default. However could not be used for Analysis. Imagine th first column has multiple countries in one cell (DE, US, ES), the second column has in one cell the related numeric values (e.g. scores like 10, 15, 7). That means the scorefor Germany is 10, for US 15 and ES 7.

 

 How to deal with that kind of data? Is there a way of importing it into seperated lines?

(This is just a simplified version of the data, you can think of additional variables and grouping variables like study/project and so forth where one of these cells belongs to one of the studies each.)

 

What I've tried so far:

To use this data in JMP you can use a multiple response variable and set the property "Multiple Response" to a delimiter "", because a wrap does not have a delimiter as "," or ";". So far so good, but the data is now analyzed by frequencies rather than numeric values. So a bunch of analysis methods cannot be used.

 

Stacking, Splitting and other table manipulations did not work for me as they don't account for a multiple response column and put the whole entry as one value. Expanding to columns I was also not successful, though I just might not found the trick. However this seems to have already many steps involved for a nice solution, but better any solution than none 

 

I guess the best solution is to provide the data in a different structure/format in Excel already. Or do you have any other suggestion?

 

Attached the excel file and a script to import and change the modeling type to Multiple response.

Thanks,

Martin

 

Re: Importing Excel Sheets with wrapped cells content

Hi Brady, thanks a lot! I strongly recommend the customer to change the format in the source, as this is possible to handle with JSL but could become even more cumbersome if there are more columns in addition one needs to take care of.

The stack option with contiguous and multiple series is now also more clear to me.

Thanks,

Martin

Re: Importing Excel Sheets with wrapped cells content

This data is really ugly due to the wrapping. See if this works; it seems to work for me.

 

The main ideas are:

 

Collapse whitespace, replace newlines with commas, text to columns, contiguous multiple series stack.

 

In addition there are a few cleanup operations like column deletions, empty row deletions, & type conversions.

 

Names Default To Here(1);

dt = Open(pickfile(),
 Worksheets( "Sheet1" ),
 Worksheet Settings(
  1,
  Has Column Headers( 1 ),
  Number of Rows in Headers( 1 ),
  Headers Start on Row( 1 ),
  Data Starts on Row( 2 ),
  Data Starts on Column( 1 ),
 )
);

dt << new column("C2", formula(collapse whitespace(substitute(:Country, "\!n",","))));

dt << new column("D2", formula(collapse whitespace(substitute(:Data, "\!n",","))));

dt << select columns({"C2", "D2"});

dt << text to columns(delimiter(","));

dt << Delete Columns;

dt << Delete Columns (1,2);

collist = dt << get column names(string);
colExpr = insert(expr(columns()), colList);

stackExpr = Expr(
 dtStack = dt << Stack(
  _CCC_, 
  Source Label Column( "Label" ),
  Stacked Data Column( "Data" ),
  Number of Series( 2 ),
  Contiguous
 )
);

substituteInto(stackExpr, expr(_CCC_), nameexpr(colExpr));
stackExpr;

dtStack << delete Columns (1,3);

dtStack << select where (:Data == "") << delete rows();

close(dt, nosave);

column(dtStack,1)<<SET name=""></SET>

Re: Importing Excel Sheets with wrapped cells content

Hmm, good idea, but I cannot use it as it asks for 2 columns to be stacked together but I only can select one. Attached an example table, may I'm just use it wrong.

 

Correction, I can use it if I change the delimiter in the table to "," with

 

 

For Each Row(
new1 = Substitute( Column( 1 )[], "\!n", "," );
Column( 1 )[] = new1;
 
);
For Each Row(
new2 = Substitute( Column( 2 )[], "\!n", "," );
Column( 2 )[] = new2;
 
);

 

 

However, it still does not look like I would expect the end table to be, which is something like:

 

ID	Country	DATA
1	DE	10
1	Arg.	15
1	USA	13
2	DE	10

 

Re: Importing Excel Sheets with wrapped cells content

Using the Multiple Response Column idea you proposed puts you into a great position to use @brady_brady's Data Table Tools Add-in.  There's a utility in there that will let you convert back and forth between Multiple Response and Stacked.  Once it's stacked you can then just change the modeling and data types to what's appropriate for the data.

 

Best,

 

M