Simple Equivalent of excel drag in pandas

Question

I have been using pandas for a little while. In Excel, we frequently use drag functionality as in the picture.

The equivalent of that which I am using in pandas is something like:

def apply_drag(row, initial_value, initial_index, rate):
     count = (row.name - initial_index).days
     return initial_value * math.pow(1 + rate, count)

custom_nav_df["nav"] = custom_nav_df.apply(apply_drag, args=(initial_value, initial_index, risk_free_rate), axis=1)

While this serves my purpose, I presume there should be a simpler way to do such a trivial task. Also, for this case, if the date is not continuous, it will give wrong results.

What should be the simplest way to achieve this where data for the current row is achieved from previous row (let's say arithmetic or geometric progression).

It this question really about dragging? I mean, it does not seem to have anything related to a GUI or mouse interaction in it? — Niko Föhr, Nov 09 '20 at 16:43
Do you think [Is there a way in Pandas to use previous row value in dataframe.apply when previous value is also calculated in the apply?](https://stackoverflow.com/questions/34855859/is-there-a-way-in-pandas-to-use-previous-row-value-in-dataframe-apply-when-previ) could help you? — Niko Föhr, Nov 09 '20 at 16:46

alec_djinn · Answer 1 · 2020-11-11T09:12:28.367

I think you can simplify your function.

def f(rate, start, n):
    yield start
    for _ in range(n-1):
        start *= rate
        yield start

Now you can simply assign its result to a column. Here is a quick example:

In [20]: df = pd.DataFrame({'colA': list(range(5)) })

In [21]: df
Out[21]:
   colA
0     0
1     1
2     2
3     3
4     4

In [22]: df['nav'] = list(f(1.05, 100, len(df)))

In [23]: df
Out[23]:
   colA         nav
0     0  100.000000
1     1  105.000000
2     2  110.250000
3     3  115.762500
4     4  121.550625

Alternatively, you can simply iterate over each row using the index:

In [10]: df
Out[10]:
   A      B
0  0  100.0
1  1    NaN
2  2    NaN
3  3    NaN
4  4    NaN

In [11]: for i in df.index:
    ...:     if i:
    ...:         df.iat[i,1] = df.iat[i-1,1]*1.05
    ...:
    ...:

In [12]: df
Out[12]:
   A           B
0  0  100.000000
1  1  105.000000
2  2  110.250000
3  3  115.762500
4  4  121.550625

You can start from an empty column, of course:

In [14]: df['B'] = np.nan

In [15]: for i in df.index:
    ...:     if i:
    ...:         df.iat[i,1] = df.iat[i-1,1]*1.05
    ...:     else:
    ...:         df.iat[i,1] = 100
    ...:

In [16]: df
Out[16]:
   A           B
0  0  100.000000
1  1  105.000000
2  2  110.250000
3  3  115.762500
4  4  121.550625

This is actually good much simpler approach. But I was thinking if a simpler approach is available without using an external function, where I use some inbuilt pandas functionality like shift etc. — sprksh, Nov 11 '20 at 03:49
@sprksh I have added an alternative option. I am not aware of a built-it pandas function to do the same. — alec_djinn, Nov 11 '20 at 09:07

Simple Equivalent of excel drag in pandas

1 Answers1