I have a table and an example is given below.
tab <- matrix(c(seq(0, 130, by = 10), sample(100, size = 14)), ncol=2)
tab[1, 1] <- 1
colnames(tab) <- c("A", "B")
I need to expand the table so i have a full sequence of values from 1:140 in A and the values of B fill out to match A.
So i would then have 1:9 in A and whatever value in the original table ([1, 2]) filling out those spaces. Then from 10:19 in A and whatever value in [2, 2] filling out B.
I hope I have explained myself. I can only think of doing it manually in a loop but not too sure how to code this.
Any help much appriciated.
CodePudding user response：
If you mean interpolate B on A's expansion, then we can use approx for that:
approximated <- approx(tab[,1], tab[,2], xout = seq(min(tab[,1]), max(tab[,1])))
approximated
# $x
# [1] 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
# [30] 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
# [59] 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87
# [88] 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116
# [117] 117 118 119 120 121 122 123 124 125 126 127 128 129 130
# $y
# [1] 49.0 50.8 52.6 54.3 56.1 57.9 59.7 61.4 63.2 65.0 61.0 57.0 53.0 49.0 45.0 41.0 37.0 33.0 29.0
# [20] 25.0 29.9 34.8 39.7 44.6 49.5 54.4 59.3 64.2 69.1 74.0 68.4 62.8 57.2 51.6 46.0 40.4 34.8 29.2
# [39] 23.6 18.0 26.2 34.4 42.6 50.8 59.0 67.2 75.4 83.6 91.8 100.0 94.7 89.4 84.1 78.8 73.5 68.2 62.9
# [58] 57.6 52.3 47.0 44.7 42.4 40.1 37.8 35.5 33.2 30.9 28.6 26.3 24.0 28.7 33.4 38.1 42.8 47.5 52.2
# [77] 56.9 61.6 66.3 71.0 72.8 74.6 76.4 78.2 80.0 81.8 83.6 85.4 87.2 89.0 83.8 78.6 73.4 68.2 63.0
# [96] 57.8 52.6 47.4 42.2 37.0 35.3 33.6 31.9 30.2 28.5 26.8 25.1 23.4 21.7 20.0 20.6 21.2 21.8 22.4
# [115] 23.0 23.6 24.2 24.8 25.4 26.0 23.7 21.4 19.1 16.8 14.5 12.2 9.9 7.6 5.3 3.0
This can be turned into a matrix fairly easily,
out <- cbind(A = approximated$x, B = approximated$y)
head(out,3); tail(out,3)
# A B
# [1,] 1 49.0
# [2,] 2 50.8
# [3,] 3 52.6
# A B
# [128,] 128 7.6
# [129,] 129 5.3
# [130,] 130 3.0
If instead you mean a step-function in a way, then add the method="constant" instead:
approximated <- approx(tab[,1], tab[,2], xout = seq(min(tab[,1]), max(tab[,1])),
method="constant")
out <- cbind(A = approximated$x, B = approximated$y)
head(out,11); tail(out,11)
# A B
# [1,] 1 49
# [2,] 2 49
# [3,] 3 49
# [4,] 4 49
# [5,] 5 49
# [6,] 6 49
# [7,] 7 49
# [8,] 8 49
# [9,] 9 49
# [10,] 10 65
# [11,] 11 65
# A B
# [120,] 120 26
# [121,] 121 26
# [122,] 122 26
# [123,] 123 26
# [124,] 124 26
# [125,] 125 26
# [126,] 126 26
# [127,] 127 26
# [128,] 128 26
# [129,] 129 26
# [130,] 130 3
FYI, I use seq(min(tab[,1]), max(tab[,1])) in order to do this programmatically, not assuming that A ranges from 1 to its max. This can be simplified a little if that's the intent and design.