CODEWITHSUNDEEP
rfor-loopquantmod
bodega18
bodega18

Reputation: 654

For Loop to Rename Column Names of Many Objects R

I am looking for a way to rename the columns of several objects with a for loop or other method in R. Ultimately, I want to be able to bind the rows of each Stock object into one large data frame, but cannot due to differing column names. Example below:

AAPL <-
Date        AAPL.Open  AAPL.High AAPL.Low  AAPL.Close  AAPL.Volume  AAPL.Adjusted  Stock  pct_change
2020-05-14  304.51     309.79    301.53    309.54      39732300     309.54         AAPL   0.61
2020-05-15  300.35     307.90    300.21    307.71      41561200     307.71         AAPL  -0.59


GOOG <-  
Date        GOOG.Open GOOG.High  GOOG.Low  GOOG.Close  GOOG.Volume   GOOG.Adjusted  Stock  pct_change 
2020-05-14  1335.02   1357.420   1323.910  1356.13     1603100       1356.13        GOOG   0.50
2020-05-15  1350.00   1374.480   1339.000  1373.19     1705700       1373.19        GOOG   1.26

For this example I have 2 objects (AAPL and GOOG), but realistically I would be working with many more. Can I create a for loop to iterate through each object, and rename the 2nd column of each to "Open", 3rd column to "High", 4th column to "Low",.... etc so I can then bind all these objects together?

I already have a column named "Stock", so I do not need the Ticker part of the column name.

Upvotes: 0

Views: 424

Answers (3)

Len Greski
Len Greski

Reputation: 10855

Using quantmod we can read a set of stock ticker symbols, clean their names & rbind() into a single data frame.

There are three key features illustrated within this answer, including:

  1. Use of get() to access the objects written by quantmod::getSymbols() once they are loaded into memory.

  2. Use of the symbol names passed into lapply() to add a symbol column to each data frame.

  3. Conversion of the dates stored as row names in the xts objects written by getSymbols() to a data frame column.

First, we'll use getSymbols() to read data from yahoo.com. 

library(quantmod)
from.dat <- as.Date("12/02/19",format="%m/%d/%y")
to.dat <- as.Date("12/06/19",format="%m/%d/%y")

theSymbols <- c("AAPL","AXP","BA","CAT","CSCO","CVX","XOM","GS","HD","IBM",
                "INTC","JNJ","KO","JPM","MCD","MMM","MRK","MSFT","NKE","PFE","PG",
                "TRV","UNH","UTX","VZ","V","WBA","WMT","DIS","DOW")
getSymbols(theSymbols,from=from.dat,to=to.dat,src="yahoo")

# since quantmod::getSymbols() writes named data frames, need to use
# get() with the symbol names to access each data frame
head(get(theSymbols[[1]]))



> head(get(theSymbols[[1]]))
           AAPL.Open AAPL.High AAPL.Low AAPL.Close AAPL.Volume AAPL.Adjusted
2019-12-02    267.27    268.25   263.45     264.16    23621800      262.8231
2019-12-03    258.31    259.53   256.29     259.45    28607600      258.1370
2019-12-04    261.07    263.31   260.68     261.74    16795400      260.4153
2019-12-05    263.79    265.89   262.73     265.58    18606100      264.2359

Having illustrated how to access the symbol objects in the global environment, we'll use lapply() to extract the dates from the row names, clean the column headings, and write the symbol name as a column for each symbol's data object. 

# convert to list
symbolData <- lapply(theSymbols,function(x){
     y <- as.data.frame(get(x))
     colnames(y) <- c("open","high","low","close","volume","adjusted")
     y$date <- rownames(y)
     y$symbol <- x
     y
})

Finally, we convert the list of data frames to a single data frame. 

#combine to single data frame
combinedData <- do.call(rbind,symbolData)
rownames(combinedData) <- 1:nrow(combinedData)

...and the output:

> nrow(combinedData)
[1] 120
> head(combinedData)
    open   high    low  close   volume adjusted       date symbol
1 267.27 268.25 263.45 264.16 23621800 262.8231 2019-12-02   AAPL
2 258.31 259.53 256.29 259.45 28607600 258.1370 2019-12-03   AAPL
3 261.07 263.31 260.68 261.74 16795400 260.4153 2019-12-04   AAPL
4 263.79 265.89 262.73 265.58 18606100 264.2359 2019-12-05   AAPL
5 120.31 120.36 117.07 117.26  5538200 116.2095 2019-12-02    AXP
6 116.04 116.75 114.65 116.57  3792300 115.5256 2019-12-03    AXP
>

Upvotes: 2

akrun
akrun

Reputation: 887048

With lapply, we can loop over the list and remove the prefix in the column names with sub. This can be done without any external packages

lst1 <- lapply(list(AAPL, GOOG),  function(x)  {
        colnames(x) <- sub(".*\\.", "", colnames(x))
    x})

Upvotes: 0

Jan
Jan

Reputation: 5254

If you can guarantee the order of these columns this should do it:

for(df in list(AAPL, GOOG))
  colnames(df) <- c("Date", "Open", "High", "Low", "Close", "Volume", "Adjusted", "Stock", "pct_change")

Upvotes: 2

Related Questions