How To Shift A Dataframe Element-wise To Fill Nans?

I have a DataFrame like this: >>> df = pd.DataFrame({'a': list('ABCD'), 'b': ['E',np.nan,np.nan,'F']}) a b 0 A E 1 B NaN 2 C NaN 3 D F I am trying to fi

Solution 1:

OK misunderstood what you wanted to do the first time. The dummy example was a bit ambiguous.

Here is another:

>>>df = pd.DataFrame({'a': list('ABCD'), 'b': ['E',np.nan,np.nan,'F']})
   a    b
0  A    E
1  B  NaN
2  C  NaN
3  D    F

To my knowledge, this operation does not exist with pandas, so we will use numpy to do the work.

First transform the dataframe to numpy array and flatten it to be one-dimensional. Then drop NaNs using pandas.isna that is working on a larger range types than numpy.isnan, and then reshape the array to its original shape before transforming back to dataframe:

array = df.to_numpy().flatten()
pd.DataFrame(array[~pd.isna(array)].reshape(-1,df.shape[1]), columns=df.columns)

output:

ab0A  E
1B  C
2  D  F

It is also working for more complex examples, as long as the NaN pattern is conserved among columns with NaNs:

In:
   a    b   c    d
0  A    H  A2   H2
1  B  NaN  B2  NaN2  C  NaN  C2  NaN3  D    I  D2   I2
4  E  NaN  E2  NaN5FNaN  F2  NaN6  G    J  G2   J2

Out:
   a   b   c   d
0  A   H  A2  H2
1  B  B2   C  C2
2  D   I  D2  I2
3  E  E2   F  F2
4  G   J  G2  J2

In:
   a    b    c0  A    F    H
1  B  NaNNaN2  C  NaNNaN3  D  NaNNaN4  E    G    I

Out:
   a  b  c0  A  F  H
1  B  C  D
2  E  G  I

In case NaNs columns do not have the same pattern such as:

   a    b   c    d
0  A    H  A2  NaN1  B  NaN  B2  NaN2  C  NaN  C2   H2
3  D    I  D2   I2
4  E  NaN  E2  NaN5FNaN  F2  NaN6  G    J  G2   J2

You can apply the operation per group of two columns:

def elementwise_shift(df):
    array = df.to_numpy().flatten()
    return pd.DataFrame(array[~pd.isna(array)].reshape(-1,df.shape[1]), columns=df.columns)

(df.groupby(np.repeat(np.arange(df.shape[1]/2), 2), axis=1)
   .apply(elementwise_shift)
)

output:

ab   c   d
0A  H  A2  B2
1B  C  C2  H22  D  I  D2  I2
3  E  F  E2  F2
4  G  J  G2  J2

Solution 2:

You can do this in two steps with a placeholder column. First you fill all the nans in column b with the a values from the next row. Then you apply the filtering. In this example I use ffill with a limit of 1 to filter all nan values after the first, there's probably a better method.

import pandas as pd
import numpy as np
df=pd.DataFrame({"a":[1,2,3,3,4],"b":[1,2,np.nan,np.nan,4]})

# Fill all nans:
df['new_b'] = df['b'].fillna(df['a'].shift(-1))
df = df[df['b'].ffill(limit=1).notna()].copy() # .copy() because loc makes a view
df = df.drop('b', axis=1).rename(columns={'new_b': 'b'})

print(df)
# output:#    a  b# 0  1  1# 1  2  2# 2  3  2# 4  4  4

Solution 3:

Did you try df[columnname] = df[columnname].ffill()