Concat dataframe to multi index dataframe with gradient values

Question

I have a Multi-index dataframe with multiple test result values. For further data analysis I want to add the derivation to the dataframe.

I tried to either calculate it via a lambda function directly after I grouped the dataframe. Grouping (mean values) is required due to the noise in the sampling. Later I want to delete the rows from my dataframes where the derivative is <= 0.

The simplified Multi-index dataframe looks like this:

arrays = [['LS13', 'LS13', 'LS13', 'LS13','LS14','LS14','LS14','LS14','LS14','LS14','LS14','LS14'],[0, 2, 2.5, 3,0,2,5,5.5,6,6.5,7,7.5]]
index = pd.MultiIndex.from_arrays(arrays, names=('File', 'Flow Rate Setpoint [l/s]'))
df = pd.DataFrame({('Flow Rate [l/s]','mean') : [-0.057,2.089,2.496,3.011,0.056,2.070,4.995,5.519,6.011,6.511,7.030,7.499],('Time [s]','mean') : [42.225,104.909,165.676,226.446,42.225,104.918,469.560,530.328,591.100,651.864,712.660,773.034],('Shear Stress [Pa]','mean') : [-0.698,5.621,7.946,11.278,-0.774,6.557,40.610,48.370,54.685,58.414,58.356,56.254]},index=index)

if I run my code:

import numpy as np

xls = ['LS13', 'LS14']

gradient = [pd.Series(np.gradient(df.loc[(i),('Shear Stress [Pa]','mean')],df.loc[(i),('Time [s]','mean')])) for i in xls]

now I want to concat gradient to df on axis = 1, Title could be df['Gradient''values'].

So my pd.Series looks like:

    Gradient
     values
                
0   0.100808
1   0.069048
2   0.04654
3   0.054801
0   0.116941
1   0.087431
2   0.149521
3   0.115805
4   0.082639
5   0.030213
6   -0.017938
7   -0.034806

next step would be to remove/drop the rows where ['Gradient','values'] <= 0, in my example ['LS14','7':'7.5']

When I tried to concatenate both Dataframe df and Series gradient (I'm aware that the indexes are different)

merged = pd.concat([pd.DataFrame(df),pd.Series(gradient)], axis=1 , ignore_index = True)

Errors are usually one of the following:

ValueError: Buffer dtype mismatch, expected 'Python object' but got 'long long'

TypeError: cannot concatenate object of type "<class 'list'>"; only pd.Series, pd.DataFrame, and pd.Panel (deprecated) objs are valid

I would also assume there is an easier way to get this done with an lambda function and just apply it in place.

merged = pd.concat([df, pd.Series([gradient], name=('Gradient','value'))], axis=1)

I would have expected that to work, but I also get a miss match error:

ValueError: Buffer dtype mismatch, expected 'Python object' but got 'long long'

when I try:

df[("Gradient","value")] =pd.Series([pd.Series(np.gradient(df.loc[(i),('Shear Stress [Pa]','mean')],df.loc[(i),('Time [s]','mean')])) for i in xls])

The 'Gradient','value' column gets correctly added to the dataframe but the values are again NaN.

Answer 1

You can try groupby().apply():

def get_gradients(x):
    gradients = np.gradient(x[('Shear Stress [Pa]', 'mean')],x[('Time [s]', 'mean')] )
    return pd.Series(gradients, index=x.index)

df[('Gradient','Value')] = (df.groupby('File', group_keys=False)
                              .apply(get_gradients)
                           )