Pandas newbie - Reading excel - trying to apply formula to columns

user_15466070 0
Viewed 73

I have an excel sheet with multiple columns, three of them are named X Y Z. I'm trying to apply the same formula to the three columns using panda dataframe. I need to subtract the last value in the column from every cell value in that column; save new value and keep going down the column. Is there a way to do this without iterating over every row? This process has to be performed on each of the columns X Y and Z only.

INPUT
T   X    Y   Z   vx   vy   vz
0   0     0   3   3     4     5
1   0     1   4   4     4     6
2   23    6   9   5     6     6
3   35    9   9   7     7     8

Output
T    X     Y    Z   vx   vy   vz
0   -35   -9  -6    3     4     5
1   -35   -8  -5    4     4     6
2   -12   -3   0    5     6     6
3     0    0    0   7     7     8
pandas excel python
2 Answers

Apparently not each of the columns, as you're only applying it to the X, Y and Z columns.

anyway,

selecting your input column, then subtracting the last entry of it, seems to be what you're looking for.

df = pd.read_clipboard()
df[["X","Y","Z"]] = df[["X", "Y", "Z"]] - df[["X", "Y", "Z"]].iloc[-1]

output:

#   T   X  Y  Z  vx  vy  vz
#0  0 -35 -9 -6   3   4   5
#1  1 -35 -8 -5   4   4   6
#2  2 -12 -3  0   5   6   6
#3  3   0  0  0   7   7   8
user_52120310

Assuming that you want to do all columns, this will work on a spreadsheet by the name of "file.xls":

import pandas as pd 
df = pd.read_excel('file.xls', header=None)

for i in df.columns:
    df[i] = df[i] - int(df[i].tail(1))

print (df)
user_32352600
Related