Subtract a subset of columns from a key column in Pandas Pivot

user_7623705 0
Viewed 496

I have a pivot table with multiple columns of data in a time series:

             A    B    C    D
11/1/2018    1    5    5    7
11/2/2018    2    6    6    8
11/3/2018    3    7    7    9

The values in the data columns are not important for this example. I would like to subtract the value in the "key" column (column A in this case) from a subset of columns: B & C in this case. I would then like to drop any columns not in the subset or the key column. Result would be:

             A    B    C
11/1/2018    1    4    4
11/2/2018    2    4    4
11/3/2018    3    4    4

I have subtracted columns in the past via code like this:

df['dif'] = df['B'] -df['A']

But this will add the "dif" column. I would like to replace column B with B-A values. Also, instead of passing the instructions one at a time (B-A, C-A), would like to pass the list something like "if column in list, subtract key column, else drop column."

Thanks

pandas pivot python-3.x
2 Answers

You can use apply to substract A from the subset columns that you choose and finally join again with A.

df['A'].to_frame().join(df[['B','C']].apply(lambda x: x - df['A']))

            A   B   C
11/1/2018   1   4   4
11/2/2018   2   4   4
11/3/2018   3   4   4
user_37405450

pandas.DataFrame.sub with axis=0

When subtracting a Series from a DataFrame Pandas will align the columns of the DataFrame with the index of the Series by default. This is what happens when you use the - operator. However, when you use the pandas.DataFrame.sub method, you can override that default and specify that the DataFrame should align its index with the index of the Series.

def f(d, key, subset):
  return d[[key]].join(d[subset].sub(d[key], axis=0))

f(df, 'A', ['B', 'C'])

           A  B  C
11/1/2018  1  4  4
11/2/2018  2  4  4
11/3/2018  3  4  4
user_23366540
Related