Reputation: 496

Subtracting subcolumn from a column of a dataframe

I have a dataframe as follows:

    name   tag      price
0   x1     tweak1   1.1 
1   x1     tweak2   1.2
2   x1     base     1.0
3   x2     tweak1   2.1
4   x2     tweak2   2.2
5   x2     base     2.0

I want to subtract the base price from price column and create a new column as follows:

    name   tag      price  sensitivity
0   x1     tweak1   1.1    0.1
1   x1     tweak2   1.2    0.2
2   x1     base     1.0    0.0
3   x2     tweak1   1.3    -0.7
4   x2     tweak2   2.4    0.4
5   x2     base     2.0    0.0

and eventually drop the rows with tag base to get

    name   tag      price  sensitivity
0   x1     tweak1   1.1    0.1
1   x1     tweak2   1.2    0.2
3   x2     tweak1   1.3    -0.7
4   x2     tweak2   2.4    0.4

What is the best way to perform this operation in pandas?

Upvotes: 3

Answers (3)

piRSquared

Reputation: 294506

I'd start by making your index from the 'name' and 'tag' columns.
Then I'd subtract the 'base' cross section. Pandas will align for us.
Finally, use assign + drop + reset_index for bookkeeping and formatting.

p = df.set_index(['name', 'tag'])[['price']]

p.assign(sensitivity=p - p.xs('base', level=1)).drop('base', level=1).reset_index()

  name     tag  price  sensitivity
0   x1  tweak1    1.1          0.1
1   x1  tweak2    1.2          0.2
2   x2  tweak1    1.3         -0.7
3   x2  tweak2    2.4          0.4

Upvotes: 2

MattR

Reputation: 5146

Here is how I would tackle it:

1) Create a column for base

2) Subtract those columns

3) Drop the base (no pun intended)

import pandas as pd
import numpy as np
# Creates a column 'Base' If 'Tag' is base and use the value from price
# if 'Tag' is not base, use 0
df['Base'] = np.where(df.tag.isin(['base']), df['Price'] ,0)
# takes the difference of the two columns
df['difference'] = df['Price'] - df['Base']
# Creates a new DF that uses all values except when 'Tag' is base
df3 = df[df['Tag'] !='Base'] 
print(df3)

Here is the Example I used to come up with my code. Feel free to follow if you wish:

import re
import pandas as pd
import numpy as np
df = pd.DataFrame({'A' : [1,1,3,4,5,5,3,1,5,np.NaN], 
                    'B' : [1,np.NaN,3,5,0,0,np.NaN,9,0,0], 
                    'C' : ['AA1233445','AA1233445', 'rmacy','Idaho Rx','Ab123455','TV192837','RX','Ohio Drugs','RX12345','USA Pharma'], 
                    'D' : [123456,123456,1234567,12345678,12345,12345,12345678,123456789,1234567,np.NaN],
                    'E' : ['Assign','Unassign','Assign','Ugly','Appreciate','Undo','Assign','Unicycle','Assign','Unicorn',]})
print(df)

df['Base'] = np.where(df.E.isin(['Assign']), df['A'] ,0)
df['difference'] = df['B'] - df['Base']
df3 = df[df['E'] !='Assign']

output:

   A    B           C            D           E  Base  difference
1  1.0  NaN   AA1233445     123456.0    Unassign   0.0         NaN
3  4.0  5.0    Idaho Rx   12345678.0        Ugly   0.0         5.0
4  5.0  0.0    Ab123455      12345.0  Appreciate   0.0         0.0
5  5.0  0.0    TV192837      12345.0        Undo   0.0         0.0
7  1.0  9.0  Ohio Drugs  123456789.0    Unicycle   0.0         9.0
9  NaN  0.0  USA Pharma          NaN     Unicorn   0.0         0.0

Upvotes: 1

akuiper

Reputation: 215117

You can try this:

(df.groupby('name', group_keys=False)
 .apply(lambda g: g.assign(sensitivity = g.price - g.price[g.tag == "base"].values))
 [lambda x: x.tag != "base"])

Or another option, pivot table to wide format, do the subtraction and then transform it back to long format:

wide_df = df.pivot_table(['price'], 'name', 'tag')   
(wide_df.sub(wide_df[('price', 'base')], axis=0)
 .drop(('price', 'base'), 1).stack(level=1)
 .reset_index())

Upvotes: 2

Subtracting subcolumn from a column of a dataframe

Answers (3)

Related Questions