pandas shift inside apply function

Question

I am trying to fill some of the NaN values with values from another column and the row above.

For example, I get a dataframe that looks like this:

    Distance  Down  firstDownYards  secondDownYards
1       10.0   1.0             NaN              NaN
2        8.0   2.0             2.0              NaN
3        8.0   3.0             2.0              0.0
4       19.0   3.0            -9.0            -11.0
5       19.0   4.0            -9.0            -11.0
6       10.0   1.0             NaN              NaN
7        5.0   2.0             5.0              NaN
8        5.0   3.0             5.0              0.0
9       10.0   1.0             NaN              NaN
10       9.0   2.0             1.0              NaN
11      11.0   3.0            -1.0             -2.0
12      12.0   4.0            -2.0             -3.0
13      10.0   1.0             NaN              NaN
14       5.0   2.0             5.0              NaN
15      10.0   1.0             NaN              NaN
16       8.0   2.0             2.0              NaN
17       8.0   3.0             2.0              0.0
18      10.0   1.0             NaN              NaN
19      10.0   2.0             0.0              NaN
20       6.0   3.0             4.0              4.0

In the secondDownYards, I would like to fill the NaN where down is lower than 2 with the opposite of the next row of column firstDownYards. Here is an example of what the column would look like:

    Distance  Down  firstDownYards  secondDownYards
1       10.0   1.0             NaN              -2    # Change here
2        8.0   2.0             2.0              NaN
3        8.0   3.0             2.0              0.0
4       19.0   3.0            -9.0            -11.0
5       19.0   4.0            -9.0            -11.0
6       10.0   1.0             NaN              -5    # Change here
7        5.0   2.0             5.0              NaN
8        5.0   3.0             5.0              0.0
9       10.0   1.0             NaN              -1    # Change here
10       9.0   2.0             1.0              NaN
11      11.0   3.0            -1.0             -2.0
12      12.0   4.0            -2.0             -3.0
13      10.0   1.0             NaN              -5    # Change here
14       5.0   2.0             5.0              NaN
15      10.0   1.0             NaN              -2    # Change here
16       8.0   2.0             2.0              NaN
17       8.0   3.0             2.0              0.0
18      10.0   1.0             NaN              0    # Change here
19      10.0   2.0             0.0              NaN
20       6.0   3.0             4.0              4.0

I have tried creating a function that looks like this, but when I try and print x.shift(), it simply prints the same thing as x. I would then use df.apply(getLastCol,args=(....),axis=1). downNb is the condition, in this example being 2. currentCol and lastCol are the names of the current column and the previous column.

def getLastCol(x,downNb,currentCol,lastCol):
    if x['Down'] < downNb:
        print(x.shift())
        value = x.shift(-1)[lastCol]
    else:
        value = x[currentCol]
    return value

piRSquared · Accepted Answer · 2018-08-17 20:51:48Z

Using `shift` and `loc` in-place

df.loc[df.Down.lt(2), 'secondDownYards'] = df.firstDownYards.shift(-1).mul(-1)

You also mentioned the condition that secondDownYards must be NaN as well. In your example, that is always the case, if it is not guaranteed and you only want the replace the NaN values, you may also add that check:

df.loc[df.Down.lt(2) & df.secondDownYards.isnull(), 'secondDownYards'] # ...

Using `np.where` and `assign`

This option has the benefit of not modifying the DataFrame in-place:

df.assign(
    secondDownYards= np.where(
    df.Down.lt(2), df.firstDownYards.shift(-1).mul(-1), df.secondDownYards
))

Both of these options result in your desired output:

    Distance  Down  firstDownYards  secondDownYards
1       10.0   1.0             NaN             -2.0
2        8.0   2.0             2.0              NaN
3        8.0   3.0             2.0              0.0
4       19.0   3.0            -9.0            -11.0
5       19.0   4.0            -9.0            -11.0
6       10.0   1.0             NaN             -5.0
7        5.0   2.0             5.0              NaN
8        5.0   3.0             5.0              0.0
9       10.0   1.0             NaN             -1.0
10       9.0   2.0             1.0              NaN
11      11.0   3.0            -1.0             -2.0
12      12.0   4.0            -2.0             -3.0
13      10.0   1.0             NaN             -5.0
14       5.0   2.0             5.0              NaN
15      10.0   1.0             NaN             -2.0
16       8.0   2.0             2.0              NaN
17       8.0   3.0             2.0              0.0
18      10.0   1.0             NaN             -0.0
19      10.0   2.0             0.0              NaN
20       6.0   3.0             4.0              4.0

Collectives™ on Stack Overflow

pandas shift inside apply function

1 Answer 1

Using `shift` and `loc` in-place

Using `np.where` and `assign`

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Using shift and loc in-place

Using np.where and assign

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Using `shift` and `loc` in-place

Using `np.where` and `assign`