Remove NaN values from pandas dataframe and reshape table

Question

• Given a dataframe with columns interspersed with NaNs, how can the dataframe be transformed to remove all the NaN from the columns?

Sample DataFrames

import pandas as pd
import numpy as np

# dataframe from list of lists
list_of_lists = [[ 4., 7., 1., np.nan],
                 [np.nan, np.nan, 3., 3.],
                 [ 4., 9., np.nan, np.nan],
                 [np.nan, np.nan, 7., 9.],
                 [np.nan, 2., np.nan, 2.],
                 [4., np.nan, np.nan, np.nan]]

df_from_lists = pd.DataFrame(list_of_lists, columns=['A', 'B', 'C', 'D'])

# dataframe from list of dicts
list_of_dicts = [{'A': 4.0, 'B': 7.0, 'C': 1.0},
                 {'C': 3.0, 'D': 3.0},
                 {'A': 4.0, 'B': 9.0},
                 {'C': 7.0, 'D': 9.0},
                 {'B': 2.0, 'D': 2.0},
                 {'A': 4.0}]

df_from_dicts = pd.DataFrame(list_of_dicts)

Display of DataFrame

     A    B    C    D
0  4.0  7.0  1.0  NaN
1  NaN  NaN  3.0  3.0
2  4.0  9.0  NaN  NaN
3  NaN  NaN  7.0  9.0
4  NaN  2.0  NaN  2.0
5  4.0  NaN  NaN  NaN

Expected Output

     A    B    C    D
0  4.0  7.0  1.0  3.0
1  4.0  9.0  3.0  9.0
2  4.0  2.0  7.0  2.0

Answer 1

You need apply with dropna, only is necessary create numpy array and reassign Series for reset indices:

df.apply(lambda x: pd.Series(x.dropna().values))

Sample:

df = pd.DataFrame({'B':[4,np.nan,4,np.nan,np.nan,4],
                   'C':[7,np.nan,9,np.nan,2,np.nan],
                   'D':[1,3,np.nan,7,np.nan,np.nan],
                   'E':[np.nan,3,np.nan,9,2,np.nan]})

print (df)
     B    C    D    E
0  4.0  7.0  1.0  NaN
1  NaN  NaN  3.0  3.0
2  4.0  9.0  NaN  NaN
3  NaN  NaN  7.0  9.0
4  NaN  2.0  NaN  2.0
5  4.0  NaN  NaN  NaN

df1 = df.apply(lambda x: pd.Series(x.dropna().values))
print (df1)
     B    C    D    E
0  4.0  7.0  1.0  3.0
1  4.0  9.0  3.0  9.0
2  4.0  2.0  7.0  2.0

Answer 2

Approach #1 Here's one with array data -

a = df.values.T
df_out = pd.DataFrame(a[~np.isnan(a)].reshape(a.shape[0],-1).T)

Sample run -

In [450]: df
Out[450]: 
     0    1    2
0  1.0  NaN  NaN
1  9.0  7.0  8.0
2  NaN  NaN  NaN
3  NaN  5.0  7.0

In [451]: a = df.values.T

In [452]: pd.DataFrame(a[~np.isnan(a)].reshape(a.shape[0],-1).T)
Out[452]: 
     0    1    2
0  1.0  7.0  8.0
1  9.0  5.0  7.0

---

Approach #2 As it turns out, we already have an utility for it : justify -

In [1]: df
Out[1]: 
     0    1    2
0  1.0  NaN  NaN
1  9.0  7.0  8.0
2  NaN  NaN  NaN
3  NaN  5.0  7.0

In [2]: pd.DataFrame(justify(df.values, invalid_val=np.nan, axis=0, side='up')[:2])
Out[2]: 
     0    1    2
0  1.0  7.0  8.0
1  9.0  5.0  7.0

Benchmarking

Approaches -

def app0(df): # @jezrael's soln
    return df.apply(lambda x: pd.Series(x.dropna().values))

def app1(df): # Proposed in this post
    a = df.values.T
    return pd.DataFrame(a[~np.isnan(a)].reshape(a.shape[0],-1).T)

def app2(df): # Proposed in this post
    a = df.values
    return pd.DataFrame(justify(a, invalid_val=np.nan, axis=0, side='up')[:5])

def app3(df): # @piRSquared's soln-1
    v = df.values
    r = np.arange(v.shape[1])[None, :]
    a = np.isnan(v).argsort(0)
    return pd.DataFrame(v[a[:5], r], columns=df.columns)

def app4(df): # @piRSquared's soln-2
    return pd.DataFrame(
        (lambda a, s: a[~np.isnan(a)].reshape(-1, s, order='F'))
        (df.values.ravel('F'), df.shape[1]),
        columns=df.columns
    )

Timings -

In [513]: # Setup input dataframe with exactly 5 non-NaNs per col
     ...: m,n = 500,100
     ...: N = 5
     ...: a = np.full((m,n), np.nan)
     ...: row_idx = np.random.rand(m,n).argsort(0)[:N]
     ...: a[row_idx, np.arange(n)] = np.random.randint(0,9,(N,n))
     ...: df = pd.DataFrame(a)
     ...: 

In [572]: %timeit app0(df)
     ...: %timeit app1(df)
     ...: %timeit app2(df)
     ...: %timeit app3(df)
     ...: %timeit app4(df)
     ...: 
10 loops, best of 3: 46.1 ms per loop
10000 loops, best of 3: 132 µs per loop
1000 loops, best of 3: 554 µs per loop
1000 loops, best of 3: 446 µs per loop
10000 loops, best of 3: 148 µs per loop

Answer 3

Using @Divakar's sample dataframe

df

     0    1    2
0  1.0  NaN  NaN
1  9.0  7.0  8.0
2  NaN  NaN  NaN
3  NaN  5.0  7.0

---

v = df.values
r = np.arange(v.shape[1])[None, :]
a = np.isnan(v).argsort(0)

pd.DataFrame(v[a[:2], r], columns=df.columns)

     0    1    2
0  1.0  7.0  8.0
1  9.0  5.0  7.0

---

Inspired by @Divakar's answer

pd.DataFrame(
    (lambda a, s: a[~np.isnan(a)].reshape(-1, s, order='F'))(df.values.ravel('F'), df.shape[1]),
    columns=df.columns
)

     0    1    2
0  1.0  7.0  8.0
1  9.0  5.0  7.0