how to drop a pandas multi level dataframe column when all sub columns are completely blank

user_15482062 0
Viewed 289

dataframe:

|--------------------------------------------------------------------|
|    Name        |    email          |  Phone no    |   Gender       |
|----------------|-------------------|--------------|----------------|
|legacy | target |legacy    | target |legacy|target |legacy | target |
|-------|--------|----------|--------|------|-------|-------|--------|
|Name1  |Name1   |n1@abc.com|        |      |       |       |        |
|Name2  |Name2   |          |        |      |   12  |       |        |
|--------------------------------------------------------------------|

Expected output:

|---------------------------------------------------|
|    Name        |    email          |  Phone no    |
|----------------|-------------------|--------------|
|legacy | target |legacy    | target |legacy|target |
|-------|--------|----------|--------|------|-------|
|Name1  |Name1   |n1@abc.com|        |      |       |
|Name2  |Name2   |          |        |      |   12  |
|---------------------------------------------------|

I am using the below code, but it is removing "email target" and "phone no legacy" column as well.

df.dropna(how='all', axis=1, inplace=True)

However I want to drop only the "Gender" column as this is the only column where both legacy and target fields are completely blank.

Could anyone please help me.

Thank you.

drop pandas multiple-columns dataframe python
2 Answers

try (I'm supposing empty cells are NaN):

m=df.isna().all().unstack(level=1)
cols=m[m.all(1)].index.tolist()

Finally use get_level_values():

df=df.loc[:, ~df.columns.get_level_values(0).isin(cols)]

output of df:

         Name             Email             Phone no
    Legecy  Target  Legecy      Target  Legecy  Target
0   Name1   Name1   n1@abc.com  NaN     NaN     NaN
1   Name1   Name2   NaN         NaN     NaN     12
user_142898920

Try (I'm supposing empty cells are strings ""):

m = (~df.eq("").all().groupby(level=0).all()).eq(True)
x = df.loc[:, m.index[m]]
print(x)

Prints:

    Name        Phone no          email       
  legacy target   legacy target  legacy target
0  Name1  Name1                  n1@abc       
1  Name2  Name2              12
user_100359850
Related