[R] calculate within-day correlations
emorway
emorway at usgs.gov
Fri Sep 14 02:35:38 CEST 2012
useRs,
Here is some R-ready data for my question to follow. Of course this data is
small snippet from a much larger dataset that is about a decade long.
Q<-read.table(textConnection("2002 3 28 15 77.38815
2002 3 28 30 77.09505
2002 3 28 45 76.80196
2002 3 28 60 76.50887
2002 3 28 75 76.50887
2002 3 28 90 76.50887
2002 3 28 105 76.50887
2002 3 28 120 76.50887
2002 3 28 135 76.80196
2002 3 28 150 77.09506
2002 3 28 165 77.38815
2002 3 28 180 77.68125
2002 3 28 195 77.68125
2002 3 28 210 77.68125
2002 3 28 225 77.68125
2002 3 28 240 77.68125
2002 3 28 255 77.38815
2002 3 28 270 77.09505
2002 3 28 285 76.80196
2002 3 28 300 76.50887
2002 3 28 315 76.80196
2002 3 28 330 77.09506
2002 3 28 345 77.38815
2002 3 28 360 77.68125
2002 3 28 375 77.68125
2002 3 28 390 77.68125
2002 3 28 405 77.68125
2002 3 28 420 77.68125
2002 3 28 435 77.68125
2002 3 28 450 77.68125
2002 3 28 465 77.68125
2002 3 28 480 77.68125
2002 3 28 495 77.97691
2002 3 28 510 78.27258
2002 3 28 525 78.56824
2002 3 28 540 78.86389
2002 3 28 555 78.86389
2002 3 28 570 78.86389
2002 3 28 585 78.86389
2002 3 28 600 78.86389
2002 3 28 615 78.86389
2002 3 28 630 78.86389
2002 3 28 645 78.86389
2002 3 28 660 78.86389
2002 3 28 675 78.86389
2002 3 28 690 78.86389
2002 3 28 705 78.86389
2002 3 28 720 78.86389
2002 3 28 735 79.16212
2002 3 28 750 79.46034
2002 3 28 765 79.75856
2002 3 28 780 80.05679
2002 3 28 795 79.75856
2002 3 28 810 79.46033
2002 3 28 825 79.16211
2002 3 28 840 78.86389
2002 3 28 855 78.56823
2002 3 28 870 78.27257
2002 3 28 885 77.97691
2002 3 28 900 77.68125
2002 3 28 915 77.68125
2002 3 28 930 77.68125
2002 3 28 945 77.68125
2002 3 28 960 77.68125
2002 3 28 975 77.68125
2002 3 28 990 77.29046
2002 3 28 1005 76.89966
2002 3 28 1020 76.50887
2002 3 28 1035 75.93033
2002 3 28 1050 75.35179
2002 3 28 1065 74.77325
2002 3 28 1080 74.19473
2002 3 28 1095 73.90929
2002 3 28 1110 73.62385
2002 3 28 1125 73.33841
2002 3 28 1140 73.05298
2002 3 28 1155 73.33842
2002 3 28 1170 73.62386
2002 3 28 1185 73.90929
2002 3 28 1200 74.19473
2002 3 28 1215 74.19473
2002 3 28 1230 74.19473
2002 3 28 1245 74.19473
2002 3 28 1260 74.19473
2002 3 28 1275 74.48272
2002 3 28 1290 74.77071
2002 3 28 1305 75.05871
2002 3 28 1320 75.3467
2002 3 28 1335 75.63725
2002 3 28 1350 75.92779
2002 3 28 1365 76.21832
2002 3 28 1380 76.50887
2002 3 28 1395 76.80196
2002 3 28 1410 77.09506
2002 3 28 1425 77.38815
2002 3 28 1440 77.68125
2002 3 29 15 77.38815
2002 3 29 30 77.09505
2002 3 29 45 76.80196
2002 3 29 60 76.50887
2002 3 29 75 76.21832
2002 3 29 90 75.92778
2002 3 29 105 75.63724
2002 3 29 120 75.3467
2002 3 29 135 75.63725
2002 3 29 150 75.92779
2002 3 29 165 76.21832
2002 3 29 180 76.50887
2002 3 29 195 76.21832
2002 3 29 210 75.92778
2002 3 29 225 75.63724
2002 3 29 240 75.3467
2002 3 29 255 75.3467
2002 3 29 270 75.3467
2002 3 29 285 75.3467
2002 3 29 300 75.3467
2002 3 29 315 75.3467
2002 3 29 330 75.3467
2002 3 29 345 75.3467
2002 3 29 360 75.3467
2002 3 29 375 75.63725
2002 3 29 390 75.92779
2002 3 29 405 76.21832
2002 3 29 420 76.50887
2002 3 29 435 76.50887
2002 3 29 450 76.50887
2002 3 29 465 76.50887
2002 3 29 480 76.50887
2002 3 29 495 76.80196
2002 3 29 510 77.09506
2002 3 29 525 77.38815
2002 3 29 540 77.68125
2002 3 29 555 77.97691
2002 3 29 570 78.27258
2002 3 29 585 78.56824
2002 3 29 600 78.86389
2002 3 29 615 79.16212
2002 3 29 630 79.46034
2002 3 29 645 79.75856
2002 3 29 660 80.05679
2002 3 29 675 80.05679
2002 3 29 690 80.05679
2002 3 29 705 80.05679
2002 3 29 720 80.05679
2002 3 29 735 80.35759
2002 3 29 750 80.65839
2002 3 29 765 80.9592
2002 3 29 780 81.26001
2002 3 29 795 81.26001
2002 3 29 810 81.26001
2002 3 29 825 81.26001
2002 3 29 840 81.26001
2002 3 29 855 81.26001
2002 3 29 870 81.26001
2002 3 29 885 81.26001
2002 3 29 900 81.26001
2002 3 29 915 80.95921
2002 3 29 930 80.6584
2002 3 29 945 80.35759
2002 3 29 960 80.05679
2002 3 29 975 80.35759
2002 3 29 990 80.65839
2002 3 29 1005 80.9592
2002 3 29 1020 81.26001
2002 3 29 1035 81.26001
2002 3 29 1050 81.26001
2002 3 29 1065 81.26001
2002 3 29 1080 81.26001
2002 3 29 1095 81.56339
2002 3 29 1110 81.86678
2002 3 29 1125 82.17016
2002 3 29 1140 82.47354
2002 3 29 1155 82.47354
2002 3 29 1170 82.47354
2002 3 29 1185 82.47354
2002 3 29 1200 82.47354
2002 3 29 1215 82.47354
2002 3 29 1230 82.47354
2002 3 29 1245 82.47354
2002 3 29 1260 82.47354
2002 3 29 1275 82.47354
2002 3 29 1290 82.47354
2002 3 29 1305 82.47354
2002 3 29 1320 82.47354
2002 3 29 1335 82.47354
2002 3 29 1350 82.47354
2002 3 29 1365 82.47354
2002 3 29 1380 82.47354
2002 3 29 1395 82.47354
2002 3 29 1410 82.47354
2002 3 29 1425 82.47354
2002 3 29 1440 82.47354
2002 3 30 15 82.47354
2002 3 30 30 82.47354
2002 3 30 45 82.47354
2002 3 30 60 82.47354
2002 3 30 75 82.77951
2002 3 30 90 83.08547
2002 3 30 105 83.39143
2002 3 30 120 83.6974
2002 3 30 135 84.00597
2002 3 30 150 84.31453
2002 3 30 165 84.62309
2002 3 30 180 84.93166
2002 3 30 195 84.93166
2002 3 30 210 84.93166
2002 3 30 225 84.93166
2002 3 30 240 84.93166
2002 3 30 255 84.93166
2002 3 30 270 84.93166
2002 3 30 285 84.93166
2002 3 30 300 84.93166
2002 3 30 315 84.93166
2002 3 30 330 84.93166
2002 3 30 345 84.93166
2002 3 30 360 84.93166
2002 3 30 375 84.93166
2002 3 30 390 84.93166
2002 3 30 405 84.93166
2002 3 30 420 84.93166
2002 3 30 435 84.93166
2002 3 30 450 84.93166
2002 3 30 465 84.93166
2002 3 30 480 84.93166
2002 3 30 495 84.93166
2002 3 30 510 84.93166
2002 3 30 525 84.93166
2002 3 30 540 84.93166
2002 3 30 555 85.24281
2002 3 30 570 85.55397
2002 3 30 585 85.86513
2002 3 30 600 86.17629
2002 3 30 615 85.86514
2002 3 30 630 85.55398
2002 3 30 645 85.24281
2002 3 30 660 84.93166
2002 3 30 675 84.93166
2002 3 30 690 84.93166
2002 3 30 705 84.93166
2002 3 30 720 84.93166
2002 3 30 735 84.62309
2002 3 30 750 84.31453
2002 3 30 765 84.00597
2002 3 30 780 83.6974
2002 3 30 795 83.39143
2002 3 30 810 83.08547
2002 3 30 825 82.77951
2002 3 30 840 82.47354
2002 3 30 855 82.17016
2002 3 30 870 81.86678
2002 3 30 885 81.56339
2002 3 30 900 81.26001
2002 3 30 915 81.26001
2002 3 30 930 81.26001
2002 3 30 945 81.26001
2002 3 30 960 81.26001
2002 3 30 975 81.26001
2002 3 30 990 81.26001
2002 3 30 1005 81.26001
2002 3 30 1020 81.26001
2002 3 30 1035 81.26001
2002 3 30 1050 81.26001
2002 3 30 1065 81.26001
2002 3 30 1080 81.26001
2002 3 30 1095 80.95921
2002 3 30 1110 80.6584
2002 3 30 1125 80.35759
2002 3 30 1140 80.05679
2002 3 30 1155 79.75856
2002 3 30 1170 79.46033
2002 3 30 1185 79.16211
2002 3 30 1200 78.86389
2002 3 30 1215 78.86389
2002 3 30 1230 78.86389
2002 3 30 1245 78.86389
2002 3 30 1260 78.86389
2002 3 30 1275 78.56823
2002 3 30 1290 78.27257
2002 3 30 1305 77.97691
2002 3 30 1320 77.68125
2002 3 30 1335 77.09762
2002 3 30 1350 76.51398
2002 3 30 1365 75.93034
2002 3 30 1380 75.3467
2002 3 30 1395 75.05871
2002 3 30 1410 74.77071
2002 3 30 1425 74.48272
2002 3 30 1440
74.19473"),header=F,col.names=c('yr','mn','dy','min','Q'))
SC<-read.table(textConnection("2002 3 28 15 4480
2002 3 28 30 4480
2002 3 28 45 4480
2002 3 28 60 4480
2002 3 28 75 4480
2002 3 28 90 4480
2002 3 28 105 4480
2002 3 28 120 4480
2002 3 28 135 4482.5
2002 3 28 150 4485
2002 3 28 165 4487.5
2002 3 28 180 4490
2002 3 28 195 4490
2002 3 28 210 4490
2002 3 28 225 4490
2002 3 28 240 4490
2002 3 28 255 4487.5
2002 3 28 270 4485
2002 3 28 285 4482.5
2002 3 28 300 4480
2002 3 28 315 4480
2002 3 28 330 4480
2002 3 28 345 4480
2002 3 28 360 4480
2002 3 28 375 4480
2002 3 28 390 4480
2002 3 28 405 4480
2002 3 28 420 4480
2002 3 28 435 4482.5
2002 3 28 450 4485
2002 3 28 465 4487.5
2002 3 28 480 4490
2002 3 28 495 4492.5
2002 3 28 510 4495
2002 3 28 525 4497.5
2002 3 28 540 4500
2002 3 28 555 4502.5
2002 3 28 570 4505
2002 3 28 585 4507.5
2002 3 28 600 4510
2002 3 28 615 4510
2002 3 28 630 4510
2002 3 28 645 4510
2002 3 28 660 4510
2002 3 28 675 4510
2002 3 28 690 4510
2002 3 28 705 4510
2002 3 28 720 4510
2002 3 28 735 4510
2002 3 28 750 4510
2002 3 28 765 4510
2002 3 28 780 4510
2002 3 28 795 4512.5
2002 3 28 810 4515
2002 3 28 825 4517.5
2002 3 28 840 4520
2002 3 28 855 4520
2002 3 28 870 4520
2002 3 28 885 4520
2002 3 28 900 4520
2002 3 28 915 4522.5
2002 3 28 930 4525
2002 3 28 945 4527.5
2002 3 28 960 4530
2002 3 28 975 4532.5
2002 3 28 990 4535
2002 3 28 1005 4537.5
2002 3 28 1020 4540
2002 3 28 1035 4537.5
2002 3 28 1050 4535
2002 3 28 1065 4532.5
2002 3 28 1080 4530
2002 3 28 1095 4530
2002 3 28 1110 4530
2002 3 28 1125 4530
2002 3 28 1140 4530
2002 3 28 1155 4535
2002 3 28 1170 4540
2002 3 28 1185 4545
2002 3 28 1200 4550
2002 3 28 1215 4550
2002 3 28 1230 4550
2002 3 28 1245 4550
2002 3 28 1260 4550
2002 3 28 1275 4550
2002 3 28 1290 4550
2002 3 28 1305 4550
2002 3 28 1320 4550
2002 3 28 1335 4542.5
2002 3 28 1350 4535
2002 3 28 1365 4527.5
2002 3 28 1380 4520
2002 3 28 1395 4520
2002 3 28 1410 4520
2002 3 28 1425 4520
2002 3 28 1440 4520
2002 3 29 15 4520
2002 3 29 30 4520
2002 3 29 45 4520
2002 3 29 60 4520
2002 3 29 75 4520
2002 3 29 90 4520
2002 3 29 105 4520
2002 3 29 120 4520
2002 3 29 135 4520
2002 3 29 150 4520
2002 3 29 165 4520
2002 3 29 180 4520
2002 3 29 195 4522.5
2002 3 29 210 4525
2002 3 29 225 4527.5
2002 3 29 240 4530
2002 3 29 255 4530
2002 3 29 270 4530
2002 3 29 285 4530
2002 3 29 300 4530
2002 3 29 315 4532.5
2002 3 29 330 4535
2002 3 29 345 4537.5
2002 3 29 360 4540
2002 3 29 375 4540
2002 3 29 390 4540
2002 3 29 405 4540
2002 3 29 420 4540
2002 3 29 435 4542.5
2002 3 29 450 4545
2002 3 29 465 4547.5
2002 3 29 480 4550
2002 3 29 495 4552.5
2002 3 29 510 4555
2002 3 29 525 4557.5
2002 3 29 540 4560
2002 3 29 555 4560
2002 3 29 570 4560
2002 3 29 585 4560
2002 3 29 600 4560
2002 3 29 615 4557.5
2002 3 29 630 4555
2002 3 29 645 4552.5
2002 3 29 660 4550
2002 3 29 675 4547.5
2002 3 29 690 4545
2002 3 29 705 4542.5
2002 3 29 720 4540
2002 3 29 735 4540
2002 3 29 750 4540
2002 3 29 765 4540
2002 3 29 780 4540
2002 3 29 795 4537.5
2002 3 29 810 4535
2002 3 29 825 4532.5
2002 3 29 840 4530
2002 3 29 855 4530
2002 3 29 870 4530
2002 3 29 885 4530
2002 3 29 900 4530
2002 3 29 915 4535
2002 3 29 930 4540
2002 3 29 945 4545
2002 3 29 960 4550
2002 3 29 975 4537.5
2002 3 29 990 4525
2002 3 29 1005 4512.5
2002 3 29 1020 4500
2002 3 29 1035 4490
2002 3 29 1050 4480
2002 3 29 1065 4470
2002 3 29 1080 4460
2002 3 29 1095 4470
2002 3 29 1110 4480
2002 3 29 1125 4490
2002 3 29 1140 4500
2002 3 29 1155 4490
2002 3 29 1170 4480
2002 3 29 1185 4470
2002 3 29 1200 4460
2002 3 29 1215 4460
2002 3 29 1230 4460
2002 3 29 1245 4460
2002 3 29 1260 4460
2002 3 29 1275 4455
2002 3 29 1290 4450
2002 3 29 1305 4445
2002 3 29 1320 4440
2002 3 29 1335 4437.5
2002 3 29 1350 4435
2002 3 29 1365 4432.5
2002 3 29 1380 4430
2002 3 29 1395 4427.5
2002 3 29 1410 4425
2002 3 29 1425 4422.5
2002 3 29 1440 4420
2002 3 30 15 4420
2002 3 30 30 4420
2002 3 30 45 4420
2002 3 30 60 4420
2002 3 30 75 4420
2002 3 30 90 4420
2002 3 30 105 4420
2002 3 30 120 4420
2002 3 30 135 4417.5
2002 3 30 150 4415
2002 3 30 165 4412.5
2002 3 30 180 4410
2002 3 30 195 4407.5
2002 3 30 210 4405
2002 3 30 225 4402.5
2002 3 30 240 4400
2002 3 30 255 4397.5
2002 3 30 270 4395
2002 3 30 285 4392.5
2002 3 30 300 4390
2002 3 30 315 4390
2002 3 30 330 4390
2002 3 30 345 4390
2002 3 30 360 4390
2002 3 30 375 4387.5
2002 3 30 390 4385
2002 3 30 405 4382.5
2002 3 30 420 4380
2002 3 30 435 4382.5
2002 3 30 450 4385
2002 3 30 465 4387.5
2002 3 30 480 4390
2002 3 30 495 4392.5
2002 3 30 510 4395
2002 3 30 525 4397.5
2002 3 30 540 4400
2002 3 30 555 4402.5
2002 3 30 570 4405
2002 3 30 585 4407.5
2002 3 30 600 4410
2002 3 30 615 4410
2002 3 30 630 4410
2002 3 30 645 4410
2002 3 30 660 4410
2002 3 30 675 4410
2002 3 30 690 4410
2002 3 30 705 4410
2002 3 30 720 4410
2002 3 30 735 4412.5
2002 3 30 750 4415
2002 3 30 765 4417.5
2002 3 30 780 4420
2002 3 30 795 4417.5
2002 3 30 810 4415
2002 3 30 825 4412.5
2002 3 30 840 4410
2002 3 30 855 4412.5
2002 3 30 870 4415
2002 3 30 885 4417.5
2002 3 30 900 4420
2002 3 30 915 4417.5
2002 3 30 930 4415
2002 3 30 945 4412.5
2002 3 30 960 4410
2002 3 30 975 4400
2002 3 30 990 4390
2002 3 30 1005 4380
2002 3 30 1020 4370
2002 3 30 1035 4362.5
2002 3 30 1050 4355
2002 3 30 1065 4347.5
2002 3 30 1080 4340
2002 3 30 1095 4355
2002 3 30 1110 4370
2002 3 30 1125 4385
2002 3 30 1140 4400
2002 3 30 1155 4400
2002 3 30 1170 4400
2002 3 30 1185 4400
2002 3 30 1200 4400
2002 3 30 1215 4400
2002 3 30 1230 4400
2002 3 30 1245 NA
2002 3 30 1260 4400
2002 3 30 1275 4402.5
2002 3 30 1290 4405
2002 3 30 1305 4407.5
2002 3 30 1320 4410
2002 3 30 1335 4412.5
2002 3 30 1350 4415
2002 3 30 1365 4417.5
2002 3 30 1380 4420
2002 3 30 1395 4425
2002 3 30 1410 4430
2002 3 30 1425 4435
2002 3 30 1440
4440"),header=F,col.names=c('yr','mn','dy','min','SC'))
Q_use<-data.frame(date=as.POSIXct(paste(Q[,1],"-",Q[,2],"-",Q[,3],"
",floor(Q[,4]/60),":",Q[,4]-(floor(Q[,4]/60)*60),":00",sep=''),"%Y-%m-%d
%H:%M:%S",tz=""),Q=Q$Q)
SC_use<-data.frame(date=as.POSIXct(paste(SC[,1],"-",SC[,2],"-",SC[,3],"
",floor(SC[,4]/60),":",SC[,4]-(floor(SC[,4]/60)*60),":00",sep=''),"%Y-%m-%d
%H:%M:%S",tz=""),SC=SC$SC)
Using the data provided, I’m trying to calculate each day’s correlation
between Q_use$Q and SC_use$SC and store the values in a data.frame. An
example result I’d like to make is
#Day 1
cor(Q_use$Q[1:95],SC_use$SC[1:95])
#[1] -0.4916499
#Day 2
cor(Q_use$Q[96:191],SC_use$SC[96:191])
#[1] -0.6085098
edm<-data.frame(Correl=t(t(c(cor(Q_use$Q[1:95],SC_use$SC[1:95]),
cor(Q_use$Q[96:191],SC_use$SC[96:191])))))
But of course I want R to figure out appropriate indexes (i.e. 1:95, 96:191,
and so in the larger dataset) for me. In other words, I'm seeking some help
with R code that will ‘pass’ through the two datasets calculating each day’s
correlation and doesn’t rely on the user supplying the ranges of indexes for
way the daily values reside.
There are, as there always is, a couple of wrinkles. On day 3, for example,
cor(Q_use$Q[192:287],SC_use$SC[192:287])
[1] NA
This is because SC_use$SC[275] = NA. Is there a way to direct R to continue
calculating that day's correlation using the data that is available for that
day? It is also necessary to check and make sure that
Q_use[i,1]==SC_use[i,1] for each i in that day because in the larger dataset
the row indices don’t necessarily match up (I have made sure that they do
for this simple example). It would be handy to know how many values were
missing on incomplete days, perhaps in a column appended to the resulting
data frame. I appreciate any R code that could help get me started toward
this end, I’m stuck. I tried looking at ?aggregate, had a look in the
reshape library, and ‘rollapply’ in the zoo library, but I wasn’t seeing a
way to do the error checking I just described.
Thanks, Eric
--
View this message in context: http://r.789695.n4.nabble.com/calculate-within-day-correlations-tp4643091.html
Sent from the R help mailing list archive at Nabble.com.
More information about the R-help
mailing list