Problem iteration columns and rows Dataframe

Question

Here is my problem :

Let’s say you have to buy and sell two objects with those following conditions:

You buy object A or B if its price goes below 150 (<150) and assuming that you can buy fraction of the object (so decimals are allowed)
If the following day the object is still below 150, then you just keep the object and do nothing
If the object is higher or equal to 150, then you sell the object and take profits

You start the game with 10000$

Here is the DataFrame with all the prices

df=pd.DataFrame({'Date':['2017-05-19','2017-05-22','2017-05-23','2017-05-24','2017-05-25','2017-05-26','2017-05-29'],
                 'A':[153,147,149,155,145,147,155],
                 'B':[139,152,141,141,141,152,152],})

df['Date']=pd.to_datetime(df['Date'])
df = df.set_index('Date')

The goal is to return a DataFrame with the number of object for A and B you hold and the number of cash you have left. If the conditions are met, the allocation for each object is the half of the cash you have if you don’t hold any object (weight =1/2) and is the rest if you already have one object (weight=1)

Let’s look at df first, I will also develop the new data frame that I’m trying to create (let’s call it df_end) :

On 2017-05-19, object A is 153$ and B is 139$ : You buy 35.97 object B (=5000/139) as the price is <150 —> You have 5000$ left in cash.

On 2017-05-22, object A is 147$ and B is 152$ : You buy 34.01 object A (=5000/147) as the price is <150 + You sell 35.97 object B at 152$ as it is >=150 --> You have now 5467,44$ left in cash thanks to the selling of B.

On 2017-05-23, object A is 149$ and B is 141$ : You keep your position on Object A (34.01 object) as it’s still below 150 and you buy 38.77 Object B (=5467.44/141) as the price is <150 —> You have now 0$ left in cash.

On 2017-05-24, object A is 155$ and B is 141$ : You sell 34.01 object A at 155$ as it’s above 150$ and you keep 38.77 Object B as it’s still below 150 —> You have now 5271.55$ left in cash thanks to the selling of A

On 2017-05-25, object A is 145$ and B is 141$: You buy 36.35 object A (5271.55/145) as it’s below 150 and you keep 38.77 Object B as it’s still below 150 —> You have now 0$ in cash

On 2017-05-26, object A is 147$ and B is 152$: You sell 38.77 object B at 152 as it’s above 150 and you keep 36.35 Object A as it’s still below 150 —> You have now 5893.04$ in cash thanks to the selling of Object B

On 2017-05-29, object A is 155$ and B is 152$: You sell 36.35 object A at 155 as it’s above 150 and you do nothing else as B is not below 150 —> You have now 11.527,29$ in cash thanks to the selling of Object A.

Hence, the new dataframe df_end should look like this (this is the Result I am looking for)

               A     B    Cash
Date        
2017-05-19   0      35.97  5000
2017-05-22   34.01  0      5467.64
2017-05-23   34.01  38.77  0
2017-05-24   0      38.77  5272.11
2017-05-25   36.35  38.77  0    
2017-05-26   36.35  0      5893.04
2017-05-29   0      0      11527.29

My principal problem is that we have to iterate over both rows and columns and this is the most difficult part. It's been a week that I'm trying to find a solution but I still don't find any idea on that, that is why I tried to explain as clear as possible.

So if somebody has an idea on this issue, you are very welcome. Thank you so much

MrNobody33 · Accepted Answer · 2020-06-26 04:57:48Z

You could try this:

import pandas as pd

df=pd.DataFrame({'Date':['2017-05-19','2017-05-22','2017-05-23','2017-05-24','2017-05-25','2017-05-26','2017-05-29'],
                 'A':[153,147,149,155,145,147,155],
                 'B':[139,152,141,141,141,152,152],})

df['Date']=pd.to_datetime(df['Date'])
df = df.set_index('Date')

print(df)

#Values before iterations
EntryCash=10000
newdata=[]
holding=False



#First iteration  (Initial conditions)
firstrow=df.to_records()[0]
possibcash=EntryCash if holding else EntryCash/2
prevroa=possibcash/firstrow[1] if firstrow[1]<=150 else 0
prevrob=possibcash/firstrow[2] if firstrow[2]<=150 else 0 
holding=any(i!=0 for i in [prevroa,prevrob])
newdata.append([df.to_records()[0][0],prevroa,prevrob,possibcash])

#others iterations
for row in df.to_records()[1:]:
    possibcash=possibcash if holding else possibcash/2
    a=row[1]
    b=row[2]
    if a>150:
        if prevroa>0:
            possibcash+=prevroa*a
            a=0
        else:
            a=prevroa
    else:
        if prevroa==0:
            a=possibcash/a
            possibcash=0
        else:
            a=prevroa
    if b>150:
        if prevrob>0:
            possibcash+=prevrob*b
            b=0
        else:
            b=prevrob
    else:
        if prevrob==0:
            b=possibcash/b
            possibcash=0
        else:
            b=prevrob
    prevroa=a
    prevrob=b
    newdata.append([row[0],a,b,possibcash])
    holding=any(i!=0 for i in [a,b])
    

df_end=pd.DataFrame(newdata, columns=[df.index.name]+list(df.columns)+['Cash']).set_index('Date')
print(df_end)

Output:

df
              A    B
Date                
2017-05-19  153  139
2017-05-22  147  152
2017-05-23  149  141
2017-05-24  155  141
2017-05-25  145  141
2017-05-26  147  152
2017-05-29  155  152

df_end
                    A          B          Cash
Date                                          
2017-05-19   0.000000  35.971223   5000.000000
2017-05-22  34.013605   0.000000   5467.625899
2017-05-23  34.013605  38.777489      0.000000
2017-05-24   0.000000  38.777489   5272.108844
2017-05-25  36.359371  38.777489      0.000000
2017-05-26  36.359371   0.000000   5894.178274
2017-05-29   0.000000   0.000000  11529.880831

If you want it rounded to two decimals, you can add:

df_end=df_end.round(decimals=2)
df_end:
                A      B      Cash
Date                              
2017-05-19   0.00  35.97   5000.00
2017-05-22  34.01   0.00   5467.63
2017-05-23  34.01  38.78      0.00
2017-05-24   0.00  38.78   5272.11
2017-05-25  36.36  38.78      0.00
2017-05-26  36.36   0.00   5894.18
2017-05-29   0.00   0.00  11529.88

Slight Differences Final Values

It is slight different to your desired output because sometimes you were rounding the values to two decimals and sometimes you didn't. For example: In your second row you put:

#second row
2017-05-22   34.01  0      5467.64

That means you used the complete value of object A, first row, that is 35.971223 not 35.97:

35.97*152
Out[120]: 5467.44

35.971223*152
Out[121]: 5467.6258960000005  #---->closest to 5467.64

And at row 3, again you used the real value, not the rounded:

#row 3
2017-05-24   0      38.77  5272.11

#Values
34.013605*155
Out[122]: 5272.108775

34.01*155
Out[123]: 5271.549999999999

And finally, at the last two rows you used the rounded value, I guess, because:

#last two rows
2017-05-26   36.35  0      5893.04
2017-05-29   0      0      11527.29

#cash values

#penultimate row, cash value
38.777489*152
Out[127]: 5894.178328

38.77*152
Out[128]: 5893.040000000001

#last row, cash value
5894.04+(155*36.35)
Out[125]: 11528.29  #---->closest to 11527.29

5894.04+(155*36.359371)
Out[126]: 11529.742505

Collectives™ on Stack Overflow

Problem iteration columns and rows Dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related