so i have data frame as below

i want to shift the whole data frame n rows such that it skips nan rows but still preserve it. desire output: for n =2

i tried the following:

df['dummy'] = df.apply(lambda x: 1 if pd.notnull(x[column]) else 0, axis=1)
df['dummy2'] = df.groupby(['dummy'])[column].shift(n)
df[column] = df.apply(lambda x: x['dummy2'] if x['dummy']==1 else x[column], axis=1)

which is good if there is only a few columns i need to shift.

i also tried the applymap function

dummy_df = df.applymap(lambda x: 1 if pd.notnull(x) else 0)

which returns a dummy data frame to separate groups that i want to shift, just have no idea what to do next with it. the problem is that there are thousands of columns i need to shift row wise.

Is there any ways i can do this using minimum loops? And are there any ways to do it with groupby function using dummy_df?

CodePudding user response：

Use lambda function with Series.dropna and Series.shift:

df = df.apply(lambda x: x.dropna().shift(2))

print (df)
     A1   A2    A3    A4   A5    A6
0   NaN  NaN   NaN   NaN  NaN   NaN
1   NaN  NaN   NaN   NaN  NaN   NaN
2   NaN  NaN   NaN   7.0  NaN   NaN
3   1.0  NaN   NaN  11.0  NaN   NaN
4  54.0  5.0   3.0  12.0  9.0   8.0
5  10.0  NaN  84.0   NaN  NaN  45.0

CodePudding user response：

try this:

tmp = df.apply(
    lambda s: s.sort_values(
        key=lambda v: pd.notnull(v)
        ).values
    )
res = tmp.shift(2)
res