Shifting Values To The Top In A Python Dataframe If Nan Values Are Encountered

I am working with an irregular df. I am trying to get rid of the initial NaNs and shift all values to the top leaving NaNs at the bottom. I want to perform a realignment of the val

Solution 1:

You iterate over the cols and using first_valid_index and get_locshift the col values:

In [314]:for col in df:
    df[col]= df[col].shift(-df.index.get_loc(df[col].first_valid_index()))
df

Out[314]:
            col1  col2  col3  col4  col5  col6  col7  col8
STRIP                                                     
01/12/20110.80.80.780.70.60.60.10.701/01/20120.80.80.750.70.60.60.20.901/02/20120.80.80.730.70.60.60.30.601/03/20120.80.70.720.70.60.60.40.401/04/20120.70.70.700.70.60.60.50.301/05/20120.70.70.690.70.60.60.80.201/06/20120.70.70.680.70.60.60.7NaN01/07/20120.70.70.67NaN0.50.6NaNNaN01/08/20120.70.7NaNNaN0.5NaNNaNNaN01/09/20120.7NaNNaNNaNNaNNaNNaNNaN02/01/2013NaNNaNNaNNaNNaNNaNNaNNaN03/01/2013NaNNaNNaNNaNNaNNaNNaNNaN

Another method using apply:

In [317]:
df.apply(lambda x: x.shift(-x.index.get_loc(x.first_valid_index())))

Out[317]:
            col1  col2  col3  col4  col5  col6  col7  col8
STRIP                                                     
01/12/20110.80.80.780.70.60.60.10.701/01/20120.80.80.750.70.60.60.20.901/02/20120.80.80.730.70.60.60.30.601/03/20120.80.70.720.70.60.60.40.401/04/20120.70.70.700.70.60.60.50.301/05/20120.70.70.690.70.60.60.80.201/06/20120.70.70.680.70.60.60.7NaN01/07/20120.70.70.67NaN0.50.6NaNNaN01/08/20120.70.7NaNNaN0.5NaNNaNNaN01/09/20120.7NaNNaNNaNNaNNaNNaNNaN02/01/2013NaNNaNNaNNaNNaNNaNNaNNaN03/01/2013NaNNaNNaNNaNNaNNaNNaNNaN

EDIT

If 'STRIP' is a column then you don't need get_loc:

In [319]:
df.apply(lambda x: x.shift(-x.first_valid_index()))

Out[319]:
         STRIP  col1  col2  col3  col4  col5  col6  col7  col8
001/12/20110.80.80.780.70.60.60.10.7101/01/20120.80.80.750.70.60.60.20.9201/02/20120.80.80.730.70.60.60.30.6301/03/20120.80.70.720.70.60.60.40.4401/04/20120.70.70.700.70.60.60.50.3501/05/20120.70.70.690.70.60.60.80.2601/06/20120.70.70.680.70.60.60.7NaN701/07/20120.70.70.67NaN0.50.6NaNNaN801/08/20120.70.7NaNNaN0.5NaNNaNNaN901/09/20120.7NaNNaNNaNNaNNaNNaNNaN1002/01/2013NaNNaNNaNNaNNaNNaNNaNNaN1103/01/2013NaNNaNNaNNaNNaNNaNNaNNaN

Solution 2:

I think you can just stack the valid numbers and nan's back together:

In [95]:

df2 = df.apply(lambda x: np.hstack((x[~x.isnull()], x[x.isnull()])), axis=0)
print df2

         STRIP  col1  col2  col3  col4  col5  col6  col7  col8
001/12/20110.80.80.780.70.60.60.10.7101/01/20120.80.80.750.70.60.60.20.9201/02/20120.80.80.730.70.60.60.30.6301/03/20120.80.70.720.70.60.60.40.4401/04/20120.70.70.700.70.60.60.50.3501/05/20120.70.70.690.70.60.60.80.2601/06/20120.70.70.680.70.60.60.7NaN701/07/20120.70.70.67NaN0.50.6NaNNaN801/08/20120.70.7NaNNaN0.5NaNNaNNaN901/09/20120.7NaNNaNNaNNaNNaNNaNNaN1002/01/2013NaNNaNNaNNaNNaNNaNNaNNaN1103/01/2013NaNNaNNaNNaNNaNNaNNaNNaN