Converting a Data Frame to Xts

Transform the dataframe to xts object

The code below will give you what you want with dplyr and piping. I'm not sure why everything needs to be done with piping as not every function is built for magrittr pipes. For as.xts you need to reference to the date column with .$ if you want to use piping.

But the outcome will not be useful. xts transforms the data in a matrix and since Symbol and date are in the matrix the whole matrix will be a character matrix.

library(xts)
library(dplyr)

df %>% 
  mutate(date = as.Date(date)) %>% 
  as.xts(order.by = .$date)

           symbol   date         open_pr gross  avg_aud ts    tv  
2017-10-12 "N2"     "2017-10-12" "10.2"  " 460" " 19"   " 59" " 6"
2017-10-12 "NJ"     "2017-10-12" " 2.7"  " 121" "  3"   " 32" " 1"
2017-10-12 "K-Kl"   "2017-10-12" " 0.5"  "  21" "  0"   " 31" " 0"
2017-10-12 "K-P3"   "2017-10-12" " 4.5"  " 203" "  5"   " 34" " 2"
2017-10-12 "K-N"    "2017-10-12" " 2.9"  " 130" "  5"   " 57" " 2"
2017-10-12 "KP+"    "2017-10-12" " 8.1"  " 363" " 21"   " 83" " 7"
2017-10-12 "K13"    "2017-10-12" " 2.3"  " 102" "  4"   " 59" " 1"
2017-10-12 "KS"     "2017-10-12" " 1.0"  "  45" "  1"   " 28" " 0"
2017-10-12 "KTotal" "2017-10-12" "43.2"  "1946" "153"   "113" "49"
2017-10-12 "P500"   "2017-10-12" "28.5"  "1282" " 92"   "103" "29"
2017-10-12 "P800"   "2017-10-12" " 5.8"  " 262" " 10"   " 53" " 3"
2017-10-12 "P23"    "2017-10-12" " 6.7"  " 303" " 14"   " 69" " 5"
2017-10-12 "P55"    "2017-10-12" " 5.7"  " 256" "  6"   " 33" " 2"
2017-10-12 "PA"     "2017-10-12" " 0.1"  "   6" "  0"   "  4" " 0"
2017-10-12 "PKA"    "2017-10-12" "10.0"  " 449" " 27"   " 87" " 9"

But if you want something like your example on the bottom with google, use something like below.

Step 1 is to create a function to create xts timeseries with the symbol in front of the column names. Step 2 split your original data by symbol and create a list to contain all the data in a named list. Step 3 is to use Map to apply the function to the data. After this you can access all the data in the my_data list.

my_func <- function(x, symbol){
  index <- as.Date(x[["date"]])
  x <- x[, setdiff(colnames(x), c("symbol", "date"))]
  x <- xts::as.xts(x, order.by = index)
  colnames(x) <- paste0(symbol, ".", colnames(x))
  return(x)
}

my_data <- split(df, df$symbol)

my_data <- Map(my_func, my_data, names(my_data))

head(my_data, 2)
$`K-Kl`
           K-Kl.open_pr K-Kl.gross K-Kl.avg_aud K-Kl.ts K-Kl.tv
2017-10-12          0.5         21            0      31       0

$`K-N`
           K-N.open_pr K-N.gross K-N.avg_aud K-N.ts K-N.tv
2017-10-12         2.9       130           5     57      2

Converting yearly data into xts format

At first, in the main dataset, it seems you should change class = "Date" to class = "Integer" for the Year variable in your dput data to prevent it to create year "1975"

