add matching column from one csv file to another and multiply between columns

Question

I have two separate csv files 1. 15 columns and 2500 rows 2. 3 columns and 150 rows. Both of these tables have one common column name. I want to add the columns of csv file 1 to csv 2 but only matching record based on common column names and values and then multiply each column of csv 1 file with csv 2 column and write a separate csv file. The data and result looks like:

CSV 1:

Wavelength     Spec1      Spec2     Spec3     Spec4

461            0.031162   0.02928   0.025156  0.028369
462            0.031452   0.028441  0.024456  0.028587
463            0.03145    0.02857   0.024519  0.028295
464            0.031333   0.029298  0.025012  0.02806
465            0.031426   0.02948   0.024909  0.02872
466            0.031602   0.029342  0.025329  0.028861
467            0.031521   0.029108  0.025481  0.028738
468            0.031123   0.029013  0.024849  0.028731
469            0.031609   0.029285  0.025036  0.028567
470            0.031695   0.029543  0.025357  0.028627
471            0.031134   0.029535  0.025374  0.028931
472            0.031587   0.029106  0.02503   0.028847

CSV 2:

Wavelength   SpecR    Stdv
462          0.883542 0.010013
463          0.877453 0.011648
464          0.881011 0.011571
465          0.874721 0.009472
466          0.879688 0.006468
467          0.886569 0.004118
468          0.891913 0.003214
469          0.88768  0.002561

Output 1

Wavelength  SpecR    Stdv     Spec1    Spec2    Spec3    Spec4
462         0.883542 0.010013 0.031452 0.028441 0.024456 0.028587
463         0.877453 0.011648 0.03145  0.02857  0.024519 0.028295
464         0.881011 0.011571 0.031333 0.029298 0.025012 0.02806
465         0.874721 0.009472 0.031426 0.02948  0.024909 0.02872
466         0.879688 0.006468 0.031602 0.029342 0.025329 0.028861
467         0.886569 0.004118 0.031521 0.029108 0.025481 0.028738
468         0.891913 0.003214 0.031123 0.029013 0.024849 0.028731
469         0.88768  0.002561 0.031609 0.029285 0.025036 0.028567

Output 2:

Wavelength SpecR    Spec1    Spec2    Spec3    Spec4
462        0.883542 0.02779  0.025129 0.021608 0.025258
463        0.877453 0.027596 0.025069 0.021515 0.024828
464        0.881011 0.027605 0.025812 0.022036 0.024721
465        0.874721 0.027489 0.025787 0.021788 0.025122
466        0.879688 0.0278   0.025811 0.022282 0.025388
467        0.886569 0.027946 0.025806 0.022591 0.025478
468        0.891913 0.027759 0.025877 0.022163 0.025625
469        0.88768  0.028058 0.025995 0.022224 0.025358

I can combine the csv tables but I don’t know how to append the data I needed. Thanks

csv1 <- read.table('spectral1.csv', header=TRUE,sep=',')
csv2 <- read.table('spectral2.csv', header=TRUE,sep=',')

What do you mean by "`then multiply each column of csv 1 file with csv 2 column and write a separate csv file`" ? — Tim Biegeleisen, Apr 16 '15 at 04:35
@Juvin Please review comment and answers and let us know how else we can help you. — Tim Biegeleisen, Apr 16 '15 at 04:55

score 2 · Answer 1 · edited May 23 '17 at 11:58

To join your csv1 data frame to csv2 the way you want it to be in output1 you can use merge() ... se more on joins in this answer.

output1 <- merge(csv2, csv1, by = "Wavelength", all.x = TRUE) 

#   Wavelength    SpecR     Stdv    Spec1    Spec2    Spec3    Spec4
# 1        462 0.883542 0.010013 0.031452 0.028441 0.024456 0.028587
# 2        463 0.877453 0.011648 0.031450 0.028570 0.024519 0.028295
# 3        464 0.881011 0.011571 0.031333 0.029298 0.025012 0.028060
# 4        465 0.874721 0.009472 0.031426 0.029480 0.024909 0.028720
# 5        466 0.879688 0.006468 0.031602 0.029342 0.025329 0.028861
# 6        467 0.886569 0.004118 0.031521 0.029108 0.025481 0.028738
# 7        468 0.891913 0.003214 0.031123 0.029013 0.024849 0.028731
# 8        469 0.887680 0.002561 0.031609 0.029285 0.025036 0.028567

And to multiply columns with a particular column ... lets say with SpecR to get your output2 data frame, you could use transform() and then exclude the third column, if you need to:

output2 <- transform(output1, 
                     Spec1 = Spec1 * SpecR,
                     Spec2 = Spec2 * SpecR,
                     Spec3 = Spec3 * SpecR,
                     Spec4 = Spec4 * SpecR )[,-3] 

#   Wavelength    SpecR      Spec1      Spec2      Spec3      Spec4
# 1        462 0.883542 0.02778916 0.02512882 0.02160790 0.02525782
# 2        463 0.877453 0.02759590 0.02506883 0.02151427 0.02482753
# 3        464 0.881011 0.02760472 0.02581186 0.02203585 0.02472117
# 4        465 0.874721 0.02748898 0.02578678 0.02178843 0.02512199
# 5        466 0.879688 0.02779990 0.02581181 0.02228162 0.02538868
# 6        467 0.886569 0.02794554 0.02580625 0.02259066 0.02547822
# 7        468 0.891913 0.02775901 0.02587707 0.02216315 0.02562555
# 8        469 0.887680 0.02805868 0.02599571 0.02222396 0.02535835

but if your number of columns varies you can do something in this direction, to include all columns after the third one:

output2 <- cbind(output1$Wavelengt, output1$SpecR, output1[, -c(1:3)] * output1$SpecR)

or even simpler to directly do it on output1:

output1[, -c(1:3)] <- (output1[, -c(1:3)] * output1$SpecR)

and then again exclude the Stdv column, if you need to. Which will give the same result as the transform above.

add matching column from one csv file to another and multiply between columns

1 Answers1