ENH: add percentage threshold to DataFrame.dropna

[erfannariman]

Is your feature request related to a problem?
When doing feature selection for models, or with handling missing data, in most cases you want to dynamically set a threshold to drop column which exceed this. A percentage is a more general metric to use to see how many data is missing data per row or per column. Right now we have the thresh argument, which accepts an int, but in most cases this is not desirable. For example when running the code in production, the shape of the data changes over time and thus a percentage threshold would make more sense to me. Or when you don't know the data well enough, what does "a number" mean in this case. Percentage is standarized.

Besides that, the thresh right now is not flexible to go over column or index axis. For data cleaning you might want to drop rows which exceed the threshold, but for feature selection, you want to treat each column separately.

Describe the solution you'd like
Have a percentage threshold for both column and index axis.

API breaking implications
Not that I can think of, but not 100% sure.

Describe alternatives you've considered
Writing own custom functions.

Additional context

Locally I created a version where the perc argument is created (see linked draft PR):

example 1:

>>> df = pd.DataFrame({'col': ["A", "A", "B", "B"],
...                    'A': [80, np.nan, np.nan, np.nan],
...                    'B': [80, np.nan, 76, 67]})
>>> df
  col     A     B
0   A  80.0  80.0
1   A   NaN   NaN
2   B   NaN  76.0
3   B   NaN  67.0

>>> df.dropna(perc=0.5)
  col     A     B
0   A  80.0  80.0
2   B   NaN  76.0
3   B   NaN  67.0

>>> df.dropna(perc=0.5, axis=1)
  col     B
0   A  80.0
1   A   NaN
2   B  76.0
3   B  67.0

---

example 2:

>>> df = pd.DataFrame(np.random.randint(1, 10, (10,4)), columns=list('ABCD'))
>>> df.loc[3, 'B':] = np.nan
>>> df.loc[8, :'B'] = np.nan
>>> df.loc[1:6, 'C'] = np.nan
>>> df
     A    B    C    D
0  7.0  2.0  3.0  7.0
1  3.0  2.0  NaN  9.0
2  2.0  2.0  NaN  2.0
3  7.0  NaN  NaN  NaN 
4  1.0  9.0  NaN  4.0
5  9.0  9.0  NaN  1.0
6  2.0  2.0  NaN  6.0
7  9.0  3.0  5.0  6.0
8  NaN  NaN  9.0  5.0
9  7.0  7.0  3.0  1.0

As we can see, index 3 (axis=0) has 75% missing values and column C (axis=1) has 60% missing values. With the percentage argument we can specify what the threshold is, but also consider row wise or column wise:

Example consider per row:

>>> df.dropna(perc=.4, axis=0)
     A    B    C    D
0  7.0  2.0  3.0  7.0
1  3.0  2.0  NaN  9.0
2  2.0  2.0  NaN  2.0
4  1.0  9.0  NaN  4.0
5  9.0  9.0  NaN  1.0
6  2.0  2.0  NaN  6.0
7  9.0  3.0  5.0  6.0
9  7.0  7.0  3.0  1.0

Above we can see row 3 and 8 got dropped because these had > 40% missing values.

>>> df.dropna(perc=.4, axis=1)
     A    B    D
0  7.0  2.0  7.0
1  3.0  2.0  9.0
2  2.0  2.0  2.0
3  7.0  NaN  NaN
4  1.0  9.0  4.0
5  9.0  9.0  1.0
6  2.0  2.0  6.0
7  9.0  3.0  6.0
8  NaN  NaN  5.0
9  7.0  7.0  1.0

Same command but with axis=1, so we can consider percentage threshold per column, and we see that column C got dropped because it had 60% missing values

[mroeschke]

This generally would just be equivalent to df.dropna(thresh=round(len(df) * percentage)) correct?

[erfannariman]

This generally would just be equivalent to df.dropna(thresh=round(len(df) * percentage)) correct?

No not really, this method can be applied over row or column axis and thus takes each row / column separately into account. @mroeschke

[mroeschke]

I'd be +0 if we could roll this into thresh instead of adding a new parameter.

Let's wait for opinions from other members of the core team

[simonjayhawkins]

Thanks @erfannariman for the report. I would agree with @mroeschke without a more detailed proposal/discussion. Can you update the OP using the Feature Request template instead. (available when raising issue but also included in details below)

Is your feature request related to a problem?

[this should provide a description of what the problem is, e.g. "I wish I could use pandas to do [...]"]

Describe the solution you'd like

[this should provide a description of the feature request, e.g. "DataFrame.foo should get a new parameter bar that [...]", try to write a docstring for the desired feature]

API breaking implications

[this should provide a description of how this feature will affect the API]

Describe alternatives you've considered

[this should provide a description of any alternative solutions or features you've considered]

Additional context

[add any other context, code examples, or references to existing implementations about the feature request here]

# Your code here, if applicable

[simonjayhawkins]

I'd be +0 if we could roll this into thresh instead of adding a new parameter.

thres currently accepts an int. would allowing a float here make sense or would that lead to a more confusing api, with 1 having a different meaning to 1.0

[erfannariman]

Thanks @erfannariman for the report. I would agree with @mroeschke without a more detailed proposal/discussion. Can you update the OP using the Feature Request template instead.

Yes, updated OP @simonjayhawkins , this idea came up late at night and just started writing without a proper ticket request, sorry.

thres currently accepts an int. would allowing a float here make sense or would that lead to a more confusing api, with 1 having a different meaning to 1.0

IMO this wouldn't make much sense, first of all I think this would be confusing, second when setting a threshold I think it's important that rows or columns can be threated and also important that they can be treated separately.

[WillAyd]

This generally would just be equivalent to df.dropna(thresh=round(len(df) * percentage)) correct?

No not really, this method can be applied over row or column axis and thus takes each row / column separately into account. @mroeschke

Can you provide an example where this distinction matters?

[erfannariman]

@WillAyd sure, I added a more extensive example in OP, see example 2.

[simonjayhawkins]

@WillAyd sure, I added a more extensive example in OP, see example 2.

I still don't see how this example means that the thresh parameter cannot be used. thresh also uses the axis parameter, it's just an absolute discrete number instead of a ratio to the axis length.

so why would accepting a float between 0.0 and 1.0 (0.4 in the example) not produce the same output?

the length of axis 1 is 4. 0.6*4 = 2.4 (rounded to 3)
the length of axis 0 is 10. 0.6*10 = 6

produces the same output.

>>> df.dropna(thresh=3, axis=0)
     A    B    C    D
0  5.0  7.0  4.0  5.0
1  2.0  3.0  NaN  3.0
2  9.0  8.0  NaN  5.0
4  2.0  6.0  NaN  4.0
5  6.0  6.0  NaN  8.0
6  4.0  3.0  NaN  9.0
7  7.0  4.0  3.0  2.0
9  3.0  3.0  7.0  2.0
>>>
>>> df.dropna(thresh=6, axis=1)
     A    B    D
0  5.0  7.0  5.0
1  2.0  3.0  3.0
2  9.0  8.0  5.0
3  9.0  NaN  NaN
4  2.0  6.0  4.0
5  6.0  6.0  8.0
6  4.0  3.0  9.0
7  7.0  4.0  2.0
8  NaN  NaN  2.0
9  3.0  3.0  2.0
>>>

second when setting a threshold I think it's important that rows or columns can be threated and also important that they can be treated separately.

so I still don't understand the distinction, with the added example.