Home > Net >  How to take the average of every nth row of Pandas dataframe?
How to take the average of every nth row of Pandas dataframe?

Time:07-28

I want to create a dataframe with the average of groups of rows.

The last column treatment_df[patient] contains row values P1, P2, P3, P4, P5...and then followed by P1, P2, P3, P4, P5...and so on. For each P1-P5, I want to get the average row values and assign to new dataframe.

In the new dataframe, change the column names to remove the "_P1", "_P2", "_P3", "_P4", "_P5" values after the first underscore.

import pandas as pd
import numpy as np

treatment_df[:3].groupby(treatment_df[-1].index // 5).mean()

Traceback:

Traceback:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
/usr/local/lib/python3.7/dist-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
   3360             try:
-> 3361                 return self._engine.get_loc(casted_key)
   3362             except KeyError as err:

4 frames
/usr/local/lib/python3.7/dist-packages/pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

/usr/local/lib/python3.7/dist-packages/pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: -1

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
<ipython-input-202-583f129c3bdf> in <module>()
----> 1 treatment_df.groupby(treatment_df[-1].index // 5).mean()

/usr/local/lib/python3.7/dist-packages/pandas/core/frame.py in __getitem__(self, key)
   3456             if self.columns.nlevels > 1:
   3457                 return self._getitem_multilevel(key)
-> 3458             indexer = self.columns.get_loc(key)
   3459             if is_integer(indexer):
   3460                 indexer = [indexer]

/usr/local/lib/python3.7/dist-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
   3361                 return self._engine.get_loc(casted_key)
   3362             except KeyError as err:
-> 3363                 raise KeyError(key) from err
   3364 
   3365         if is_scalar(key) and isna(key) and not self.hasnans:

KeyError: -1

treatment_df.head().to_dict()

{'DNAJA1': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.378391959798995,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.2466734902763562,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.32702149437052197,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.282316442605998,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.20020120724346072},
 'DNAJA1P5': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': -0.19145728643216084,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.2231320368474923,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.2123848515864893,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': -0.18200620475698034,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.22032193158953728},
 'DNAJA2': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.22261306532663308,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.25588536335721596,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.266632548618219,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.24508790072388842,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.21730382293762568},
 'DNAJA3': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.18040201005025125,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.12384851586489252,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.10491299897645859,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.14477766287487076,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.13380281690140847},
 'DNAJA4': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.45477386934673364,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.20573183213920163,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.23490276356192435,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.2740434332988625,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.25955734406438624},
 'DNAJB1': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.5793969849246231,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.3316274309109519,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.38024564994882304,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.35056876938986564,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.30281690140845063},
 'DNAJB11': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.31005025125628144,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.21596724667349024,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.33725690890481075,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.32885211995863495,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.23943661971830987},
 'DNAJB12': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.08492462311557787,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.12077789150460601,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.11617195496417604,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.14477766287487076,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.09054325955734402},
 'DNAJB13': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': -0.04773869346733674,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.08904810644831117,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.08034800409416583,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': -0.050672182006204686,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.10462776659959759},
 'DNAJB14': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.27487437185929653,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.2947799385875129,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.3157625383828045,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.31437435367114797,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.289738430583501},
 'DNAJB2': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.18643216080402006,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.12998976458546577,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.1745138178096213,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.22647362978283345,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.11569416498993959},
 'DNAJB3': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': -0.09396984924623121,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.10133060388945755,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.08955987717502559,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': -0.0413650465356773,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.12273641851106647},
 'DNAJB4': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.26381909547738686,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.0071647901740019854,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.06601842374616176,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.07032057911065157,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.014084507042253579},
 'DNAJB5': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.12311557788944721,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.08802456499488233,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.05066530194472878,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.11168562564632886,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.08752515090543261},
 'DNAJB6 /// TMEM135': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.31909547738693467,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.3070624360286592,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.28812691914022526,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.25439503619441584,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.3078470824949698},
 'DNAJB7': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': -0.13015075376884422,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.17297850562947795,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.13152507676560904,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': -0.13960703205791103,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.19215291750503022},
 'DNAJB8': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': -0.042713567839195964,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.06243602865916064,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.07830092118730805,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': -0.03619441571871765,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.07042253521126762},
 'DNAJB9': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.17135678391959802,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.07062436028659165,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.16734902763561924,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.16442605997931756,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.03521126760563377},
 'DNAJC1': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.23567839195979898,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.24257932446264086,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.281985670419652,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.2740434332988625,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.23943661971830987},
 'DNAJC10': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.28190954773869353,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.23029682702149437,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.25946775844421693,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.2430196483971044,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.25754527162977864},
 'DNAJC11': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.14221105527638192,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.06448311156601842,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.07523029682702154,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.08066184074457086,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.11569416498993959},
 'DNAJC12': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': -0.11507537688442217,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.1361310133060389,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.12026612077789149,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': -0.07755946225439504,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.14185110663983905},
 'DNAJC13': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.35829145728643225,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.42988741044012296,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.4160696008188332,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.3557394002068252,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.4295774647887325},
 'DNAJC14': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.23768844221105523,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.2128966223132037,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.24718526100307064,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.2616339193381594,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.1971830985915493},
 'DNAJC15': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.2597989949748744,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.24257932446264086,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.2717502558853634,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.3298862461220268,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.22837022132796786},
 'DNAJC16': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.25778894472361813,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.2589559877175025,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.23183213920163764,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.24715615305067223,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.24849094567404426},
 'DNAJC17': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.08793969849246232,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.060388945752302955,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.04452405322415563,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.0744570837642193,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.07947686116700202},
 'DNAJC18': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.0809045226130653,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.03787103377686798,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.06397134083930399,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.07032057911065157,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.026156941649899377},
 'DNAJC19': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': -0.028643216080402028,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.08597748208802455,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.07932446264073689,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': -0.03309203722854182,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.07444668008048293},
 'DNAJC2': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.1522613065326633,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.09825997952917093,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.1253838280450359,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.14064115822130302,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.1106639839034205},
 'DNAJC21': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.20954773869346735,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.1463664278403276,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.21647901740020464,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.1789038262668046,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.1891348088531187},
 'DNAJC22': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': -0.05879396984924623,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.11463664278403277,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.08751279426816781,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': -0.06825232678386756,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.12173038229376258},
 'DNAJC24': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': -0.028643216080402028,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.029682702149437058,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.0537359263050153,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': -0.026887280248190256,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.0010060362173038016},
 'DNAJC25 /// DNAJC25-GNG10 /// GNG10': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.19145728643216084,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.1924257932446265,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.18065506653019445,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.22957600827300936,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.16700201207243454},
 'DNAJC27': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.04974874371859298,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.0051177072671443015,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.07318321392016376,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.05480868665977252,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.009054325955734393},
 'DNAJC28': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': -0.06482412060301512,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.12691914022517914,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.09365404298874105,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': -0.10754912099276112,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.13179074446680086},
 'DNAJC3': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.2648241206030152,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.3643807574206756,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.33828045035823956,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.22854188210961746,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.2977867203219316},
 'DNAJC30': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.12713567839195977,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.11873080859774823,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.09160696008188336,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.14064115822130302,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.09356136820925551},
 'DNAJC4 /// NUDT22': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.21959798994974872,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.2906857727737973,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.21340839303991813,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.27094105480868674,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.22032193158953728},
 'DNAJC5': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.242713567839196,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.3285568065506654,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.3096212896622313,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.2967942088934851,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.295774647887324},
 'DNAJC5B': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': -0.1482412060301508,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.1668372569089048,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.169396110542477,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': -0.16339193381592554,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.17907444668008052},
 'DNAJC5G': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': -0.09095477386934676,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.11873080859774823,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.12128966223132033,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': -0.11375387797311268,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.15895372233400404},
 'DNAJC6': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.03165829145728639,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': -0.008188331627430918,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.0035823950870010383,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.09307135470527408,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.014084507042253579},
 'DNAJC7': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.2175879396984925,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.2128966223132037,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.2441146366427841,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.2409513960703206,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.19919517102615691},
 'DNAJC8': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.14623115577889448,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.11668372569089054,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.1233367451381781,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.16235780765253363,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.1277665995975855},
 'DNAJC9': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.15527638190954773,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.09518935516888441,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.09877175025588536,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.10961737331954503,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.09356136820925551},
 'LOC646358': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.21959798994974872,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.25588536335721596,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.2697031729785057,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.22543950361944154,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.22434607645875249},
 'ST13': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.45577889447236175,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.34288638689866935,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.39048106448311165,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.4198552223371252,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.3581488933601609},
 'ST13P4': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.2507537688442211,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.17604912998976466,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.2154554759467758,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.23888314374353675,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.1961770623742455},
 'ST13P5': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.28190954773869353,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.19856704196519956,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.25230296827021503,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.27094105480868674,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.21327967806841047},
 'VEGFA': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.2668341708542714,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.11668372569089054,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.27584442169907875,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.35263702171664946,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.09154929577464782},
 'VEGFB': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.19547738693467331,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.14943705220061412,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 0.09672466734902767,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.15201654601861433,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 0.1006036217303823},
 'VEGFC': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 0.028643216080402028,
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 0.012282497441146378,
  '0h_P3_T1_TimeC1_PIDC1_Survivor': -0.009723643807574182,
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 0.025853154084798345,
  '0h_P5_T1_TimeC4_PIDC3_Survivor': -0.011066398390342085},
 'patient': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': 'P1',
  '0h_P2_T1_TimeC2_PIDC2_Survivor': 'P2',
  '0h_P3_T1_TimeC1_PIDC1_Survivor': 'P3',
  '0h_P4_T1_TimeC1_PIDC1_Survivor': 'P4',
  '0h_P5_T1_TimeC4_PIDC3_Survivor': 'P5'},
 'time': {'0h_P1_T1_TimeC1_PIDC4_Non-Survivor': '0h',
  '0h_P2_T1_TimeC2_PIDC2_Survivor': '0h',
  '0h_P3_T1_TimeC1_PIDC1_Survivor': '0h',
  '0h_P4_T1_TimeC1_PIDC1_Survivor': '0h',
  '0h_P5_T1_TimeC4_PIDC3_Survivor': '0h'}}

