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

rename columns

I am creating a script that will import a .txt file and do some manipulation. I want the column names to be a concatenation of rows 1 and 2. In other words, on row 1 I have things like "length" and "width," on row 2 I have things like "meters" and "centimeters," and on rows 3 through x I have the values. So I want to concat "length" with "meters" and make it the column name for column one, with all the data values starting on the new row 1.

Any pointers on how to get started or suggestions of some things that might be helpful?

Thanks.
1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: rename columns

It worked for me, but I could reproduce the same error with another file I tried. I have edited the script above: char() is used to avoid that concat fails for numeric column names. And I changed the counter i to j because the forum sometimes refuse to show i within square brackets (at least my browser did not show the names properly).

Now it should be complete. At least it works as expected for me with a range of text files (JMP9 for Mac).

View solution in original post

5 REPLIES 5
ms
Super User (Alumni) ms
Super User (Alumni)

Re: rename columns

There are probably many ways this can be scripted. Here is one example using the different import text options. A tab delimited file is assumed here.

 

There are probably many ways this can be scripted. Here is one example using the different import text options. A tab delimited file is assumed here.


[PRE]
//Open only first two rows
dt = Open(
 "path/file.txt",
 Import Settings(
  End Of Field( Tab ),
  Labels( 1 ),
  Column Names Start( 1 ),
  Data Starts( 2 ),
  Lines To Read( 1 ),
 )
);
// Create a list of new column names
names = {};
For( j = 1, j <= N Col( dt ), j++,
 Insert Into( names, Column( j ) << get name() || " " || Char(Column( j )[1]))
);
Close( dt, nosave );

//Open again, include all data from row 3 
dt = Open(
 "path/file.txt",
 Import Settings(
  End Of Line( CRLF, CR, LF ),
  End Of Field( Tab ),
  Column Names Start( 1 ),
  Data Starts( 3 ),

 )
);

//Set column names
For( j = 1, j <= N Col( dt ), j++,
 Column( j ) << set name( names[j]))
[/PRE]

 

Re: rename columns

Thanks you very much. I think that's really close and I think I understand what you're doing. However, I'm getting this error when I try to implement it:

::names = {};
For( ::i = 1, ::i <= N Col( ::dt ), ::i++,
	Insert Into(
		::names,
		Column( ::i ) << get name ||  /*###*/" " ||  /*###*/Column( ::i )[1] /*###*/

Any ideas?  I basically took your script and changed some of the import proferences.  Other than that, it's the same.

ms
Super User (Alumni) ms
Super User (Alumni)

Re: rename columns

It worked for me, but I could reproduce the same error with another file I tried. I have edited the script above: char() is used to avoid that concat fails for numeric column names. And I changed the counter i to j because the forum sometimes refuse to show i within square brackets (at least my browser did not show the names properly).

Now it should be complete. At least it works as expected for me with a range of text files (JMP9 for Mac).

Re: rename columns

That worked! Thank you very much!
pmroz
Super User

Re: rename columns

FWIW I avoid using j as a looping variable, because it's a JSL matrix function!

J(nrows, , )
Function: Creates a matrix of identical values.
Returns: The matrix.
Arguments:
nrows Number of rows in matrix. If ncols is not specified, nrows is also used as ncols.
ncols Number of columns in matrix.
value The value used to populate the matrix. If value is not specified, 1 is used.

j will work as a variable, but I tend to use i, k, m, n, etc.