CODEWITHSUNDEEP
pythonpandasdataframegroup-by
warrenfitzhenry
warrenfitzhenry

Reputation: 2299

Pandas series Max value for column based on index

I am trying to extract the max value for a column based on the index. I have this series:

Hour   Values
    1     0
    1     3
    1     1
    2     0
    2     5
    2     4  
    ...
    23    3
    23    4
    23    2
    24    1
    24    9
    24    2

and am looking to add a new column 'Max Value' that will have the maximum of the 'Values' column for each value, based on the index (Hour):

Hour   Values  Max Value
    1     0      3
    1     3      3
    1     1      3
    2     0      5
    2     5      5
    2     4      5
    ...
    23    3      4
    23    4      4
    23    2      4
    24    1      9
    24    9      9
    24    2      9

I can do this in excel, but am new to pandas. The closest I have come is this scratchy effort, which is as far as I have got, but I get a syntax error on the first '=':

df['Max Value'] = 0
df['Max Value'][(df['Hour'] =1)] = df['Value'].max()

Upvotes: 2

Views: 1291

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210982

Use transform('max') method:

In [61]: df['Max Value'] = df.groupby('Hour')['Values'].transform('max')

In [62]: df
Out[62]:
    Hour  Values  Max Value
0      1       0          3
1      1       3          3
2      1       1          3
3      2       0          5
4      2       5          5
5      2       4          5
6     23       3          4
7     23       4          4
8     23       2          4
9     24       1          9
10    24       9          9
11    24       2          9

Upvotes: 7

Related Questions