CODEWITHSUNDEEP
pythonpandas
Kevin Ling
Kevin Ling

Reputation: 550

How to return the first index when the value are greater than something

This is to simulate the "Knock out" situations in the structured investment. Generally, there are three stocks: Stock A; Stock B; Stock C, and their prices are observed monthly to check whether they are above 100% of the price of first month (KO level).

The basic dataframe looks like this:

           Stock-A Stock-B Stock-C
2010-01-01   10      20       40
2010-02-01   9.5     18       31
2010-03-01   10.5    22       39
2010-04-01   11.5    23       36

If one of their prices going above the KO level, the function should return the month and do not need to monitor the price any more.

For example, the stock-A across the KO level at 2010-03-01, so the function should label this as a successful "Knocked-out" stock as well as return the date, which is 2010-03-01.

If there is a stock never across the line, the function should iterate over the end and return this as a non-knocked out stock. How can I achieve this?

Upvotes: 2

Views: 276

Answers (1)

wvxvw
wvxvw

Reputation: 9464

Here's my attempt, with some assumptions (plus, take this with a grain of salt: I don't claim to be a Pandas expert, just thought this would be an interesting problem to work on)

df = pandas.DataFrame([
    {'date': '2010-01-01', 'stock_a': 10, 'stock_b': 20, 'stock_c': 30},
    {'date': '2010-01-02', 'stock_a': 9.5, 'stock_b': 18, 'stock_c': 31},
    {'date': '2010-03-01', 'stock_a': 10.5, 'stock_b': 22, 'stock_c': 39},
    {'date': '2010-04-01', 'stock_a': 11.5, 'stock_b': 23, 'stock_c': 36},
])
# Assuming you want the first chronological record of such an event
df = df.sort_values(['date', 'stock_a', 'stock_b', 'stock_c'])
df2 = pandas.concat([
    df['stock_a'] > df['stock_a'][0],
    df['stock_b'] > df['stock_b'][0],
    df['stock_c'] > df['stock_c'][0],
], axis=1)
df2['date'] = True
# At this point columns stock_{a,b,c} are populated with NaN in place where the value
# was less than the first observation
df3 = df[df2].query('stock_a == stock_a & stock_b == stock_b & stock_c == stock_c')

if not df3.empty:
    print df3.iloc[[0]]
print 'No match'

Upvotes: 2

Related Questions