CODEWITHSUNDEEP
pythonpandaspandas-groupby
Luis Medina
Luis Medina

Reputation: 555

Pandas groupby and get row with max value as a result

I have a pandas dataframe with a index datetime, and I want to group by seconds and get as a result the row with the max value in the column 'a_ABS' but I only get the max value per column.

import pandas as pd

data = {'lat':[4.2471, 4.2646,4.2945, 4.2819,4.2635,4.2616,4.2731,4.2555],
        'lng':[-76.7504,-76.7198,-76.7069,-76.7251,-76.726,-76.7196,-76.715,-767.118],
       'a':[208.999,-894.0,-171.0,108.999,-162.0,-29.0,-143.999,-133.0],
       'e':[0.105,0.209,0.934,0.150,0.158,0.347,0.333,0.089]}

df = pd.DataFrame(data)
df = pd.DataFrame(data, index =['2020-01-01 16:32:14.105000-05:00', '2020-01-01 16:32:14.112000-05:00',
                                '2020-01-01 16:32:14.175000-05:00', '2020-01-01 16:32:14.176000-05:00',
                                '2020-01-01 16:32:14.211000-05:00','2020-01-01 16:32:14.220000-05:00',
                               '2020-01-01 16:32:14.310000-05:00','2020-01-01 16:32:14.327000-05:00'])
df.index = pd.to_datetime(df.index)


a=df
a['a_ABS']=a['a'].abs()
aa=a.groupby([a.index.floor('s')], as_index=True).max()

complete dataframe

dataframe with max value per colum

Upvotes: 0

Views: 86

Answers (2)

Daniel
Daniel

Reputation: 3527

Something like this will work:

import pandas as pd

# create dataframe:
df = pd.DataFrame({
    'lat':[4.2471, 4.2646,4.2945, 4.2819,4.2635,4.2616,4.2731,4.2555],
    'lng':[-76.7504,-76.7198,-76.7069,-76.7251,-76.726,-76.7196,-76.715,-767.118],
    'a':[208.999,-894.0,-171.0,108.999,-162.0,-29.0,-143.999,-133.0],
    'e':[0.105,0.209,0.934,0.150,0.158,0.347,0.333,0.089]
})

# set index:
df.index = pd.to_datetime([
    '2020-01-01 16:32:14.105000-05:00', '2020-01-01 16:32:14.112000-05:00',
    '2020-01-01 16:32:14.175000-05:00', '2020-01-01 16:32:14.176000-05:00',
    '2020-01-01 16:32:14.211000-05:00', '2020-01-01 16:32:15.220000-05:00',
    '2020-01-01 16:32:14.310000-05:00', '2020-01-01 16:32:15.327000-05:00',
])

# create absolute column:
df['a_ABS'] = df['a'].abs()

# create seconds column:
df['seconds'] = df.index.second

# group columns by seconds:
df_grouped = df.groupby(['seconds']).max()

# extract only the 'a_ABS' column:
df_grouped = df_grouped['a_ABS']

# reset index:
df_grouped = df_grouped.reset_index()

Upvotes: 0

Prachiti Prakash Prabhu
Prachiti Prakash Prabhu

Reputation: 684

You're almost there. Select the first row after sorting using a.iloc[:1]. Full code:

import pandas as pd

data = {'lat':[4.2471, 4.2646,4.2945, 4.2819,4.2635,4.2616,4.2731,4.2555],
        'lng':[-76.7504,-76.7198,-76.7069,-76.7251,-76.726,-76.7196,-76.715,-767.118],
       'a':[208.999,-894.0,-171.0,108.999,-162.0,-29.0,-143.999,-133.0],
       'e':[0.105,0.209,0.934,0.150,0.158,0.347,0.333,0.089]}

df = pd.DataFrame(data)
df = pd.DataFrame(data, index =['2020-01-01 16:32:14.105000-05:00', '2020-01-01 16:32:14.112000-05:00',
                                '2020-01-01 16:32:14.175000-05:00', '2020-01-01 16:32:14.176000-05:00',
                                '2020-01-01 16:32:14.211000-05:00','2020-01-01 16:32:14.220000-05:00',
                               '2020-01-01 16:32:14.310000-05:00','2020-01-01 16:32:14.327000-05:00'])
df.index = pd.to_datetime(df.index)


a=df
a['a_ABS']=a['a'].abs()

a=a.sort_values(by="a_ABS", ascending=False)
first_df=a.iloc[:1]

print(first_df)

Upvotes: 1

Related Questions