Python Panda: Modify Dataframe with mask and create new Dataframe
Budget: $30 – $250 USD
I have the following df of 10 million rows:
Super speed is essential to this project, so please only apply if you know the fastest techniques to do this project.
date price MA1 MA2 MA3
date0 price0 12 10 8
date1 price1 11 11 11
date2 price2 12 21 14
date3 price3 13 12 15
date4 price4 14 14 14
date5 price5 15 17 14
date6 price6 19 16 15
date7 price7 15 12 13
date8 price8 11 10 13
date9 price9 21 12 13
date10 price10 13 11 14
date11 price11 14 14 14
date12 price12 16 16 16
date13 price13 34 32 23
date14 price14 12 12 12
I have the following df:
date price MA1 MA2 MA3
date0 price0 12 10 8
date1 price1 11 11 11
date2 price2 12 21 14
date3 price3 13 12 15
date4 price4 14 14 14
date5 price5 15 17 14
date6 price6 19 16 15
date7 price7 15 12 13
date8 price8 11 10 13
date9 price9 21 12 13
date10 price10 13 11 14
date11 price11 14 14 14
date12 price12 16 16 16
date13 price13 34 32 23
date14 price14 12 12 12
I filter my df using the following mask:
df =(data
.assign(same=lambda x: (x['MA1'] == x['MA2']) & (x['MA1'] == x['MA3']))
.loc[lambda x: x.same == True]
)
I get:
date price MA1 MA2 MA3
date1 price1 11 11 11
date4 price4 14 14 14
date11 price11 14 14 14
date12 price12 16 16 16
date14 price14 12 12 12
So the dates where MA1, MA2 and M3 are matching are date1, date4, date11, date 12, date14.
I would like to create a df which matches this format
date price price_past price_fut return_past return_future
date1 price1 price0 price2 (price1-price0)/price0 (price2-price1)/price1
date4 price4 price3 price5 (price4-price3)/price3 (price5-price4)/price4
date11 price11 price10 price12 (price11-price10)/price10 (price12-price11)/price11
date12 price12 price11 price13 (price12-price11)/price11 (price13 -price12)/price12
date14 price14 price13 price15 (price14-price13)/price13 (price15-price14)/price14
Then i want to get statistics on the number of rows where returns are of the same sign. (percentage, counts, etc..)
I would like then to extend the number of lookback and forward looking prices i incorporate in the statistic computation.. So for instance here I use 1 price back 1 price forward. I would like to be able to set the number of how many prices I choose to select. Thanks
Super speed is essential to this project, so please only apply if you know the fastest techniques to do this project.
date price MA1 MA2 MA3
date0 price0 12 10 8
date1 price1 11 11 11
date2 price2 12 21 14
date3 price3 13 12 15
date4 price4 14 14 14
date5 price5 15 17 14
date6 price6 19 16 15
date7 price7 15 12 13
date8 price8 11 10 13
date9 price9 21 12 13
date10 price10 13 11 14
date11 price11 14 14 14
date12 price12 16 16 16
date13 price13 34 32 23
date14 price14 12 12 12
I have the following df:
date price MA1 MA2 MA3
date0 price0 12 10 8
date1 price1 11 11 11
date2 price2 12 21 14
date3 price3 13 12 15
date4 price4 14 14 14
date5 price5 15 17 14
date6 price6 19 16 15
date7 price7 15 12 13
date8 price8 11 10 13
date9 price9 21 12 13
date10 price10 13 11 14
date11 price11 14 14 14
date12 price12 16 16 16
date13 price13 34 32 23
date14 price14 12 12 12
I filter my df using the following mask:
df =(data
.assign(same=lambda x: (x['MA1'] == x['MA2']) & (x['MA1'] == x['MA3']))
.loc[lambda x: x.same == True]
)
I get:
date price MA1 MA2 MA3
date1 price1 11 11 11
date4 price4 14 14 14
date11 price11 14 14 14
date12 price12 16 16 16
date14 price14 12 12 12
So the dates where MA1, MA2 and M3 are matching are date1, date4, date11, date 12, date14.
I would like to create a df which matches this format
date price price_past price_fut return_past return_future
date1 price1 price0 price2 (price1-price0)/price0 (price2-price1)/price1
date4 price4 price3 price5 (price4-price3)/price3 (price5-price4)/price4
date11 price11 price10 price12 (price11-price10)/price10 (price12-price11)/price11
date12 price12 price11 price13 (price12-price11)/price11 (price13 -price12)/price12
date14 price14 price13 price15 (price14-price13)/price13 (price15-price14)/price14
Then i want to get statistics on the number of rows where returns are of the same sign. (percentage, counts, etc..)
I would like then to extend the number of lookback and forward looking prices i incorporate in the statistic computation.. So for instance here I use 1 price back 1 price forward. I would like to be able to set the number of how many prices I choose to select. Thanks