1

Given a dataframe with a date-time index:

df = 

                  FB       GOOGL      HD     JNJ       KO      MCD   MSFT  
Date                                                                        
2007-02-23    0.0000   235.49821   40.98   64.16  23.6350   46.050  28.90   
2007-03-02    0.0000   219.55276   38.98   61.84  22.9250   43.630  27.76   
2007-03-09    0.0000   226.65464   38.65   62.14  23.7850   44.160  27.29   
2007-03-16    0.0000   220.37356   37.50   60.51  23.5900   43.460  27.31   
2007-03-23    0.0000   231.12898   38.23   60.50  24.0300   45.050  27.99   
2007-03-30    0.0000   229.16207   36.74   60.25  24.0000   45.030  27.87   
2007-04-06    0.0000   235.97868   38.02   61.56  24.7550   45.770  28.55   
2007-04-13    0.0000   233.37616   37.89   62.35  24.9450   47.680  28.62   
2007-04-20    0.0000   241.47901   39.24   65.08  26.0650   48.350  29.02   
2007-04-27    0.0000   239.73732   38.46   64.16  26.0350   48.960  30.13   
2007-05-04    0.0000   235.78349   38.92   64.47  26.6250   49.920  30.56   
2007-05-11    0.0000   233.60138   38.91   62.26  26.3600   50.700  30.90   
2007-05-18    0.0000   235.37309   38.87   63.42  26.3050   52.330  30.83   
2007-05-25    0.0000   241.88941   38.37   63.23  25.9450   50.860  30.48   
2007-06-01    15.000   250.46772   39.22   63.38  26.3900   50.910  30.60   
2007-06-08    20.000   257.94997   37.94   62.13  25.8300   51.380  30.05   
2007-06-15    25.000   253.19536   38.00   62.76  25.7900   52.190  30.49   
2007-06-22    30.000   262.69957   39.53   61.11  25.8250   50.420  29.50   
2007-06-29    35.000   261.93383   39.35   61.62  26.1500   50.760  29.47

I have a list of dates where I would need do a formula calculation. However, due to some specific errors, my code breaks when a stock enters the dataframe/market (0 means the stock has no value at this time) exactly on the date.

Let's say the list of dates where I need to do calculations is 

myList = ["2007-06-01"]

I would like to have an updated dataframe where if the date of the index matches the myList date and if the value before said date of a column is 0, then make that 0 into the value on the next column down as shown below. 

dfUpdated = 

                  FB       GOOGL      HD     JNJ       KO      MCD   MSFT  
Date                                                                        
2007-02-23    0.0000   235.49821   40.98   64.16  23.6350   46.050  28.90   
2007-03-02    0.0000   219.55276   38.98   61.84  22.9250   43.630  27.76   
2007-03-09    0.0000   226.65464   38.65   62.14  23.7850   44.160  27.29   
2007-03-16    0.0000   220.37356   37.50   60.51  23.5900   43.460  27.31   
2007-03-23    0.0000   231.12898   38.23   60.50  24.0300   45.050  27.99   
2007-03-30    0.0000   229.16207   36.74   60.25  24.0000   45.030  27.87   
2007-04-06    0.0000   235.97868   38.02   61.56  24.7550   45.770  28.55   
2007-04-13    0.0000   233.37616   37.89   62.35  24.9450   47.680  28.62   
2007-04-20    0.0000   241.47901   39.24   65.08  26.0650   48.350  29.02   
2007-04-27    0.0000   239.73732   38.46   64.16  26.0350   48.960  30.13   
2007-05-04    0.0000   235.78349   38.92   64.47  26.6250   49.920  30.56   
2007-05-11    0.0000   233.60138   38.91   62.26  26.3600   50.700  30.90   
2007-05-18    0.0000   235.37309   38.87   63.42  26.3050   52.330  30.83   
2007-05-25    15.000   241.88941   38.37   63.23  25.9450   50.860  30.48   
2007-06-01    15.000   250.46772   39.22   63.38  26.3900   50.910  30.60   
2007-06-08    20.000   257.94997   37.94   62.13  25.8300   51.380  30.05   
2007-06-15    25.000   253.19536   38.00   62.76  25.7900   52.190  30.49   
2007-06-22    30.000   262.69957   39.53   61.11  25.8250   50.420  29.50   
2007-06-29    35.000   261.93383   39.35   61.62  26.1500   50.760  29.47

so essentially 2007-05-25 FB stock became 15.000 from 0.000 due to those two conditions.

Improve this question

1 Answer 1

Reset to default
2

Using get_loc

df.iloc[df.index.get_loc("2007-06-01")-1,0]=df.iloc[df.index.get_loc("2007-06-01"),0]
df
Out[531]: 
              FB      GOOGL     HD    JNJ      KO    MCD   MSFT
Date                                                           
2007-02-23   0.0  235.49821  40.98  64.16  23.635  46.05  28.90
2007-03-02   0.0  219.55276  38.98  61.84  22.925  43.63  27.76
2007-03-09   0.0  226.65464  38.65  62.14  23.785  44.16  27.29
2007-03-16   0.0  220.37356  37.50  60.51  23.590  43.46  27.31
2007-03-23   0.0  231.12898  38.23  60.50  24.030  45.05  27.99
2007-03-30   0.0  229.16207  36.74  60.25  24.000  45.03  27.87
2007-04-06   0.0  235.97868  38.02  61.56  24.755  45.77  28.55
2007-04-13   0.0  233.37616  37.89  62.35  24.945  47.68  28.62
2007-04-20   0.0  241.47901  39.24  65.08  26.065  48.35  29.02
2007-04-27   0.0  239.73732  38.46  64.16  26.035  48.96  30.13
2007-05-04   0.0  235.78349  38.92  64.47  26.625  49.92  30.56
2007-05-11   0.0  233.60138  38.91  62.26  26.360  50.70  30.90
2007-05-18   0.0  235.37309  38.87  63.42  26.305  52.33  30.83
2007-05-25  15.0  241.88941  38.37  63.23  25.945  50.86  30.48
2007-06-01  15.0  250.46772  39.22  63.38  26.390  50.91  30.60
2007-06-08  20.0  257.94997  37.94  62.13  25.830  51.38  30.05
2007-06-15  25.0  253.19536  38.00  62.76  25.790  52.19  30.49
2007-06-22  30.0  262.69957  39.53  61.11  25.825  50.42  29.50
2007-06-29  35.0  261.93383  39.35  61.62  26.150  50.76  29.47
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I am getting KeyError: '2018-03-21' is there a reason why this might be occuring?
@lee.edward01 Check your index whether the 2018-03-21 is exist
you were right, I miswrote 2018 for 2008. now I am getting a AttributeError: 'numpy.float64' object has no attribute 'iloc'. Thank you for all your help
@lee.edward01 I have no idea about this error , did you add 1.0 ? or maybe try convert it to int , since index position only accept int

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.