R 3.0.3 Rbind Multiple CSV Files

Write a function to filter multiple excel files in the directory and rbind the results

Try this. Use do.call() to rbind all files that will be stored in a list L. Here the code:

library(readxl)
#Function
apply_fun <- function(filename) {
  data <- readxl::read_excel(filename, sheet = "Section Cut Forces - Design", 
                             skip = 1, col_names = TRUE)
  data <- data[-1,]
  data <- subset(data, StepType == "Max")
  Moment <- data[,c(1,2,6,10)]
  Moment$id <- filename
  return(Moment)
}
#Code
filenames <- list.files(pattern = "\\.xlsx", full.names = TRUE)
L <- lapply(filenames,apply_fun)
#Bind
Out <- do.call(rbind,L)

Output with test file (Some rows):

head(Out)
# A tibble: 6 x 5
  SectionCut OutputCase V2                 M3                 id             
  <chr>      <chr>      <chr>              <chr>              <chr>          
1 1          LL-3       35.317999999999998 51208.425000000003 ./84-9-S00.xlsx
2 1          LL-2       35.917999999999999 48494.190999999999 ./84-9-S00.xlsx
3 1          LL-1       26.937999999999999 33676.78           ./84-9-S00.xlsx
4 2          LL-3       37.884999999999998 14.86              ./84-9-S00.xlsx
5 2          LL-2       32.731999999999999 14.372999999999999 ./84-9-S00.xlsx
6 2          LL-1       23.114999999999998 10.484             ./84-9-S00.xlsx

Output 2:

# A tibble: 35 x 5
   SectionCut OutputCase V2                 M3                 id             
   <chr>      <chr>      <chr>              <chr>              <chr>          
 1 1          LL-3       35.317999999999998 51208.425000000003 ./84-9-S00.xlsx
 2 1          LL-2       35.917999999999999 48494.190999999999 ./84-9-S00.xlsx
 3 1          LL-1       26.937999999999999 33676.78           ./84-9-S00.xlsx
 4 2          LL-3       37.884999999999998 14.86              ./84-9-S00.xlsx
 5 2          LL-2       32.731999999999999 14.372999999999999 ./84-9-S00.xlsx
 6 2          LL-1       23.114999999999998 10.484             ./84-9-S00.xlsx
 7 3          LL-3       38.220999999999997 57100.021000000001 ./84-9-S00.xlsx
 8 3          LL-2       37.587000000000003 53050.275999999998 ./84-9-S00.xlsx
 9 3          LL-1       24.864999999999998 36847.82           ./84-9-S00.xlsx
10 Left       LL-3       31.035             49882.42           ./84-9-S00.xlsx
# ... with 25 more rows

Read from a list of csv filenames into R

SOLUTION:

Fixes: 1) must use double-[[]] to get the individual list element 2) don't use assign()

So either:

for (i in 1:length(temp)) { temp[[i]] <- read.csv(temp[i]) }

or, if you don't want to overwrite the temp variable:

df = c(rep(data.frame(), length(temp))) # list of empty dataframe
for (i in 1:length(temp)) { df[[i]] <- as.list(read.csv(temp[i])) }

There were two separate mistakes in your original code:

1. using single [] instead of double [[]]. Single [] gives you a list slice containing one element (not what you want to assign to), instead of just that actual element.

2. assign is not doing what you think it's doing, as @G-Grothendieck said.

You simply want to do temp[[i]] <- read.csv(temp[i])

But what you're actually doing is assigning to the variable whose name is contained in temp[i]. So if temp[i] is 'whosyour.csv', you're actually creating and assigning to a variable with that name, rather than assigning to temp[i] itself:

whosyour.csv <- read.csv('whosyour.csv') # NOT WHAT YOU WANTED!

When importing CSV into R how to generate column with name of the CSV?

You have already done all the hard work. With a fairly small modification this should be straight-forward.

The logic is:

1. Create a small helper function that reads an individual csv and adds a column with the file name.
2. Call this helper function in llply()

The following should work:

read_csv_filename <- function(filename){
    ret <- read.csv(filename)
    ret$Source <- filename #EDIT
    ret
}

import.list <- ldply(filenames, read_csv_filename)

Note that I have proposed another small improvement to your code: read.csv() returns a data.frame - this means you can use ldply() rather than llply().

API call using R

I've had good luck with the downloader pkg. It's a light wrapper around the base R downloading functions, Just replace the parts of those URL that will change. Doesn't look like you are using the ZIP:

library(downloader)
for( i in 1:3){ 
  download.file( paste0("https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVinValues/",
                        VIN[i],"?format=csv&modelyear=",myear4[i]), 
                destfile=paste0("test_", i ,".csv"))}
#-------------------
trying URL 'https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVinValues/5XYPKDA55KG446393?format=csv&modelyear=2019'
Content type 'application/octet-stream' length 2352 bytes
==================================================
downloaded 2352 bytes

trying URL 'https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVinValues/5XYPHDA56KG443792?format=csv&modelyear=2019'
Content type 'application/octet-stream' length 2351 bytes
==================================================
downloaded 2351 bytes

trying URL 'https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVinValues/5XYPGDA33KG432573?format=csv&modelyear=2019'
Content type 'application/octet-stream' length 2349 bytes
==================================================
downloaded 2349 bytes

I'm not sure if the files would be guaranteed to have all the same columns but if they were you could read them into R with read.csv and `do.call(rbind, ...) them together. There are lots of asked and answered questions about that part and multi-part questions are discouraged on SO, so I'm not thinking I should continue.

---

Related Topics