CODEWITHSUNDEEP
pythonpandasdataframe
user888469
user888469

Reputation: 137

Create a second data frame when conditions in the first data frame are met

I have two data frames. I would like to make an entry to the second data frame if a condition is met, then make another entry to the second data frame when the condition no longer holds.

Here are the first 7 rows of the first data frame:

df1: 
Date        Close price       MA20      Long    Long previous
  
2018-01-19        10.01       10.45        0                0
2018-01-20        10.55       10.65        0                0
2018-01-21        11.12       10.88        1                0
2018-01-22        10.88       10.84        1                1
2018-01-23        10.80       10.83        0                1
2018-01-24        10.90       10.85        1                0
2018-01-25        10.57       10.75        0                1

Where Long is a dummy variable 1 for when the Close price > MA20, and 0 otherwise.

Long previous if another dummy variable to check wether or not a position would be active.

And given the example data above I would want the second data frame to be:

df2:
Trade   Entry date   Exit date   Entry price   Exit price
1       2018-01-21   2018-01-23        11.12        10.80
2       2018-01-24   2018-01-25        10.90        10.57

(Trade is just the index + 1)

The logic behind my second data frame:

When Long == 1 and Long previous is == 0, the date is entered into the second data frame, then once Long!= 1, it records the date into the exit date column. The entry price is when Long became 1, and the exit price is the price on the date Long was no longer equal to 1.

How do I go about extracting this data from the first data frame?

Thanks

Upvotes: 1

Views: 55

Answers (1)

Corralien
Corralien

Reputation: 120391

Create 2 dataframes, one for Entry price and another for Exit price, then join them:

cols = {'Date': 'Entry date', 'Close price': 'Entry price'}
df1 = df.loc[df['Long'].eq(1) & df['Long previous'].eq(0), ['Date', 'Close price']] \
        .reset_index(drop=True).rename(columns=cols)

cols = {'Date': 'Exit date', 'Close price': 'Exit price'}
df2 = df.loc[df['Long'].eq(0) & df['Long previous'].eq(1), ['Date', 'Close price']] \
        .reset_index(drop=True).rename(columns=cols)

out = df1.join(df2)
out.insert(0, 'Trade', out.index + 1)

Output:

>>> out
   Trade Entry date  Entry price  Exit date  Exit price
0      1 2018-01-21        11.12 2018-01-23       10.80
1      2 2018-01-24        10.90 2018-01-25       10.57

Upvotes: 1

Related Questions