Home / Questions / 547591
Accepted
NoahVerner
Asked: 2022-07-21 18:44:52 +0800 CST 2022-07-21 18:44:52 +0800 CST

How to correctly estimate the percentage change of two columns considering different indices in the series with pandas?

  • 772

Suppose I have the following dataframe called df_trading_pair:

    Start Date           Open Price     High Price  Low Price   Close Price End Date
0   2022-07-20 08:00:00  0.19277        0.19324     0.19225     0.19324     2022-07-20 08:04:59.999
1   2022-07-20 08:05:00  0.19321        0.194       0.1932      0.19388     2022-07-20 08:09:59.999
2   2022-07-20 08:10:00  0.19387        0.195       0.19387     0.19489     2022-07-20 08:14:59.999
3   2022-07-20 08:15:00  0.19496        0.19628     0.19495     0.19626     2022-07-20 08:19:59.999
4   2022-07-20 08:20:00  0.19625        0.20406     0.19625     0.2035      2022-07-20 08:24:59.999

I have been trying to figure out a simple way to calculate the percentage change of the first 4 elements in the column Open Pricevs the last 4 elements in the column Close Price, so that I get the following output in another dataframe called new_df:

 Close Price vs Open Price % change
0                            0.0057
1                            0.0087
2                            0.0123
3                            0.0438
dtype: float64

Initially, I thought the following line of code would work just fine, since both arrays would have 4 elements and contain the exact values ​​I need:

new_df["Close Price vs Open Price % change"] = (df_trading_pair["Close Price"][1:]-df_trading_pair["Open Price"][:-1])/df_trading_pair["Open Price"][:-1]

However, after executing that line the console gave me the following output:

 Close Price vs Open Price % change
0                               NaN
1                          0.003468
2                          0.005261
3                          0.006668
4                               NaN
dtype: float64

Which I don't understand, I also decided to try this other line of code:

new_df["Close Price vs Open Price % Change"] = [(y-x)/x*100 for x in df_trading_pair["Open Price"][:-1] for y in df_trading_pair["Close Price"][1:]]

Which also led me to believe it would work, but this one unfortunately returned the following error:

ValueError: Length of values (16) does not match length of index (5)

So, I would like to know what have I done wrong, or what other solution could work better to get the output I want?

python-3.x

1 Answers

  1. Best Answer

    Good day,

    The reason it doesn't work is because what you do in this line:

    new_df["Close Price vs Open Price % change"] = (df_trading_pair["Close Price"][1:]-df_trading_pair["Open Price"][:-1])/df_trading_pair["Open Price"][:-1]

    It is only filtering dataframeby which the values ​​are still linked to the indices and when subtracting them (Or doing any other operation) it will be done by mapping index by index (0 with 0, 1 with 1, etc...).

    The simple solution is to just add .valuesto each filter, to work with the values ​​and not the filter, as follows:

    new_df = pd.DataFrame()
new_df["Close Price vs Open Price % change"] = (df_trading_pair["Close Price"][1:].values - df_trading_pair["Open Price"][:-1].values)/df_trading_pair["Open Price"][:-1].values

    This returns the following dataframe:

        Close Price vs Open Price % change
0   0.005758
1   0.008695
2   0.012328
3   0.043804

    But I think that even so there is another way that would be more convenient for you if you use dataframesmore extensive ones, this is usingpandas.DataFrame.iloc

    new_df = pd.DataFrame()
new_df["Close Price vs Open Price % change"] = (df_trading_pair.iloc[-4:]['Close Price'].values - df_trading_pair.iloc[:4]['Open Price'].values) / df_trading_pair.iloc[:-1]['Open Price'].values

    We use the slices [-4:]for the last 4 items and [:4]for the first 4 items, and add their respective columns to them as shown in the example above.

    • 2