World Statistics Day was yesterday, but we’re celebrating all week long! This celebration means acknowledging the impact statistics has on our world. Who is your favorite statistician? Share with us who they are and why they top your favorites list.
Choose Language Hide Translation Bar
Highlighted
sgourshe
Level II

Transposing very large data table

Hi, 

I have a very large data file, (20+ million rows, 10 columns) that I need to transpose. Unfortunately, jmp is not capable of executing such large file quickly or efficiently. I found that jmp is able to manipulate data in text format much easily. I have tried all options of save/open as .txt but no vail..

 

Can someone help transposing this table either in txt format or data table (list, array etc.)? 

I need the following example table to look like the output below, without using the "transpose" function as it won't work for million+ rows: ''

 

Data table

 col1     col2

  a           l

  s           w

  4           j

  t            sdf

  t

  o

  p

 

Desired output (either as a txt file or a table with only 1 col and 2 rows): 

col1, a,s,4,t,t,o,p

col2,l,w,j,sdf

 

Please and Thank you :) 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Craige_Hales
Staff (Retired)

Re: Transposing very large data table

I think you have non-numeric columns so you can't use a matrix, You might make your string value as a concatenation of items in a list, and you can get a list from a character column with the <<getvalues message.

alist = dt:a<<getvalues; show(nitems(alist));
astring = concatItems(alist,","); show(length(astring),left(astring,100));

N Items(alist) = 21000000;
Length(astring) = 207635028;
Left(astring, 100) = "1,4,9,16,25,36,49,64,81,100,121,144,169,196,225,256,289,324,361,400,441,484,529,576,625,676,729,784,";

 

JMP has a limit of 2 billion characters for a string. You might be getting close.

 

Craige

View solution in original post

8 REPLIES 8
Highlighted
Craige_Hales
Staff (Retired)

Re: Transposing very large data table

I think you have non-numeric columns so you can't use a matrix, You might make your string value as a concatenation of items in a list, and you can get a list from a character column with the <<getvalues message.

alist = dt:a<<getvalues; show(nitems(alist));
astring = concatItems(alist,","); show(length(astring),left(astring,100));

N Items(alist) = 21000000;
Length(astring) = 207635028;
Left(astring, 100) = "1,4,9,16,25,36,49,64,81,100,121,144,169,196,225,256,289,324,361,400,441,484,529,576,625,676,729,784,";

 

JMP has a limit of 2 billion characters for a string. You might be getting close.

 

Craige

View solution in original post

Highlighted
sgourshe
Level II

Re: Transposing very large data table

Super thank you for quick reply! Easier than I thought. :)
Anyway, I can assign a particular cell in a table to to be this astring?
such that I have a table that has (taking the original example I used) 1 column and 2 rows looking like the following:
Column1
Row1: a,s,4,t,t,o,p
Row2: l,w,j,sdf
Highlighted
Craige_Hales
Staff (Retired)

Re: Transposing very large data table

Yes, http://www.jmp.com/support/help/Accessing_Data_Values.shtml#790448 has an example like this:

dt:age[10] = 20; // set the value of age in row 10 to 20

You might want to make a prototype 2-line table with the column names (and data types!) you need and use the "get script" from the red triangle menu. You can then paste that script into your own code.  In the example above, [10] is for row 10; you might use two assignments for [1] and [2].  The 20 is for a numeric column; your column is character if the astring variable is going to be stored in it.

Craige
Highlighted
sgourshe
Level II

Re: Transposing very large data table

Very cool thanks. Although useful, I was particularly wanting to get only the string value into the cell. Unfortunately the string includes extra comma seperations for empty cells. 

 

ie. given there are 2 columns, the 1st col has 10 rows and the 2nd col only has data for 5 rows. When I implement the string concate that you mentioned above I get the string value for second col as astring = "a,s,r,w,d,,,,,,"

 

I am particulary interested to get only "a,s,r,w,d", excluding the extra comma seperations. Any directions you can point me to? I'd great appreciate it. 

 

Thank you, 

Shiv 

Highlighted
Craige_Hales
Staff (Retired)

Re: Transposing very large data table

Highlighted
sgourshe
Level II

Re: Transposing very large data table

That is great! Thank you Craige for great support!

Highlighted
ctsc
Level I

Re: How to configure Python for JMP 14?

@Paul_Nelson, building on GM1's latest post:

 

running this script:

Clear Log();
Names Default To Here( 1 );
Python Init(Path("/Library/Frameworks/Python.framework/Versions/3.7/lib/libpython3.7.dylib"), 
    Use Python Version("3.7")	
);

Python Submit("\[

print("Done importing modules")

]\");

Python Term();     

 

gives the following output in the log:

Screen Shot 2019-01-22 at 5.30.40 PM.png

 

Running this JSL script:

Clear Log();
Names Default To Here( 1 );
Python Init(Path("/Library/Frameworks/Python.framework/Versions/3.7/lib/libpython3.7.dylib"), 
    Use Python Version("3.7")	
);

Python Submit("\[

import numpy as np
print("Done importing modules")

]\");

Python Term();      

 

gives the following output to the log:

Screen Shot 2019-01-23 at 9.52.26 AM.png

 

GIven the succesfull exit code, I think this error is very unclear. JMP/JSL seems to not know where to find modules to import?? Any help is greatly appreciated

Highlighted

Re: How to configure Python for JMP 14?

When you specify the python location such as
Python Init(Path("/Library/Frameworks/Python.framework/Versions/3.7/lib/libpython3.7.dylib"),
Use Python Version("3.7")
);
You are stating that this is the specific version of Python you want to use, located where you specified. If you call Ptyhon Init() with no arguments, or fewer arguments such as Python Init(Use Python Version(“3.7”) ), JMP does more searching to try to locate an appropriate Python. On the Mac you will likely always need to specify at least the version since Apple supplies Python 2.7 with the Operating System. We are continuing to work on improving the Python support in JMP.
Article Labels

    There are no labels assigned to this post.