Subscribe Bookmark RSS Feed

Change all columns names dynamically

terapin

Community Trekker

Joined:

Jun 23, 2011

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

// 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]);

    );

);

2 REPLIES
Solution

// 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]);

    );

);

terapin

Community Trekker

Joined:

Jun 23, 2011

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)" );

);