How to find highest values in multiple columns by grouping the row values using python?

Question

I am working on a dataset that contains three columns; roadType (string), cars and buses (integer values).

data = [["A", 5, 6], ["B", 7, 3], ["C", 9, 6], ["B", 2, 8], ["A", 4, 8], ["C", 8, 1], ["B", 1, 0]]

Now I want to group rows data on the basis of types in first column and then from these groups, I want to find the highest value from both the columns. i.e the expected output value is something like

output = [["A", 5, 8], ["B", 7, 8], ["C", 9, 6]]

How is it possible using python data analysis library pandas or any other library?

Answer 1

try this:

In [31]: d = pd.DataFrame(data, columns=['roadType','cars','buses'])

In [32]: d
Out[32]:
  roadType  cars  buses
0        A     5      6
1        B     7      3
2        C     9      6
3        B     2      8
4        A     4      8
5        C     8      1
6        B     1      0

In [33]: d.groupby('roadType').max().reset_index()
Out[33]:
  roadType  cars  buses
0        A     5      8
1        B     7      8
2        C     9      6