Retrieve value associated with max date from list of tuples in a series

Question

Overview

I have lists of tuples in a pandas series containing a price and associated date in each tuple.

Test data

SeriesDict = {0: [(0.9919, '2002-05-31 21:00:00+00:00'),
  (0.9898, '2002-09-30 21:00:00+00:00'),
  (0.9905, '2002-10-31 22:00:00+00:00')],
 1: [(1.01195, '2002-06-30 21:00:00+00:00'),
  (1.013, '2002-10-31 22:00:00+00:00')]}
TestSeries = pd.Series(SeriesDict)

What have I tried?

I can get the max date from the list of tuples as follows:

TestSeries.apply(lambda x: max([y[1] for y in x])).iloc[0]

which returns '2002-10-31 22:00:00+00:00'. I will later use this to create a new column with max date for each row in a new dataframe.

How do I now retrieve the price associated with max date (price is y[0] from the tuple) using apply or similar?

Desired output is a new column with the price associated with each date i.e. 0.9905 should be returned for this example.

jezrael · Accepted Answer · 2021-03-09T11:47:23.243

Use itemgetter:

from operator import itemgetter

print (TestSeries.apply(lambda x: max([y[1] for y in x])))
0    2002-10-31 22:00:00+00:00
1    2002-10-31 22:00:00+00:00


print (TestSeries.apply(lambda x: max(x,key=itemgetter(1))[0]))
0    0.9905
1    1.0130
dtype: float64

Another idea is create DataFrame and then processing:

s = TestSeries.explode()

df = pd.DataFrame(s.tolist(), columns=['a','b'], index=s.index)
print (df)
         a                          b
0  0.99190  2002-05-31 21:00:00+00:00
0  0.98980  2002-09-30 21:00:00+00:00
0  0.99050  2002-10-31 22:00:00+00:00
1  1.01195  2002-06-30 21:00:00+00:00
1  1.01300  2002-10-31 22:00:00+00:00


df = df.sort_values('b')
df = df[~df.index.duplicated(keep='last')]
print (df)
        a                          b
0  0.9905  2002-10-31 22:00:00+00:00
1  1.0130  2002-10-31 22:00:00+00:00

Or:

s = TestSeries.explode()

df = pd.DataFrame(s.tolist(), columns=['a','b']).assign(g = s.index)

df = df.sort_values('b').drop_duplicates(subset=['g'], keep='last')
print (df)
        a                          b  g
2  0.9905  2002-10-31 22:00:00+00:00  0
4  1.0130  2002-10-31 22:00:00+00:00  1

Retrieve value associated with max date from list of tuples in a series

1 Answers1