CYAN CEVI CYAN CEVI - 2 months ago 10
Python Question

How to drop columns which have same values in all rows via pandas or spark dataframe?

Suppose I've data similar to following:

index id name value value2 value3 data1 val5
0 345 name1 1 99 23 3 66
1 12 name2 1 99 23 2 66
5 2 name6 1 99 23 7 66


how can we drop all those columns like (value, value2, value3) where all rows have same values, in one command or couple of commands using python ? Consider we have many columns similar to value,value2,value3...value200.

output:

index id name data1
0 345 name1 3
1 12 name2 2
5 2 name6 7


Thanks

Answer

What we can do is apply nunique to calc the number of unique values in the df and drop the columns which only have a single unique value:

In [285]:
cols = list(df)
nunique = df.apply(pd.Series.nunique)
cols_to_drop = nunique[nunique == 1].index
df.drop(cols_to_drop, axis=1)

Out[285]:
   index   id   name  data1
0      0  345  name1      3
1      1   12  name2      2
2      5    2  name6      7

Another way is to just diff the numeric columns and sums them:

In [298]:
cols = df.select_dtypes([np.number]).columns
diff = df[cols].diff().sum()
df.drop(diff[diff== 0].index, axis=1)
​
Out[298]:
   index   id   name  data1
0      0  345  name1      3
1      1   12  name2      2
2      5    2  name6      7

Another approach is to use the property that the standard deviation will be zero for a column with the same value:

In [300]:
cols = df.select_dtypes([np.number]).columns
std = df[cols].std()
cols_to_drop = std[std==0].index
df.drop(cols_to_drop, axis=1)

Out[300]:
   index   id   name  data1
0      0  345  name1      3
1      1   12  name2      2
2      5    2  name6      7

Actually the above can be done in a one-liner:

In [306]:
df.drop(df.std()[(df.std() == 0)].index, axis=1)

Out[306]:
   index   id   name  data1
0      0  345  name1      3
1      1   12  name2      2
2      5    2  name6      7
Comments