How to calculate slope in rows of a multiindex dataframe in pandas?

Question

I have this df sample:

  Name  Cords   A      B    C
  Ob1     y    4.95  2.15  2.29
          x   36.33  47.8  460.2   
  Ob2     y    1.22  2.34  2.57
          x   36.33  47.8  460.2

where "Name" is an index and "Cords" is index in second level, that is, multiindex DataFrame, but now I want to calculate the slope between de index x and y in order to get:

  Name  Cords   A      B     C
  Ob1     y    4.95  2.15    2.29
          x   36.33  47.8   460.2 
        slope   0   -0.24    3.39
  Ob2     y    1.22  2.34    2.57
          x   36.33  47.8   460.2 
        slope   0    0.09  5.57e-4

do I have to use df.xs?, i dont know exactly how multiindex df work.

slope = (y2-y1)/(x2-x1)

score 0 · Answer 1 · answered Jun 11 '21 at 11:30

used shift as np.roll() means x2 is value of C in position A
it appears that you sample data is incorrect for Ob1 slope for C
to your question multi-index slicing has been used .loc

from scipy.ndimage.interpolation import shift

df = pd.DataFrame({'A': {('Ob1', 'y'): 4.95,
  ('Ob1', 'x'): 36.33,
  ('Ob2', 'y'): 1.22,
  ('Ob2', 'x'): 36.33},
 'B': {('Ob1', 'y'): 2.15,
  ('Ob1', 'x'): 47.8,
  ('Ob2', 'y'): 2.34,
  ('Ob2', 'x'): 47.8},
 'C': {('Ob1', 'y'): 2.29,
  ('Ob1', 'x'): 460.2,
  ('Ob2', 'y'): 2.57,
  ('Ob2', 'x'): 460.2}})

x1 = df.loc[(slice(None), "x"), :].values
y1 = df.loc[(slice(None), "y"), :].values
x2 = shift(x1, [0, -1])
y2 = shift(y1, [0, -1])
dfs = pd.concat(
    [
        df,
        pd.DataFrame(
            shift((y2 - y1) / (x2 - x1), [0, 1]),
            columns=df.columns,
            index=pd.MultiIndex.from_product(
                [df.index.get_level_values(0).unique(), ["slope"]]
            ),
        ),
    ]
).sort_index()

output

               A          B           C
Ob1 slope   0.00  -0.244115    0.000339
    x      36.33  47.800000  460.200000
    y       4.95   2.150000    2.290000
Ob2 slope   0.00   0.097646    0.000558
    x      36.33  47.800000  460.200000
    y       1.22   2.340000    2.570000

How to calculate slope in rows of a multiindex dataframe in pandas?

1 Answers1

output