python pandas groupby and subtract columns from different groups
Question: I have a dataframe df1

    
    
    pid     stat       h1         h2       h3      h4      h5      h6     ...     h20
    
    1        a        3.2        3.5       6.2     7.1    1.2      2.3    ...    3.2
    1        b        3.3        1.5       4.2     7.7    4.2      3.5   ...     8.4
    1        a        3.1        3.8       2.2     1.1    6.2      5.3   ...     9.2
    1        b        3.7        1.2       8.2     4.7    3.2      8.5   ...     2.4
    :        :         :          :         :       :      :        :      :      :
    2        a        2.2       3.8        6.2     7.3    1.3      4.3   ...     3.2
    2        b        4.3       1.3        4.2     5.7    2.2      3.1   ...     2.4
    2        a        2.1       3.7        2.4     1.6    6.4      9.3   ...     9.6
    2        b        3.8       1.3        8.7     3.7    7.2      8.3   ...     9.4
    :        :         :         :          :       :      :        :     :       : 
    3        a        2.2       3.8        6.2     7.3     1.3     4.3   ...     3.2
    3        b        4.3       1.3        4.2     5.7     2.2     3.1   ...     2.4
    3        a        2.1       3.7        2.4     1.6     6.4     9.3   ...     9.6
    3        b        3.8       1.3        8.7     3.7     7.2     8.3   ...     9.4
      :      :         :         :          :       :       :        :     :      :
    

I would like to obtain groups indexed on `pid` and `stat` and then subtract
`h` values of group1 from `h` values of group2 for a final `dataframe`
(`df2`). This final dataframe needs to be reindexed with numbers starting from
`0:len(groups)` Repeat it iteratively for all permutations of pid like 1-2,
1-3, 1-4, 2-1, 2-3 ... etc. I need to perform other calculations on the on the
final dataframe `df2`(values in the below `df2` are not exact subtracted, but
just a representation)

    
    
    pid(string)     stat    h1p1-h1p2   h2p1-h2p2   h3p1-h3p2   h4p1-h4p2   h5p1-h5p2   h6p1-h6p2   ...  h20p1-h2p2
    
       1-2           a        3.2         3.5         6.2         7.1         1.2         2.3        ...      3.2
       1-2           b        3.3         1.5         4.2         7.7         4.2         3.5        ...      8.4
       1-2           a        3.1         3.8         2.2         1.1         6.2         5.3        ...      9.2
       1-2           b        3.7         1.2         8.2         4.7         3.2         8.5        ...      2.4
       1-3      ....
    

I looked at options of;

    
    
      for (pid, stat), group in df1.groupby(['pid', 'stat']):
          print('pid  = %s Stat =  %s' %(pid, stat))
          print group
    

this gives me groups but, I am not sure how to access dataframes from this for
loop and use it for subtracting from other groups. Also

    
    
      df_grouped = df.groupby(['pid', 'stat']).groups()
    

still not sure how to access the new dataframe of groups and perform
operations. I would like to know, if this can be done using groupby or if
there is any better approach. Thanks in advance!


Answer: I implemented a generator and ignored the `stat` column because it makes no
different in any groups according to your sample. Please tell me if I did it
wrong.

    
    
    import pandas as pd
    from itertools import permutations
    
    def subtract_group(df, col):
        pid = df['pid'].unique()
    
        # select piece with pid == i
        segment = lambda df, i: df[df['pid'] == i].reset_index()[col]
    
        for x, y in permutations(pid, 2):
            result_df = pd.DataFrame(segment(df, x) - segment(df, y))
    
            # rename columns
            result_df.columns=["%sp%d-%sp%d" % (c, x, c, y) for c in col]
    
            # insert pid column
            result_df.insert(0, 'pid', '-'.join([str(x), str(y)]))
    
            yield result_df
    

You can test it with:

    
    
    # column name in your case
    columns = ['h' + str(i+1) for i in range(20)]
    
    print next(subtract_group(df1, columns))
    

Hope it helps.

