Nested Loop to collect Results from multiple Functions into a seperate DataFrame Challenge

user_16564834 0
Viewed 93

Simplicity is key and I try to erase my thoughts and just explain my vision of what the code is doing and not write the code in this response.

So, I have a DF with numerous columns.

1st I loop, to Groupby column Market_Data = MD To create 21 MD groups.

Each group has 13 Timeframes = TF, so I group by Column Timeframe = TF to get 13 TF groups for each 21 MD, Market_Data groups.

I have 5 or more "def functions" that have a pass/fail result.

I want the "Sum Count" = SC of how many pass for each Timeframe so the results of a Dataframe would look like below......

           TF1     TF2       TF3 -......... TF13
   MD1     SC1     SC2       SC3            SC13
   MD2     SC1     SC2       SC3            SC13
   MD3     SC1     SC2       SC3            SC13

   etc.........
pandas dataframe python
2 Answers

I'm not sure if I fully understood your question, but here's a start point for a discussion.

First, let's start with a sample input data:

# Import libraries
import numpy as np
import pandas as pd

np.random.seed(123) # set seed for reproduction
sample_data = np.random.rand(21*13).reshape(21,13) * 10 # sample data
df = pd.DataFrame(sample_data, columns=[f'TF{i}' for i in range(1,14)], index=[f'MD{i}' for i in range(1,22)]) # dataframe

That's how our sample dataframe looks like:

           TF1       TF2       TF3       TF4       TF5       TF6       TF7       TF8       TF9      TF10      TF11      TF12      TF13
MD1   6.964692  2.861393  2.268515  5.513148  7.194690  4.231065  9.807642  6.848297  4.809319  3.921175  3.431780  7.290497  4.385722
MD2   0.596779  3.980443  7.379954  1.824917  1.754518  5.315514  5.318276  6.344010  8.494318  7.244553  6.110235  7.224434  3.229589
MD3   3.617887  2.282632  2.937140  6.309761  0.921049  4.337012  4.308628  4.936851  4.258303  3.122612  4.263513  8.933892  9.441600
MD4   5.018367  6.239530  1.156184  3.172855  4.148262  8.663092  2.504554  4.830343  9.855598  5.194851  6.128945  1.206287  8.263408
MD5   6.030601  5.450680  3.427638  3.041208  4.170222  6.813008  8.754568  5.104223  6.693138  5.859366  6.249035  6.746891  8.423424
MD6   0.831950  7.636828  2.436664  1.942230  5.724570  0.957125  8.853268  6.272490  7.234164  0.161292  5.944319  5.567852  1.589596
MD7   1.530705  6.955295  3.187664  6.919703  5.543832  3.889506  9.251325  8.416700  3.573976  0.435915  3.047681  3.981857  7.049588
MD8   9.953585  3.559149  7.625478  5.931769  6.917018  1.511275  3.988763  2.408559  3.434560  5.131282  6.666246  1.059085  1.308950
MD9   3.219806  6.615643  8.465062  5.532573  8.544525  3.848378  3.167879  3.542647  1.710818  8.291126  3.386708  5.523701  5.785515
MD10  5.215331  0.026881  9.883454  9.053416  2.076359  2.924894  5.200102  9.019114  9.836309  2.575421  5.643590  8.069687  3.943701
MD11  7.310730  1.610690  6.006986  8.658645  9.835216  0.793658  4.283473  2.045429  4.506365  5.477636  0.933267  2.968608  9.275842
MD12  5.690037  4.574120  7.535260  7.418622  0.485790  7.086974  8.392433  1.659379  7.809979  2.865366  3.064698  6.652615  1.113922
MD13  6.648724  8.878568  6.963113  4.403279  4.382144  7.650961  5.656420  0.849042  5.826711  8.148437  3.370664  9.275766  7.507170
MD14  5.740638  7.516440  0.791490  8.593891  8.215041  9.098717  1.286312  0.817801  1.384156  3.993787  4.243069  5.622184  1.222435
MD15  2.013995  8.116443  4.679876  8.079382  0.074264  5.515927  9.319321  5.821755  2.060957  7.177576  3.789858  6.683839  0.293197
MD16  6.359004  0.321979  7.447807  4.729130  1.217544  5.426359  0.667744  6.533649  9.960863  7.693973  5.737741  1.026353  6.998341
MD17  6.611679  0.490971  7.922993  5.187166  4.258677  7.881872  4.115692  4.810263  1.816288  3.213189  8.455330  1.869037  4.172911
MD18  9.890345  2.365998  9.168323  9.183975  0.912963  4.636527  5.022163  3.136690  0.473395  2.416856  0.955296  2.382499  8.077911
MD19  8.949783  0.432229  3.019468  9.805822  5.395048  6.263094  0.055454  4.849094  9.883285  3.751855  0.970382  4.619088  9.630045
MD20  3.418306  7.989227  7.988463  2.082483  4.433677  7.156013  4.105198  1.910070  9.674943  6.507504  8.654599  0.252424  2.669058
MD21  5.020711  0.674486  9.930333  2.364624  3.742922  2.140119  1.054459  2.324798  3.006101  6.344423  2.812348  3.622768  0.059428

Now let's define some functions for applying to each timeframe, returning True or False (pass or not, respectively). Also, we place them all into a list:

def sum_greater_than_100(series):
    return series.sum() > 100

def mean_greater_than_5(series):
    return series.mean() > 5

def std_less_than_3(series):
    return series.std() < 3

my_list_of_functions = [sum_greater_than_100, mean_greater_than_5, std_less_than_3]

We can apply our functions using agg dataframe method:

df_pass = df.agg(my_list_of_functions)

so we get as return another dataframe with bool values for each timeframe (columns) per function (index):

                       TF1    TF2   TF3   TF4    TF5   TF6    TF7    TF8    TF9   TF10   TF11   TF12   TF13
sum_greater_than_100  True  False  True  True  False  True   True  False   True  False  False   True   True
mean_greater_than_5   True  False  True  True  False  True   True  False   True  False  False  False  False
std_less_than_3       True  False  True  True   True  True  False   True  False   True   True   True  False

Finally, we can easily sum up the columns to get the result on how many functions passed by timeframe:

df_pass.sum()

TF1     3
TF2     0
TF3     3
TF4     3
TF5     1
TF6     3
TF7     2
TF8     1
TF9     2
TF10    1
TF11    1
TF12    2
TF13    1
user_70768190

It looks like pivot tables fit what you want to do better than groupby, but without your data it's difficult to say exactly what the code should be.

user_85441230
Related