Grouping columns in a Pandas Dataframe

Question

I would be grateful if someone could help me with a pandas dataframe problem I am having.

I am trying to group a pandas dataframe by columns but am unsure how to proceed. I have a dataframe with duplicate column names (A's and B's) and would like to group these to return the maximum value of A's and B's

Duplicate Column Dataframe

index      | A | A | A | B | B |
--------------------------------
2015-01-01 |   | 1 | 7 | 1 |   |
--------------------------------
2015-01-02 | 3 |   |   |   | 5 |

Dataframe after processing

index      | A | B |
--------------------
2015-01-01 | 7 | 1 |
---------------------
2015-01-02 | 3 | 5 |

unique_cols = [A,B]   
df.groupby(by = cols, axis = 1).max()

This does not work as I get an error message about the Grouper not 1-dimensional. I have also tried transposing the dataframe and grouping by the rows. However I get an IndexError message (index 0 is out of bounds for axis 0 with size 0)

Question:

How do you group a dataframe with duplicate columns to return the maximum from them from the group?

jezrael · Accepted Answer · 2017-02-22 15:22:09Z

4

I think you need first filter columns by subset and then groupby by all columns values by level=0 with axis=1:

cols = ['A','B']   
df = df[cols].groupby(level = 0, axis = 1).max()
print (df)
              A    B
index               
2015-01-01  7.0  1.0
2015-01-02  3.0  5.0

Last if necessary cast to int:

df = df[cols].groupby(level = 0, axis = 1).max().astype(int)
print (df)
            A  B
index           
2015-01-01  7  1
2015-01-02  3  5

edited Feb 22, 2017 at 15:22

answered Feb 22, 2017 at 15:17

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

12avi Over a year ago

Thank you, I spent a long time trying to figure that out and you made it look very easy!

Collectives™ on Stack Overflow

Grouping columns in a Pandas Dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related