Expected output (the values in this example is random):

{'DNAJA1': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.378391959798995},
 'DNAJA1P5': {'0h_T1_TimeC1_PIDC4_Non-Survivor': -0.19145728643216084},
 'DNAJA2': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.22261306532663308},
 'DNAJA3': {'0h_TimeC1_PIDC4_Non-Survivor': 0.18040201005025125},
 'DNAJA4': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.45477386934673364},
 'DNAJB1': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.5793969849246231},
 'DNAJB11': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.31005025125628144},
 'DNAJB12': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.08492462311557787},
 'DNAJB13': {'0h_T1_TimeC1_PIDC4_Non-Survivor': -0.04773869346733674},
 'DNAJB14': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.27487437185929653},
 'DNAJB2': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.18643216080402006},
 'DNAJB3': {'0h_T1_TimeC1_PIDC4_Non-Survivor': -0.09396984924623121},
 'DNAJB4': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.26381909547738686},
 'DNAJB5': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.12311557788944721},
 'DNAJB6 /// TMEM135': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.31909547738693467},
 'DNAJB7': {'0h_T1_TimeC1_PIDC4_Non-Survivor': -0.13015075376884422},
 'DNAJB8': {'0h_T1_TimeC1_PIDC4_Non-Survivor': -0.042713567839195964},
 'DNAJB9': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.17135678391959802},
 'DNAJC1': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.23567839195979898},
 'DNAJC10': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.28190954773869353},
 'DNAJC11': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.14221105527638192},
 'DNAJC12': {'0h_T1_TimeC1_PIDC4_Non-Survivor': -0.11507537688442217},
 'DNAJC13': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.35829145728643225},
 'DNAJC14': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.23768844221105523},
 'DNAJC15': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.2597989949748744},
 'DNAJC16': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.25778894472361813},
 'DNAJC17': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.08793969849246232},
 'DNAJC18': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.0809045226130653},
 'DNAJC19': {'0h_T1_TimeC1_PIDC4_Non-Survivor': -0.028643216080402028},
 'DNAJC2': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.1522613065326633},
 'DNAJC21': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.20954773869346735},
 'DNAJC22': {'0h_T1_TimeC1_PIDC4_Non-Survivor': -0.05879396984924623},
 'DNAJC24': {'0h_T1_TimeC1_PIDC4_Non-Survivor': -0.028643216080402028},
 'DNAJC25 /// DNAJC25-GNG10 /// GNG10': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.19145728643216084},
 'DNAJC27': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.04974874371859298},
 'DNAJC28': {'0h_T1_TimeC1_PIDC4_Non-Survivor': -0.06482412060301512},
 'DNAJC3': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.2648241206030152},
 'DNAJC30': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.12713567839195977},
 'DNAJC4 /// NUDT22': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.21959798994974872},
 'DNAJC5': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.242713567839196},
 'DNAJC5B': {'0h_T1_TimeC1_PIDC4_Non-Survivor': -0.1482412060301508},
 'DNAJC5G': {'0h_T1_TimeC1_PIDC4_Non-Survivor': -0.09095477386934676},
 'DNAJC6': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.03165829145728639},
 'DNAJC7': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.2175879396984925},
 'DNAJC8': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.14623115577889448},
 'DNAJC9': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.15527638190954773},
 'LOC646358': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.21959798994974872},
 'ST13': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.45577889447236175},
 'ST13P4': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.2507537688442211},
 'ST13P5': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.28190954773869353},
 'VEGFA': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.2668341708542714},
 'VEGFB': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.19547738693467331},
 'VEGFC': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 0.028643216080402028},
 'patient': {'0h_T1_TimeC1_PIDC4_Non-Survivor': 'P1'},
 'time': {'0h_T1_TimeC1_PIDC4_Non-Survivor': '0h'}}