structure(list(Year = structure(c(2015, 2016, 2017, 2018, 2011, 
2012, 2013, 2014, 2007, 2009, 2010, 2015, 2016, 2017, 2018, 2007, 
2009, 2010, 2015, 2016, 2017, 2018, 2015, 2016, 2017, 2018, 2015, 
2016, 2017, 2018, 2015, 2016, 2017, 2018, 2011, 2012, 2013, 2014, 
2015, 2016, 2017, 2018, 2015, 2016, 2017, 2018, 2015, 2016, 2017, 
2018, 2015, 2016, 2017, 2018, 2011, 2012, 2013, 2014, 2007, 2009, 
2010, 2015, 2016, 2017, 2018, 2015, 2016, 2017, 2018, 2007, 2009, 
2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2015, 2016, 
2017, 2018, 2007, 2009, 2010, 2012, 2013, 2014, 2015, 2016, 2017, 
2018, 2007, 2009, 2010, 2015, 2016, 2017), class = "Integer"), ...

R does not recognize just year as a Date format, so you should convert year to y/m/d format.

You can do it by converting for example "2018" to "2018-12-31" (considering end of the year as year (or whatever you want).

So the codes below will do it:

library(xts)

data_new <- data #creating a new dataset to preserve original  
class(data_new$Year)  #now class of year is integer

## [1] "Integer"


#formatting integer year to a Date format
data_new$Year <- as.Date(paste(data_new$Year, 12, 31, sep = "-")) 

class(data_new$Year) #check changed format of year

## [1] "Date"

#creating xts object
data_xts <- as.xts(data_new, data_new[,-c("Year")], order.by = data_new$Year)

head(data_new)

##         Year ReporterName PartnerName TradeValue in 1000 USD year_group
## 1 2015-12-31       Angola       China               14320566  2015-2018
## 2 2016-12-31       Angola       China               13923092  2015-2018
## 3 2017-12-31       Angola       China               19487067  2015-2018
## 4 2018-12-31       Angola       China               24517058  2015-2018
## 5 2011-12-31       Angola       China               24360793  2011-2014
## 6 2012-12-31       Angola       China               33710030  2011-2014
##   total_average_period_in_1000USD total_average_period_byPartner_in_1000USD
## 1                        251327.4                                  18061946
## 2                        251327.4                                  18061946
## 3                        251327.4                                  18061946
## 4                        251327.4                                  18061946
## 5                       1938789.4                                  29386292
## 6                       1938789.4                                  29386292
##   percentage_of_group
## 1            71.86620
## 2            71.86620
## 3            71.86620
## 4            71.86620
## 5            15.15703
## 6            15.15703


# check periodicity
periodicity(data_xts$Year)

## 0 seconds periodicity from 2007-12-31 to 2018-12-31

How to convert a data.frame table to an xts object

Assuming that what you have is as in the Note at the end either of these work:

library(xts)

# 1
z <- read.csv.zoo("data.csv", format = "%m/%d/%Y")
x <- as.xts(z)

# 2    
DF <- read.csv("data.csv")
z <- read.zoo(DF, format = "%m/%d/%Y")
x <- as.xts(z)

# Calculate returns

library(PerformanceAnalytics)

Return.calculate(x, method = 'log')
##               TickerA     TickerB    TickerC
## 2000-01-03         NA          NA         NA
## 2000-01-04 0.04879016          NA 0.04879016
## 2000-01-05 0.04652002 -0.03077166         NA

Note

Lines <- "date,TickerA,TickerB,TickerC
01/03/2000,20,,40
01/04/2000,21,33,42
01/05/2000,22,32,
"
cat(Lines, file = "data.csv")

Correct way to convert data frame to XTS to be used in PortfolioAnalytics package

I found the solution. When converting an entire data frame to XTS. Even if the original formats of the data frame are correct, ie date = date and numbers = num. The XTS conversion will cause the formats to change. Hence why numbers were changing to characters thus causing the error with PortfolioAnalytics package.

The answer here is to pull specific column out of the data frame. Convert that to XTS whilst maintain the correct format during the process.

The below retains the number format while converting from data frame to XTS:

xts = xts(new.df$long_exit_eq, order.by=as.Date(new.df$Date, format="%m/%d/%Y"))

And if you want to pull two columns from the data frame. Lets say its your signal return column and buy and hold return column.

Can pull them both from the data frame like below as xts and then bind them together.

# Pull select columns from data frame to make XTS whilst retaining formats 
xts = xts(new.df$long_exit_eq, order.by=as.Date(new.df$Date, format="%m/%d/%Y")) 
xts1 = xts(new.df$clret, order.by=as.Date(new.df$Date, format="%m/%d/%Y")) 

# Join XTS together 
compare <- cbind(xts,xts1)

Converting a single column Dataframe to XTS is losing column name

When you have only one column in the data the default nature is to drop it's dimensions and convert it into vector.

DF.subset[,-1]
#[1] 1 2 3

To avoid that and keep dataframe as dataframe you can use drop = FALSE.

DF.subset[,-1, drop = FALSE]

#  C1
#1  1
#2  2
#3  3

Now using it in xts function.

library(xts)
DF.TS <- xts(DF[,-1, drop = FALSE], order.by =dv)
DF.subset.TS <- as.xts(DF.subset[,-1, drop = FALSE], order.by =dv)

colnames(DF.TS)
#[1] "C1" "C2"
colnames(DF.subset.TS)
#[1] "C1"