import *.csv and merge the sheets

Question

I need to import several *.csv sheets and afterwards I have to combine them. I have a Panel dataset with quarterly data over 100 periods, eleven countries and 20 variables. I have each variable as extra csv. My aim is to have a dataframe in the way that the first column are the countries, the secound the date and from the third onwards my variables.

Here are the first rows of a *.csv:

Date,Austria,Belgium,Finland,France,Germany,Greece,Ireland,Italy,Netherlands,Portugal,Spain
Q1 1990,91.739,8.978,-12.598,28.071,37.638,94.159,34.13,13.214,24.101,40.43,2.556

Q2 1990,134.143,-2.89,-26.014,16.421,48.189,166.933,19.602,8.507,7.219,17.512,-19.39

Q3 1990,84.336,-3.891,-32.364,14.527,31.013,376.683,21.09,6.773,2.065,24.313,-7.234

What did I do so far?:

path = "~/R file/"  
filenames <- dir(path, pattern = '\\.csv', full.names = F)
data <- read.csv(filenames[1])
data <- melt(data, id.vars="Date", value.name=filenames[1], variable.name="Country",na.rm=F)
data <- data[,c(1,2)]
 for (i in length(filenames)){
   print(paste("opening file ", filenames[i],sep=""))
   dta <- read.csv(filenames[i])
   dta_long <- melt(dta, id.vars="Date", value.name=filenames[i], variable.name="Country",na.rm=F)
   data <- merge(data,dta_long,by = c("Country","Date"))
    }

Print shows me a/my problem, that the loop only dealt with the last csv file. Therefore I have only three columns in data, instead of 22. Unfortunately I do not see how to solve the problem. Does someone has an idea?

Thanks in advance!

Should be `for (i in 1:length(filenames))` instead of `for (i in length(filenames))` — rmccloskey, Apr 17 '15 at 21:30

score 0 · Accepted Answer · answered Apr 17 '15 at 22:04

0

Function length return a number. So your i in...

for (i in length(filenames)){}

will go only through one value - length of the vector filename.

The solution could be as in the comment...

for (i in 1:length(filenames)){}

or ever more nice and clear is iteration through values of the vector...

for (file in filenames){
   print(paste("opening file ", file,sep=""))
   dta <- read.csv(file)
   dta_long <- melt(dta, id.vars="Date", value.name=file, variable.name="Country",na.rm=F)
   data <- merge(data,dta_long,by = c("Country","Date"))
}

answered Apr 17 '15 at 22:04

Kamil S Jaron

494
10
23

Nice idea in the last loop, but it will fail on the first iteration when `data` isn't defined. Maybe borrow OP's code initializing `data` as the first file, then you can do `for (file in filenames[-1])`... – Gregor Thomas Apr 17 '15 at 22:13
Or you can define dataframe with columns and without rows... dta <- data.frame('Date' = character(0), 'Country' = character(0)) – Kamil S Jaron Apr 18 '15 at 06:26

import *.csv and merge the sheets

1 Answers1