I have a Dataframe where, for each row, I would like to subtract the value in the first column from the value in every 'even' column after that, and the value in the second column from every 'odd' column. There will be a lot of columns.
I have the following which subtracts the first column from every other column, but I don't know how to limit it to 'even' or 'odd' columns.
In the example below, I'd like to subtract the value in the 'w' column from columns 'a' & 'c' (e, g, etc), and subtract the value in 'z' from 'b' & 'd' (f, h etc).
# import pandas and numpy library
import pandas as pd
# function to returns x-y
def addData(x, y):
return x - y
# list of tuples
matrix = [(1, 2, 3, 4),
(5, 6, 7, 8, ),
(9, 10, 11, 12),
(13, 14, 15, 16)
]
matrix2 = [(1, 2,),
(5, 6,),
(9, 10,),
(13, 14)
]
# Create a Dataframe object
df = pd.DataFrame(matrix, columns=list('abcd'))
df2 = pd.DataFrame(matrix2, columns=list('wz'))
# Applying function to each column
new_df = df.apply(addData, args=[df2['w']])
# Output
print(new_df)
Desired output:
0 0 2 2
0 0 2 2
0 0 2 2
0 0 2 2
If you only have odd/even, it's quite easy to do it manually, just slice the odd/even, and eventually concat them back afterwards:
out = pd.concat([df.iloc[:, ::2].sub(df2.iloc[:, 0], axis=0),
df.iloc[:, 1::2].sub(df2.iloc[:, 1], axis=0)
], axis=1)[df.columns]
Another option is to play with the indices and to reindex:
cols = df2.columns[np.arange(df.shape[1])%2]
# Index(['w', 'z', 'w', 'z'], dtype='object')
out = (df.set_axis(cols, axis=1) # temporarily change columns
.sub(df2[cols]) # subtract
.set_axis(df.columns, axis=1) # restore names
)
Output:
a b c d
0 0 0 2 2
1 0 0 2 2
2 0 0 2 2
3 0 0 2 2