I have accounting DataFrame and want to check some facts but the main problem is that they are not "playing" on the same league.
The DF is something like this:
A B C D E
x 1 1.5 1.1 0.8 1
y 100 120 90 115 102
z 1000 1100 1800 900 1000
I want to check their path between variables ABCDE , so result should be something like this:
A B C D E
x(%) 0 50 10 -20 0
y(%) 0 20 -10 15 2
z(%) 0 10 80 -10 0
Or
A B C D E
x(%) 100 150 110 80 0
y(%) 100 120 90 115 102
z(%) 100 110 180 90 100
The idea I have is :
df2 = df
df2.iloc[0]= ( (df2.iloc[0] * 100 ) / df2["A"].iloc[0] ) -100
df2.iloc[1]= ( (df2.iloc[1] * 100 ) / df2["A"].iloc[1] ) -100
df2.iloc[2]= ( (df2.iloc[2] * 100 ) / df2["A"].iloc[2] ) -100
# (-100 is for first example)
But, do you know a "non manual" way for this?
Data:
{'A': {'x': 1, 'y': 100, 'z': 1000},
'B': {'x': 1.5, 'y': 120, 'z': 1100},
'C': {'x': 1.1, 'y': 90, 'z': 1800},
'D': {'x': 0.8, 'y': 115, 'z': 900},
'E': {'x': 1, 'y': 102, 'z': 1000}}
>Solution :
You can convert "A" to a numpy column vector and use broadcasting:
df = (df / df["A"].to_numpy()[:, None] - 1) * 100
Output:
A B C D E
x 0.0 50.0 10.0 -20.0 0.0
y 0.0 20.0 -10.0 15.0 2.0
z 0.0 10.0 80.0 -10.0 0.0