Linear interpolation within groups

Question

Say I have a multi-index dataframe df with one column A. I would like to create a new column B where I assign the values m (e.g. 0) and M (e.g. 1) to the lowest and highest values within each group of column A, while linearly interpolating all values between.

As an example, consider the following df. I would like to do the interpolation for every X group

                     A 
X      Y                              
bar   one    -0.007381 
      two    -1.219794 
baz   one     0.145578 
      two    -0.249321 
      three  -0.249321 
      four    0.21     
foo   one    -1.046479 
      two     1.314373 
qux   one     0.716789 
      two     0.385795 

I believe I can do this with combination of aggregate and transform in Pandas, but am not sure how.

Answer 1

It think it may be better if you use groupby rather than mutliIndex: Data:

X      Y    A                          
bar   one    -0.007381 
bar   two    -1.219794 
baz   one     0.145578 
baz   two    -0.249321 
baz   three  -0.249321 
baz   four    0.21     
foo   one    -1.046479 
foo   two     1.314373 
qux   one     0.716789 
qux   two     0.385795 

And:

In [47]:

df['new']=df.groupby(df.X).transform(lambda x: (x - x.min()) / x.ptp()).A
print df
     X      Y         A       new
0  bar    one -0.007381  1.000000
1  bar    two -1.219794  0.000000
2  baz    one  0.145578  0.859745
3  baz    two -0.249321  0.000000
4  baz  three -0.249321  0.000000
5  baz   four  0.210000  1.000000
6  foo    one -1.046479  0.000000
7  foo    two  1.314373  1.000000
8  qux    one  0.716789  1.000000
9  qux    two  0.385795  0.000000

[10 rows x 4 columns]