Pandas Pct Change Between 2 Columns, Replacing Original-CodePudding

(I think) I'm looking to apply a transform to a column, by finding the % change when compared to one static column.

My first attempt looks like this (without a transform):

from pandas import DataFrame
from numpy import random

df = DataFrame(random.randint(0,100,size=(100, 4)), columns=list('ABCD'))
print(df)

for col in df.columns:
    # Find the % increase/decrease of "col" compared to column A
    df[col] = df[["A", col]].pct_change(axis=1)[col]
print(df)

...however the resulting df is all NaN's when I'm expecting it to be in % increase/decrease format.

So as an example, it starts by comparing column A with column A, that's fine, all values SHOULD be the same. Then the next iteration it should be column B compared to column A. We should see %'s in column B in the end. Then same for C and D. I'm just new to transforms/changing values of a column in place and not sure how to do it.

CodePudding user response：

Subtract column A from the dataframe then divide by column A to calculate pct_change:

df.sub(df['A'], axis=0).div(df['A'], axis=0)

The above expression can be further simplied to:

df.div(df['A'], axis=0).sub(1)

      A          B          C          D
0   0.0  -0.821429   1.535714   0.500000
1   0.0   0.491525   0.508475  -0.745763
2   0.0  -0.452055   0.013699  -0.452055
3   0.0   2.187500   0.062500   0.812500
4   0.0  -0.632184  -0.839080   0.114943
5   0.0  -0.042105  -0.378947  -0.157895
6   0.0  -0.553191  -0.734043  -0.319149
...
98  0.0  -0.604651  -0.325581  -0.418605
99  0.0   0.649123  -0.964912  -0.631579

CodePudding user response：

I think the problem is that you can not have two columns 'A' in df[["A", col]]. When you change df.columns to df.columns[1:] it runs without errors.

for col in df.columns[1:]:
    # Find the % increase/decrease of "col" compared to column A
    df[col] = df[["A", col]].pct_change(axis=1)[col]
print(df)

Result:

     A         B         C         D
0   33 -0.974288  1.757576  0.575758
1   74 -1.010044 -0.945946 -0.797297
2   62 -1.015869  0.064516 -0.145161
3   53 -0.998932  0.377358  0.075472
4   97 -1.010203 -0.948454 -0.340206
..  ..       ...       ...       ...
95  88 -0.998838 -0.102273 -0.704545
96  59 -1.009193 -0.983051 -0.525424
97  52 -1.011464  0.134615 -0.903846
98   0       inf       inf       inf
99  33 -0.979798 -0.181818 -0.303030