Pandas Groupby using multiple criteria on different axis

user_1180974 0
Viewed 1090

I have a df DataFrame like : 

      | A | B | A_ | B_ |COMMON|
--------------------------------
0     | 1 | 3 | 0  | 1  | a    |
--------------------------------
1     | 8 | 5 | 4  | 0  | a    |
--------------------------------
2     | 3 | 6 | 2  | 4  | b    |
--------------------------------
3     | 9 | 9 | 1  | 7  | b    |

And I want to group all columns X with X_ for all letters A,B,... (let's say, the group is called X as well), and group as well using COMMON. I would like to apply later function like std() to all the grouped values.

So the result would look like: 

COMMON  |  A     |  B     |
---------------------------
a       |std(...)|std(...)|
---------------------------
b       |std(...)|std(...)|

I have been able to group either one or the other, using df.groupby(['COMMMON']) for one criteria and .groupby(mapping_function, axis=1) for the other one, but how do I use them together?

Another alternative for an intermediate step would be to concatenate individual columns so that I would get: 

      | A | B |COMMON|
----------------------
0     | 1 | 3 |a    |
---------------------
1     | 8 | 5 |a    |
---------------------
2     | 3 | 6 |b    |
---------------------
3     | 9 | 9 |b    |
---------------------
0     | 0 | 1 |a    |
---------------------
1     | 4 | 0 |a    |
---------------------
2     | 2 | 4 |b    |
---------------------
3     | 1 | 7 |b    |

But I also don't know how to do that. Also as you might see, I don't really care about the index.

Thank you for your help!

pandas-groupby pandas python
3 Answers

You can reshape first by melt with removing _ from column names (for better performance, because strip only few values) with pivot_table:

df = (df.rename(columns=lambda x: x.strip('_'))
        .melt('COMMON')
        .pivot_table(index='COMMON',columns='variable', values='value', aggfunc='std'))

print (df)
variable         A         B
COMMON                      
a         3.593976  2.217356
b         3.593976  2.081666
user_29010020

IIUC

df.melt('COMMON').assign(variable=lambda x : x['variable'].str.rstrip('_')).\
       groupby(['COMMON','variable']).value.std().unstack()
Out[18]: 
variable         A         B
COMMON                      
a         3.593976  2.217356
b         3.593976  2.081666
user_79645270

Just groupby

h = lambda x: x[-1][0]

df.set_index('COMMON', append=True).stack().groupby(['COMMON', h]).std().unstack()

               A         B
COMMON                    
a       3.593976  2.217356
b       3.593976  2.081666
user_23366540
Related