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

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

d_barnett — Tue, 13 Feb 2018 21:56:18 GMT

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

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

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

tsandidge — Thu, 12 May 2016 16:22:57 GMT

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.

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

Jeff_Perkinson — Thu, 12 May 2016 16:40:09 GMT

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

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

stan_koprowski — Wed, 19 Oct 2016 02:38:18 GMT

Another option is to use the Column Names Utility Addin.

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

Best,

Stan

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

tsandidge — Thu, 12 May 2016 16:51:40 GMT

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