Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 the 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 
like image 330
CYAN CEVI Avatar asked Sep 23 '16 10:09

CYAN CEVI


People also ask

How do I drop a column with the same value?

To drop duplicate columns from pandas DataFrame use df. T. drop_duplicates(). T , this removes all columns that have the same data regardless of column names.

How do you drop rows in Pandas based on multiple column values?

Use drop() method to delete rows based on column value in pandas DataFrame, as part of the data cleansing, you would be required to drop rows from the DataFrame when a column value matches with a static value or on another column value.

How do I drop multiple columns in a DataFrame spark?

The Spark DataFrame provides the drop() method to drop the column or the field from the DataFrame or the Dataset. The drop() method is also used to remove the multiple columns from the Spark DataFrame or the Database.


1 Answers

What we can do is use nunique to calculate the number of unique values in each column of the dataframe, and drop the columns which only have a single unique value:

In [285]: nunique = df.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, take abs values and sums them:

In [298]: cols = df.select_dtypes([np.number]).columns diff = df[cols].diff().abs().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 
like image 181
EdChum Avatar answered Oct 01 '22 02:10

EdChum