[FIXED] Python: merge columns with same name, keeping minimum value

Issue

I have a big matrix, like this:

df:
    A    A    A    B    B  ... (column names) 
 A  2    4    5     9    2
 A  6    8    7     6    4
 A  5    2    6     4    5
 B  3    4    1     3    4
 B  4    5    3     1    4

 .
 .
(row names)

I would like to merge the columns with same name, and findig the minimum value. At the end I would like to have a matrix like this:

  df_min:
    A   B  ... (column names) 
 A  2    2
 A  6    4
 A  2    4     
 B  1    3     
 B  3    1     
 .
 .
(row names)

My intentions, afterwards (outside of the question), is to merge the rows as well. Desired outcome:

  df_min:
    A   B  ... (column names) 
 A  2    2   
 B  1    1     
 .
 .
(row names)

I tried this:

df_min= df.groupby('df.columns, axis=1').agg(np.min)

But it didn't work, it removed some rows (for example, removing entirely row A)... EDIT: Apparently, it worked fine but I had two columns with different names but whitespace at the end of the name. These methods reorder the columns, which confused me.

A snipped of the dataframe:

Solution

Simply groupby on the level=0 for each axis:

df.groupby(level=0, axis=1).min()

output:

   A  B
A  2  2
A  6  4
A  2  4
B  1  3
B  3  1

both axes:

df.groupby(level=0, axis=1).min().groupby(level=0).min()

output:

   A  B
A  2  2
B  1  1

Alternatively, use a single groupby trough a stack/unstack:

df.stack().groupby(level=[0,1]).min().unstack()

output:

   A  B
A  2  2
B  1  1

Answered By - mozway