How to drop columns which have same values in all rows via pandas or spark dataframe?
<p>Suppose I've data similar to following:</p>

<pre><code>  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
</code></pre>

<p>How can we drop all those columns like (<code>value</code>, <code>value2</code>, <code>value3</code>) where all rows have same values, in one command or couple of commands using <strong>python</strong> ? </p>

<p>Consider we have many columns similar to <code>value</code>,<code>value2</code>,<code>value3</code>...<code>value200</code>.</p>

<p>Output:</p>

<pre><code>   index id      name   data1
        0  345  name1    3
        1   12  name2    2
        5    2  name6    7
</code></pre>

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

<pre><code>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
</code></pre>

<p>Another way is to just <code>diff</code> the numeric columns and <code>sums</code> them:</p>

<pre><code>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
</code></pre>

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

<pre><code>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
</code></pre>

<p>Actually the above can be done in a one-liner:</p>

<pre><code>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
</code></pre>

python pandas duplicates multiple-columns spark-dataframe