CODEWITHSUNDEEP
pythonpandas
Finger twist
Finger twist

Reputation: 3786

Returning last value of a column filtered by name in Pandas

I have a data set like this :

DATE,OPTION,SELL,BUY,DEAL
2015-01-01 11:00:01, blah1,0,1,open
2015-01-01 11:00:01, blah2,0,1,open
2015-01-01 11:00:01, blah3,0,1,open
2015-01-01 11:00:02, blah1,0,1,open
2015-01-01 11:00:02, blah2,0,1,open
2015-01-01 11:00:02, blah3,0,1,open

I read it in pandas using:

df = pd.DataFrame.from_csv(csv_data)

no problem there .

How would you return the last "SELL" value of "blah2" ?

Thank you

Upvotes: 0

Views: 553

Answers (4)

EdChum
EdChum

Reputation: 394041

There is a convenience method last that can be called on a groupby object, this returns the last value of each group, we can then filter this df on index value:

In [75]:

gp = df.groupby('OPTION').last()
gp
Out[75]:
                       DATE  SELL  BUY  DEAL
OPTION                                      
 blah1  2015-01-01 11:00:02     0    1  open
 blah2  2015-01-01 11:00:02     0    1  open
 blah3  2015-01-01 11:00:02     0    1  open
In [76]:

gp[gp.index == ' blah2']
Out[76]:
                       DATE  SELL  BUY  DEAL
OPTION                                      
 blah2  2015-01-01 11:00:02     0    1  open

Upvotes: 0

Xavi
Xavi

Reputation: 189

b[b['OPTION']==' blah2'].iloc[-1]['SELL']

Upvotes: 0

Marcin
Marcin

Reputation: 238219

You can group it by OPTION and get last row of a given group as follows:

import pandas as pd

df = pd.read_csv('data.csv')

grouped_df = df.groupby('OPTION')

print(grouped_df.get_group(' blah2').tail(1))

This gives:

4  2015-01-01 11:00:02   blah2     0    1  open

Upvotes: 1

qwwqwwq
qwwqwwq

Reputation: 7309

(df[df['OPTION'] == 'blah2']).tail(1)['SELL']

to get the last sell value for all options:

df[['SELL','OPTION']].groupby("OPTION").apply(lambda x: x.tail(1))

Upvotes: 1

Related Questions