I have a dataframe from which I want to drop NA values from, but there is a specific combination of two columns in which I expect NAs, so I want to drop all the NAs except those that meet that criteria, while retaining them.
E.g for the table:
| Index | Col 1 | Col 2 | Col 3 | Col 4 |
|---|---|---|---|---|
| 1 | 1 | A | log | petal |
| 2 | 2 | B | tree | leaf |
| 3 | 1 | A | NA | petal |
| 4 | 2 | B | tree | NA |
| 5 | 3 | A | NA | leaf |
| 6 | 2 | B | log | petal |
| 7 | 3 | B | NA | leaf |
| 8 | 3 | A | log | NA |
I want to drop all rows with NA based on that aren't [Col3 == 3] and [Col2 == A] and this exemption only applies to NA in [Col3].
Such that if [Col1 == 3], [Col2 ==A], [Col3==NA], [Col4 != NA] it is not dropped, but kept. But even if it is [Col1 == 3], [Col2 ==A], but [Col4 == NA] the row is dropped regardless of what value [Col3] is.
In the above case, only row 5 is exempt from being dropped due to NA, as it meets the criteria [Col1 == 3], [Col2 ==A], [Col3==NA] and [Col4 != NA].
Although row 8 is [Col1 == 3], [Col2 ==A], [Col3==log], because [Col4==NA] the row has to be dropped.
The final dataframe should look like this (retaining original indexes to illustrate dropped rows):
| Index | Col 1 | Col 2 | Col 3 | Col 4 |
|---|---|---|---|---|
| 1 | 1 | A | log | petal |
| 2 | 2 | B | tree | leaf |
| 5 | 3 | A | NA | leaf |
| 6 | 2 | B | log | petal |
The dropped rows all had NA in them, apart from row 5, which was expected to have NA at [Col3] based on its conditions from [Col1] and [Col2], and still had a value in [Col4].
I hope this makes sense.
So what is the best way to do it, as dataframe.dropna() will remove row 5, and assigning following dropping by filter, e.g dataframe = dataframe[(~dataframe[Col1]==3) & (~dataframe[Col2]==A)].dropna() will also likewise filter out row 5 to the new assignment.
Thanks.