Multple csv files reading in a loop and calculating column wise average in R

Question

I have 3 csv files, I have three columns in all the three files( Maths, Physics and Chemistry) and marks of all the students. I created a loop to read all the files and saved in a dataframe as follows. In every file line numbers 1,2,4,5 need to be skipped.

files <- list.files(pattern = ".csv") 

for(i in 1:length(files)){
  data <- read.csv(files[i], header=F, skip=2) # by writing skip=2 I could only skip first two lines. 
  View(data)
  mathavg[i] <- sum(as.numeric(data$math), na.rm=T)/nrow(data)
}

result <- cbind(files,mathavg)
write.csv(result,"result_mathavg.csv")

I could not able to calculate the average of math column in all the three files.

Like this I need to calculate for all the three subjects across three files. any help????

Do **lapply()** over the filenames. – jogo Jan 04 '16 at 13:50 — jogo, Jan 04 '16 at 13:50
Tried every possible way. Could you please elaborate? – Kalyan Ramanuja Jan 04 '16 at 13:57 — Kalyan Ramanuja, Jan 04 '16 at 13:57

DarrenRhodes · Answer 1 · 2016-01-04T22:20:27.023

1

This should work,

files  <- c("testa.csv","testb.csv","testc.csv")
list_files  <- lapply(files,read.csv,header=F,stringsAsFactors=F)

list_files  <- lapply(list_files, function(x) x[-c(1,2,4,5),])

mathav  <- sapply(list_files,function(x) mean(as.numeric(x[,2]),na.rm=T))
result  <- cbind(files,mathav)
write.csv(result,"result_mathavg.csv",row.names=F)

I didn't have access to your files, so I made up three and called them 'files'. I used the lapply function to load the files, then to remove the lines that you didn't want. I got the average using the sapply function then I went back to your code to get result, etc.

edited Jan 04 '16 at 22:20

answered Jan 04 '16 at 15:24

DarrenRhodes

1,411
2
14
28

For math thats okay. If I want to calculate the physics column average and chemistry column avg phyav – Kalyan Ramanuja Jan 04 '16 at 20:22
I'd need to see your csv file to comment. – DarrenRhodes Jan 04 '16 at 20:28
Student Name Math Physic Chemistry Term 1 Term 1 Term 1 Score (125) Score (125) Score (125) Abhinav.S 107.75 117.25 95.5 Abhishek.C 112.5 88.75 91 Aditya Gowrishankar 117 116.5 106 Akshara Ashok 121 111.25 111 Arsheyaa Prasanna 110.75 91.25 78.25 Arya.B 117 123.75 125 Ayaan.S.Ahammad 121 123.75 121.5 Daksha Swaminathan 109 111.75 106 Debotri Banerjee 120 118.75 NT Diya.G 106.75 83.5 87.5 – Kalyan Ramanuja Jan 04 '16 at 20:32
You can see the csv sample above – Kalyan Ramanuja Jan 04 '16 at 20:33
Post me your email id, Will connect there. If you're fine. – Kalyan Ramanuja Jan 04 '16 at 20:35
Try the code again, it looks like I made a typographical error (I wrote rm.na rather than na.rm). – DarrenRhodes Jan 04 '16 at 22:23
Changed the rm.na to na.rm , that's not the problem. cause would be the column number . Its working only for column2 not for any other column – Kalyan Ramanuja Jan 05 '16 at 06:19
Hi, where you have mathav – DarrenRhodes Jan 05 '16 at 08:41
did the same. all the values are displaying as NA. I mentioned na.rm=T even then the same problem – Kalyan Ramanuja Jan 05 '16 at 08:52
what does list_files[,3] return? What does str(list_files) return? – DarrenRhodes Jan 05 '16 at 08:55
list_files > phyav mathav > result – Kalyan Ramanuja Jan 05 '16 at 09:31
Error has been posted above – Kalyan Ramanuja Jan 05 '16 at 09:32
what do you get if you type str(list_files)? – DarrenRhodes Jan 05 '16 at 09:47
I could see the list of contents in all the three files – Kalyan Ramanuja Jan 05 '16 at 09:53
When I add the remaining columns its showing object not found and even for the math its showing same value for all the three files in the output. – Kalyan Ramanuja Jan 05 '16 at 13:17

score 0 · Answer 2 · answered Jan 04 '16 at 15:31

0

mathavg needs to be initialized before it can be operated on with []. To remove lines 4 and 5 you just need to perform a subsetting operation after reading the data. lines 4 and 5 become 2 and 3 if you skip the first 2 lines when reading the data.

files <- list.files(pattern = ".csv") 
mathavg<-''
for(i in 1:length(files)){
  data <- read.csv(files[i], header=F, skip=2, stringsAsFactors=F) # by writing skip=2 I could only skip first two lines. 
  data<-data[-c(2,3),] 
  mathavg[i] <- mean(as.numeric(data$math), rm.NA=T) ##best to use R's builtin function to calculate the mean
}

result <- cbind(files,mathavg)
write.csv(result,"result_mathavg.csv")

answered Jan 04 '16 at 15:31

emilliman5

5,538
3
24
36

Bear in mind that R users encourage each other to use apply functions above for loops, http://stackoverflow.com/questions/2275896/is-rs-apply-family-more-than-syntactic-sugar – DarrenRhodes Jan 04 '16 at 15:48
@emilliman5: The above code after execution showing all the values as NAN – Kalyan Ramanuja Jan 04 '16 at 20:40
According the snippet of data you posted above `data$math` needs to be `data$Math`. But without seeing the actual data I cannot troubleshoot any further. – emilliman5 Jan 04 '16 at 20:51

Multple csv files reading in a loop and calculating column wise average in R

2 Answers2