Math with hierarchical pandas dataframe-CodePudding

I have a multiindex DataFrame like the following:

pd.DataFrame({('Number', 1): {('A', 'Blue', 'One', 'Bar'): 1,
  ('A', 'Blue', 'Two', 'Foo'): 2},
 ('Number', 2): {('A', 'Blue', 'One', 'Bar'): 2,
  ('A', 'Blue', 'Two', 'Foo'): 4},
 ('Number', 3): {('A', 'Blue', 'One', 'Bar'): 3,
  ('A', 'Blue', 'Two', 'Foo'): 6},
 ('Slope', ''): {('A', 'Blue', 'One', 'Bar'): 1,
  ('A', 'Blue', 'Two', 'Foo'): 2}})

That looks like this, but missing the slope column:

                    Number  Slope
                    1   2   3   
A   Blue    One Bar 1   2   3   1
            Two Foo 2   4   6   2

And I'm not sure how to calculate and populate the Slope column. To make the calculation I'm assuming the column names as the x-values and the values within the columns as the y-values.

CodePudding user response：

Try with np.polyfit:

# get the actual data, without `Slope` column
data = df.iloc[:, :-1]
df['Slope'] = np.polyfit(data.columns.get_level_values(1)
                             .to_numpy(dtype='int'), 
                         data.T.to_numpy(), 1)[0]

Output:

               Number       Slope
                    1  2  3      
A Blue One Bar      1  2  3   1.0
       Two Foo      2  4  6   2.0