Subscribe Bookmark RSS Feed

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

Joined:

Jun 23, 2011

Solution
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).
5 REPLIES
ms

Super User

Joined:

Jun 23, 2011

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]

 

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

Joined:

Jun 23, 2011

Solution
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).
That worked! Thank you very much!
pmroz

Super User

Joined:

Jun 23, 2011

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.