CODEWITHSUNDEEP
python-3.xpandasdataframe
lee.edward01
lee.edward01

Reputation: 473

python/pandas write value to previous row given a conditional

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.

Upvotes: 1

Views: 20

Answers (1)

BENY
BENY

Reputation: 323226

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

Upvotes: 2

Related Questions