CODEWITHSUNDEEP
pythonpandasdataframe
Alex
Alex

Reputation: 33

Python Pandas : Lowest/Highest value since a certain row

I have a data set (pd.DataFrame) which looks somewhat like this:

date    0
2015 Q4 -1.0
2016 Q1 0.8
2016 Q2 0
2016 Q3 0.1
2016 Q4 0.7
2017 Q1 -0.4
2017 Q2 -0.2
2017 Q3 1.2
2017 Q4 0.8
2018 Q1 -0.4

I am trying to find the last time the data in my column was as low as the latest row (in this case: it would have been in 2017 Q1), and would need it to work so that if the latest number was positive it would find the last time it was as high.

After having found that, I would need to extract the index value of that row. ('2017 Q1' in this case)

I've been trying what feels like an abomination of code (I only recently started coding):

from heapq import nsmallest
from heapq import nlargest
    def second_smallest(numbers):
        return nsmallest(2, numbers)[-1]
    def second_largest(numbers):
        return nlargest(2, numbers)[-1]


    def next_smallest(df = pd.DataFrame()):
        x = df[-1:].item()
        if x <= 0:
            y = df[df>= x]
            second = second_smallest(y)
            return  df.index[df == second].tolist()

which does not give me the required result, so any help would be greatly appreciated. Been looking around for a while and either my wording is wrong when searching (second language so it happens sometimes), or there isn't much on how to do that.

Many thanks.

Upvotes: 3

Views: 289

Answers (2)

Jimmy
Jimmy

Reputation: 197

Labeling your first column as 'id' and second column as 'value', one approach could be to identify all rows with the same value as the latest row, and then get the last date that had this value. See below with mock up data:

df = pd.DataFrame({'id': ['date1', 'date2', 'date3', 'date4', 'date5', 'date6', 'date7', 'date8'], 'value': [3, -1, 0, 3, 2, 5, 4, 3]})
df_same_value_as_latest = df.loc[df['value']==df['value'].tolist()[-1]]
date_required = df_same_value_as_latest.iloc[df_same_value_as_latest.shape[0]-2,:]['id']
print(date_required)

Upvotes: 0

screenpaver
screenpaver

Reputation: 1130

Does this give you what you are looking for?

if df.value.iloc[-1] < 0: 
    idx = df.index[df.value<=df.value.iloc[-1]][-2]
else:
    idx = df.index[df.value>=df.value.iloc[-1]][-2]

date = df.date.iloc[idx]

Upvotes: 2

Related Questions