Subscribe Bookmark RSS Feed

how to find the empty columns and delete them by scripting

robust1972

Community Trekker

Joined:

Jan 15, 2014

how to find the empty columns and delete them by scripting. any helps and/or hints will be very appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
ms

Super User

Joined:

Jun 23, 2011

Solution

I can think of several ways to do this. Here's an example based on the built-in Missing Data Pattern command.

dt = Current Data Table();

names = dt << Get Column Names();

dt1 = dt << Missing Data Pattern(columns(Eval(names)), invisible);

n = N Row(dt1);

For(i = N Col(dt), i >= 1, i--,

    If(Col Sum(Column(dt1, i + 3)) < n,

        Remove From(names, i)

    )

);

Try(dt << delete columns(names));

Close(dt1, nosave);

2 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Solution

I can think of several ways to do this. Here's an example based on the built-in Missing Data Pattern command.

dt = Current Data Table();

names = dt << Get Column Names();

dt1 = dt << Missing Data Pattern(columns(Eval(names)), invisible);

n = N Row(dt1);

For(i = N Col(dt), i >= 1, i--,

    If(Col Sum(Column(dt1, i + 3)) < n,

        Remove From(names, i)

    )

);

Try(dt << delete columns(names));

Close(dt1, nosave);

ian_jmp

Staff

Joined:

Jun 23, 2011

As an alternative (and probably one of Marcus' 'several ways'):

dt = CurrentDataTable();

m = dt << getAllColumnsAsMatrix;

// Loop over the columns and record which are entirely missing

c2del = {};

for(c=1, c<=NCol(m), c++,

if(NRow(Loc(IsMissing(m[0,c]))) == NRow(m), InsertInto(c2del, c));

);

// Delete the columns

dt << deleteColumns(c2del);