CodePudding user response:

(Note: rewritten following clarifications).

out = (
    df
    .set_axis(
        df.index.str.replace(r'_P\d', '', regex=True)
        .set_names('group')
    )
    .groupby(['patient', 'time', 'group'])
    .mean()
)

The replace part on the index eliminates '_P{n}' as requested. Further, patient and time are used as keys for the groupby. The result (on your sample data) is:

>>> out
                                                DNAJA1  DNAJA1P5    DNAJA2  \
patient time group                                                           
P1      0h   0h_T1_TimeC1_PIDC4_Non-Survivor  0.378392 -0.191457  0.222613   
P2      0h   0h_T1_TimeC2_PIDC2_Survivor      0.246673 -0.223132  0.255885   
P3      0h   0h_T1_TimeC1_PIDC1_Survivor      0.327021 -0.212385  0.266633   
P4      0h   0h_T1_TimeC1_PIDC1_Survivor      0.282316 -0.182006  0.245088   
P5      0h   0h_T1_TimeC4_PIDC3_Survivor      0.200201 -0.220322  0.217304   

                                                DNAJA3    DNAJA4    DNAJB1  \
patient time group                                                           
P1      0h   0h_T1_TimeC1_PIDC4_Non-Survivor  0.180402  0.454774  0.579397   
P2      0h   0h_T1_TimeC2_PIDC2_Survivor      0.123849  0.205732  0.331627   
P3      0h   0h_T1_TimeC1_PIDC1_Survivor      0.104913  0.234903  0.380246   
P4      0h   0h_T1_TimeC1_PIDC1_Survivor      0.144778  0.274043  0.350569   
P5      0h   0h_T1_TimeC4_PIDC3_Survivor      0.133803  0.259557  0.302817   

(...)

Notes:

  1. patient and time are now additional levels in the index. If that is not desired, simply add .reset_index(['patient', 'time'], drop=False) after .mean() above.

  2. you could consider splitting your index into the parts that matter to you. A simple example (but you should add consistency tests instead of just dropping time and patient) could be:

    idxcols = 'time patient t tc pidc survivor'.split()
    out = (
        df
        .set_axis(df.index.str.split('_', expand=True).set_names(idxcols))
        .drop(['time', 'patient'], axis=1)
        .groupby(idxcols)
        .mean()
    )
    
  • Related