topic Change all columns names dynamically in Discussions

Change all columns names dynamically

terapin — Wed, 14 May 2014 18:19:37 GMT

I'm opening a text file that I want to concatenate with an existing JMP file. Unfortunately, the column names of the two files don't match and therefore can't be easily concatenated. I'm trying to change the column names of the text file to match those of the specific JMP file it will be concatenated to. To further complicate things, the quantity of columns and their positions change in the various text and JMP data files. For example

Text File 1 Column Names

tair_avg rh_avg rain_total dew_point_avg leaf_wetness_min windspeed_avg

JMP data file 1

Tair_(C) RH(%) Rainfall(mm) Dew Point (C) Leaf Wetness(mV) Windspeed (mph)

Text File 2 Column Names

rh_avg tair_avg dew_point_avg rain_total windspeed_avg tsoil_5cm_avg tsoil_15cm_avg

JMP data file 2

RH (%) Tair_(C) Dew Point (C) Rainfall(mm) Windspeed (mph) Tsoil @ 5cm (C) Tsoil @ 15cm (C)

I'm not sure what is the best way to conditionally evaluate and change the text files column names to match those in the corresponding JMP data file. Since all the column names are consistent amongst the various JMP data files, that is air temperature is always Tair (C), I was thinking the use of a lookup table or multiple If Then Else's might work.

If colname = tair_avg Then colname = Tair (C) Else

If colname = rh_avg Then colname = RH (%) Else

If colname = windspeed_avg Then colname = Windspeed (mph)

......

However, since my JMP files may contain upwards of a 100 columns, the use of that many IF statements didn't seem practical or efficient. I would appreciate any ideas or suggestions on how best to accomplish this.

Re: Change all columns names dynamically

pmroz — Wed, 14 May 2014 19:49:32 GMT

// List of all possible columns in your text file

text_col_list = {"tair_avg","rh_avg","rain_total","dew_point_avg","leaf_wetness_min","windspeed_avg"};

// List of all new column names, in the same order as text_col_list

jmp_col_list = {"Tair_(C)","RH(%)","Rainfall(mm)","Dew Point (C)","Leaf Wetness(mV)","Windspeed (mph)"};

dt = data table("Text File 1");

col_name_list = dt << get column names(string);

for (i = 1, i <= nitems(text_col_list), i++,

if (contains(col_name_list, text_col_list[i]),

column(dt, text_col_list[i]) << set name(jmp_col_list[i]);

);

Re: Change all columns names dynamically

terapin — Thu, 15 May 2014 20:22:48 GMT

Thanks PMroz. Your solution is much better than the one I figured out which involved a lot of Try commands (see below).

Try(

Type( Column( "name" ) );

Column( dt, "name" ) << Set Name( "Name" );

);

Try(

Type( Column( "age" ) );

Column( dt, "age" ) << Set Name( "Age" );

);

Try(

Type( Column( "air" ) );

Column( dt, "air" ) << Set Name( "Tair (C)" );

);