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
d_barnett
Level IV

How to use only some columns from a row as column headers?

As part of a database export I get a file with some headers that I can use straight away ( from Experiment ID to DOE_FILE_ID) but then I get some 'generic' headers of C1 to C255

 

11523_Capture.JPG

what I need to do is to select all of the first row of data which has C1 to C255 as their headers and replace it with the information from the selected first row

 

so C1 would be changed to Sample, C2 would be OLD Sample Identifier etc. For every table I get this information will be different so I can't use a Find & Replace to do this.I cannot change the way that this data is imported using preferences as it is from a database.

 

Many regards

 

 

David

2 ACCEPTED SOLUTIONS

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: How Do I Use Only Part of a Row of Data as Column Header?

If you're okay doing it interactively, you can use copy and paste.

Select the cells in the first row that have the column names. Then select the columns in the columns panel on the left and paste them.

See this blog post for a more complete explanation, with a video.

-Jeff

-Jeff

View solution in original post

stan_koprowski
Community Manager Community Manager

Re: How Do I Use Only Part of a Row of Data as Column Header?

Another option is to use the Column Names Utility Addin.

Select the columns you want to move up and then click move up.

11530_Screenshot 2016-05-12 12.43.05.png

Best,

Stan

View solution in original post

4 REPLIES 4
tsandidge
Level III

Re: How Do I Use Only Part of a Row of Data as Column Header?

To start out with,

colNames = dt << get column names;

For( i = 1, i <= N Items( colNames ), i++,

  Column( i ) << set name( Column( i )[1] )

);

// delete the first row

dt << select rows( 1 );

dt << delete rows;

This will load all the column names into colNames, loop over the list, and then set each name to its first element.

For your case, I would use a regex filter to loop over the column names, adding the columns beginning with "^C\d" to a separate list.

colNames = dt << get column names;

badCols = {};

For( i = 1, i <= N Items( colNames ), i++,

    cname = Column( colNames ) << get name;

    If( !(Is Missing( Regex( cname, "^C\d" ) )),

        Insert Into( badCols, colNames )

    );

);

Show( badCols );

To then rename those specific columns, you can do the following:

For( i = 1, i <= N Items( badCols ), i++,

    cname = Column( badCols ) << get name;

    Column( cname ) << set name( Column( cname )[1] );

);

I'm still working on selecting the first cells of the relevant columns and deleting that cell (the column names), and you may also then want to delete the last row if it is empty, but this is a good place to start. I'll update when I figure that last piece out, but you may beat me to it.

I hope this helps.

tsandidge
Level III

Re: How Do I Use Only Part of a Row of Data as Column Header?

I guess I should have mentioned I was assuming you wanted to make a button out of it. Script > Button > Efficient repetition.

Jeff_Perkinson
Community Manager Community Manager

Re: How Do I Use Only Part of a Row of Data as Column Header?

If you're okay doing it interactively, you can use copy and paste.

Select the cells in the first row that have the column names. Then select the columns in the columns panel on the left and paste them.

See this blog post for a more complete explanation, with a video.

-Jeff

-Jeff
stan_koprowski
Community Manager Community Manager

Re: How Do I Use Only Part of a Row of Data as Column Header?

Another option is to use the Column Names Utility Addin.

Select the columns you want to move up and then click move up.

11530_Screenshot 2016-05-12 12.43.05.png

Best,

Stan