Subscribe Bookmark RSS Feed

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

d_barnett

Community Trekker

Joined:

Nov 30, 2011

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

4 REPLIES
tsandidge

Community Trekker

Joined:

Mar 17, 2016

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

Community Trekker

Joined:

Mar 17, 2016

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

Joined:

Jun 23, 2011

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

Joined:

Aug 8, 2012

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