4

This is probably stupid but i've not been able to see a solution.

When downloading FRED data it has horrible names such as 

FranceExports <<- getSymbols("FRAXTEXVA01CXMLM", src = "FRED", auto.assign = FALSE)

I want to put a lot of data in a data.table

eu <- data.table( FranceExports , GermanyExports, ... )

but the table returns

 head(FranceExports)
           FRAXTEXVA01CXMLM
1960-01-01        595665297
1960-02-01        610479446
1960-03-01        612014108
1960-04-01        559989074
1960-05-01        579246653
1960-06-01        557069763
> eu <- data.table(FranceExports)
> head(eu)
   FRAXTEXVA01CXMLM
1:        595665297
2:        610479446
3:        612014108
4:        559989074
5:        579246653
6:        557069763

Clearly I want to be able to access eu$FranceExports, not eu$FRAXTEXVA01CXMLM

> eu$FranceExports
NULL

I'm still new to R, so what I have figured out is that FranceExports is just a reference to the original data structure. Fine, I get that. And that env = userdata looks for a frame or table to put the data into, but it still doesn't simply change the name so that I can reference it directly.

So, how can I easily do this (easy, because there are a lot of EU countries with a lot of data ^^)

===== Apropos Convo with Louis below ==== Now each series looks like this

if (!exists("NetherlandsExports")) NetherlandsExports <<- getSymbols("NLDXTEXVA01CXMLM", src = "FRED", auto.assign = FALSE)
colnames(eu)[colnames(eu) == "NLDXTEXVA01CXMLM"] <- "NetherlandsExports"



if (!exists("GermanyExports")) SpainExports <<- getSymbols("ESPXTEXVA01CXMLM", src = "FRED", auto.assign = FALSE)
colnames(eu)[colnames(eu) == "GRCXTEXVA01CXMLM"] <- "GreeceExports"
Edward Allan
  • 53
  • 7

2 Answers2

2

Change the column name

You can solve this problem by changing the column name. This should do the trick:

eu <- data.table(FranceExports)
colnames(eu)[colnames(eu) == "FRAXTEXVA01CXMLM"] <- "FranceExports"
eu$FranceExports #This will work now

If you need to assign more than one names, you can use a code like this:

#Assuming a data.frame with 4 columns
colnames(df) <- c("col1", "col2", "col3", "col4")

Hope this helps.

Louis
  • 3,592
  • 2
  • 10
  • 18
  • 1
    Hi Louis, many thanks. That is a possibility and it is the answer to another question I haven't asked yet (so cheers for that) but... it means an extra line of code for every data series and I'm expecting to be something like 26 countries times 6 or more series for each, so I'm hoping there's a better way. – Edward Allan Apr 16 '20 at 13:54
  • 1
    @EdwardAllan Hi Edward, you're welcome. I edited the code to show you how to assign more than one column name at the time, but I'm afraid that you still need to write all the country names by hand. – Louis Apr 16 '20 at 14:03
  • 1
    ouch.... Would there be a way of concatenating it into one line? My main concern is making errors and with lists of over 100 series there is a great risk of errors creeping in. – Edward Allan Apr 16 '20 at 14:11
  • Yes, I see you. I don't know very well the FRED package, but from what I saw you have to download all the data separately first. Once you have build a `data.frame` you can change the columns names using this single (but long!) line of code: `colnames(df) <- c("FranceExports", "GermanyExports", ...)`. Keep in mind that you must be careful to respect the order of elements. – Louis Apr 16 '20 at 14:31
  • yes, although more long winded I think error checking would be easier on the first method. If I only had a few series then the second would be fine. So now I have ``` if (!exists("NetherlandsExports")) NetherlandsExports <<- getSymbols("NLDXTEXVA01CXMLM", src = "FRED", auto.assign = FALSE) colnames(eu)[colnames(eu) == "NLDXTEXVA01CXMLM"] <- "NetherlandsExports" --- that looked horrible - I'll repost in the question --- for each series – Edward Allan Apr 16 '20 at 14:33
1

Maybe something like (caveat that I have not tested it):

cfg <- c(France="FRAXTEXVA01CXMLM", Netherlands="NLDXTEXVA01CXMLM", Germany="ESPXTEXVA01CXMLM")

Reduce(function(x, y) merge(x, y, by="Date", all=TRUE),
    lapply(names(cfg), function(x) {
        DF <- quantmod::getSymbols(cfg[x], src="FRED") #setNames(data.frame(x=1:5, row.names=Sys.Date()+1:5), cfg[x])
        DT <- setDT(DF, keep.rownames=TRUE)
        setnames(DT, old=names(DT), new=c("Date", x))
    })
)

FYI, there is a mergelist function under dev in github/rdatatable to do this kind of recursive merge of a list of data.tables.

chinsoon12
  • 25,005
  • 4
  • 25
  • 35
  • that looks very useful. It will take me a little while to work through it but I'll let you know how it works out. Thank you. – Edward Allan Apr 17 '20 at 12:27
  • Hi, again @chinsoon12. Sorry I've not been able to get this to work so far although I admit it's way above my current level. I'll keep on at it, but if you have a chance to look it over I'd appreciate it. Thanks. – Edward Allan Apr 19 '20 at 15:15
  • i have issues with calling the St Louis Fed API. Hence i created some dummy data.frames that look like those that you post. You can give the new code a try. Cheers! – chinsoon12 Apr 20 '20 at 05:17
  • Hi again, Sorry for the delay. So I think I've been stupid and not realised that I'm not actually working with data.table/frame, but actually with XTS objects. so the code fails at setDT(x). Although this doesn't change the fundamental problem of being able to set decent names for the series after the merge and removing NA. I think the basic principle is right, it would need to be the R equivalent of (what I would do) FOR x in vector length ; loop. Which is what I'm learning Reduce does, although I can't work out how yet. Still reading! – Edward Allan Apr 25 '20 at 10:20