Remove NaN values and shift values from the next column

user_12355920 0
Viewed 877

I'm trying to remove NaN values from a dataframe (without removing the entire column or row) and shift the next value to the prior column. Example:

        CLIENT| ANIMAL_1 | ANIMAL_2 | ANIMAL_3| ANIMAL_4
ROW_1     1   |   cow    | frog     | NaN     | dog
ROW_2     2   |   pig    | NaN      | cat     | NaN

My goal is to have:

       CLIENT| ANIMAL_1 | ANIMAL_2 | ANIMAL_3| ANIMAL_4
ROW_1     1   |   cow    | frog     | dog     | NaN
ROW_2     2   |   pig    | cat      | NaN     | NaN

What I have tried:

  1. Transforming each row into lists and deleting the NaN from each one. But I can't seem to delete the values from the list:

    x = df[df.CLIENT == 1].iloc[:,1:].values.tolist()

then I get:

[['cow', 'frog', nan, 'dog']]

to delete the 'nan' I tried:

row_without_nan = [animal for animal in x if str(animal) != 'nan']

But it doesn't change anything in the list. I tried changing the nulls to another word and using that word, but it didn't work either.

  1. Transforming each row into array. I tried transforming into array by using np.array(), but it is useless, because the nulls become 'nan' and when I try to use np.isnan I get this: TypeError: ufunc 'isnan' not supported for the input types, and the inputs could not be safely coerced to any supported types according to the casting rule ''safe''

Does anyone know what I'm doing wrong with my list or if there is a smarter/faster way to do it?

pandas dataframe nan list python
3 Answers

Here is one way:

df_out = df.apply(lambda x: pd.Series(x.dropna().to_numpy()), axis=1)
df_out = df_out.set_axis(df.columns[:df_out.shape[1]], axis=1).reindex(df.columns, axis=1)
df_out

Output:

       CLIENT ANIMAL_1 ANIMAL_2 ANIMAL_3  ANIMAL_4
ROW_1       1      cow     frog      dog       NaN
ROW_2       2      pig      cat      NaN       NaN

Details, use dropna on each row, but then you need to to convert to numpy array to remove indexes, then assign column headers to the original dataframe and reindex along columns to pick up all null columns at the end of the dataframe.

user_63615310

Here's another way of doing it:

def drop_nan(r):
    r = list(r)
    r = [x for x in r if isinstance(x, str) or not np.isnan(x)]
    return r

res = pd.DataFrame.from_records(df.apply(drop_nan, axis=1))
res.columns = df.columns[:len(res.columns)]

The result is:

   CLIENT ANIMAL_1 ANIMAL_2 ANIMAL_3
0       1      cow     frog      dog
1       2      pig      cat     None
user_11055600

Your approach might be okay, but the issue you might be having is that after you got your array or list from your dataframe, you got a nested list, ie. [['cow', 'frog', nan, 'dog']]. Notice the double brackets. You need to get rid of a bracket pair. Try something like y=x[0]. Then run your nan removal code again.

user_128943230
Related