R DF referencing 2 different rows in same column (Returns)

Question

I am trying to create a new column in my DF called "Returns" but I'm having difficulty finding how to divide 2 variables in the same column but different rows.

Here's the DF:

Date                  Open High  Low AdjClose  
2014-02-13 2014-02-13 1815 1830 1809     1830 
2014-02-12 2014-02-12 1820 1827 1816     1819 
2014-02-11 2014-02-11 1800 1824 1800     1820 
2014-02-10 2014-02-10 1796 1800 1792     1800 
2014-02-07 2014-02-07 1776 1798 1776     1797 
2014-02-06 2014-02-06 1753 1774 1753     1773

I'm assuming the code looks something like this:

SPXprices.df$Returns <- (SPXprices.df$AdjClose - SPXprices$AdjClose[x,x])/SPXprices$AdjClose[x,x]

I was hoping someone could help me here.

Any insight would be greatly appreciated!

What values would be in the `Returns` column in this case? It's unclear to me which 2 values from `AdjClose` are being used to calculate `Returns`? — Jota, Feb 15 '14 at 05:51
Just trying to compute simple returns @Frank (Current Day-Previous Day)/Previous Day. I would like to know the code to reference a past row in the same column. TY — Blackmarkt, Feb 15 '14 at 15:30

score 1 · Answer 1 · answered Feb 15 '14 at 07:23

Is this what you are looking for?

SPXprices.df <- read.table(header=T, text=
"
Date                  Open High  Low AdjClose  
2014-02-13 2014-02-13 1815 1830 1809     1830 
2014-02-12 2014-02-12 1820 1827 1816     1819 
2014-02-11 2014-02-11 1800 1824 1800     1820 
2014-02-10 2014-02-10 1796 1800 1792     1800 
2014-02-07 2014-02-07 1776 1798 1776     1797 
2014-02-06 2014-02-06 1753 1774 1753     1773
")

#sorting the data
SPXprices.df <- SPXprices.df[order(SPXprices.df$Date),]
#using lagpad from http://stackoverflow.com/a/13128713/2862090 to lag
lagpad <- function(x, k) {
  c(rep(NA, k), x)[1 : length(x)] 
}

#showing the result of lagpad
SPXprices.df$lagAdjClose <- lagpad(SPXprices.df$AdjClose,1)

#calculate returns
SPXprices.df$Returns <- (SPXprices.df$AdjClose - lagpad(SPXprices.df$AdjClose,1))/lagpad(SPXprices.df$AdjClose,1)

SPXprices.df
                Date Open High  Low AdjClose lagAdjClose       Returns
2014-02-06 2014-02-06 1753 1774 1753     1773          NA            NA
2014-02-07 2014-02-07 1776 1798 1776     1797        1773  0.0135363790
2014-02-10 2014-02-10 1796 1800 1792     1800        1797  0.0016694491
2014-02-11 2014-02-11 1800 1824 1800     1820        1800  0.0111111111
2014-02-12 2014-02-12 1820 1827 1816     1819        1820 -0.0005494505
2014-02-13 2014-02-13 1815 1830 1809     1830        1819  0.0060472787

Thanks @Rfan that worked just wondering what is the code to reference the previous row in the same column for instance lets say I wanted to call on yesterday's AdjClose (without using ladpad) would the code look like $SPXprices.df$AdjClose[x]? Thanks again! — Blackmarkt, Feb 15 '14 at 14:43

Jota · Accepted Answer · 2014-02-16T16:45:47.510

1

Here is a way to do it by referencing the previous day

returns<-sapply(1:length(SPXprices.df$AdjClose), 
  FUN=function(x) (SPXprices.df$AdjClose[x] - SPXprices.df$AdjClose[x+1])/SPXprices.df$AdjClose[x+1])


SPXprices.df$Returns <- returns

In the sapply function, I am referencing the next day using SPXprices.df$AdjClose[x+1])

edited Feb 16 '14 at 16:45

answered Feb 15 '14 at 17:10

Jota

17,281
7
63
93

Thanks @Frank. Quick question why did you unlist the returns? – Blackmarkt Feb 16 '14 at 15:51
@user3308813 I think the first time I tried a solution, the output was a list. So, I used `unlist`, but is unnecessary with this solution. I'll fix it. – Jota Feb 16 '14 at 16:45

R DF referencing 2 different rows in same column (Returns)

2 Answers2