Balance panel data
balance_panel.RdTransforms an unbalanced panel dataset into a balanced one by either keeping only complete units or filling missing time points with a specified value.
Usage
balance_panel(data, unit_col = NULL, time_col = NULL,
fill = NA_real_, method = c("fill", "complete"),
verbose = FALSE)Arguments
- data
A data frame containing panel data.
- unit_col
Column index or name identifying the individual unit (e.g., station ID, subject ID). If
NULL, the first column is assumed.- time_col
Column index or name identifying the time point. If
NULL, the first column matchingdateis used.- fill
Value used to fill missing combinations when
method = "fill".- method
Balancing method:
"fill"adds rows for missing unit-time combinations and fills numeric non-key columns withfill; non-numeric columns of the added rows are left asNA."complete"keeps only units that have observations at every time point.- verbose
Logical; if
TRUE, prints progress message.
Details
The function assumes that the time column is sorted and contains no duplicates within a unit. If duplicates exist, they should be handled beforehand.
Examples
balance_panel(data[1:200, c(1, 4, 17:19)], unit_col = 2, time_col = 1, method = "complete")
#> date monthyear 3.98 4.47 5.01
#> 1 2019-12-31 16:00:00 January 2020 NA NA NA
#> 2 2019-12-31 16:10:00 January 2020 NA NA NA
#> 3 2019-12-31 16:20:00 January 2020 NA NA NA
#> 4 2019-12-31 16:30:00 January 2020 42.9722 74.7785 24.8375
#> 5 2019-12-31 16:40:00 January 2020 NA 6.8651 44.7176
#> 6 2019-12-31 16:50:00 January 2020 61.2601 NA NA
#> 7 2019-12-31 17:00:00 January 2020 115.8960 NA NA
#> 8 2019-12-31 17:10:00 January 2020 NA NA NA
#> 9 2019-12-31 17:20:00 January 2020 61.2601 NA NA
#> 10 2019-12-31 17:30:00 January 2020 60.9405 6.5912 42.9332
#> 11 2019-12-31 17:40:00 January 2020 NA NA NA
#> 12 2019-12-31 17:50:00 January 2020 NA NA NA
#> 13 2019-12-31 18:00:00 January 2020 NA 13.9498 90.8651
#> 14 2019-12-31 18:10:00 January 2020 NA NA NA
#> 15 2019-12-31 18:20:00 January 2020 61.2601 NA NA
#> 16 2019-12-31 18:30:00 January 2020 130.9630 227.8960 75.6951
#> 17 2019-12-31 18:40:00 January 2020 NA NA NA
#> 18 2019-12-31 18:50:00 January 2020 NA NA NA
#> 19 2019-12-31 19:00:00 January 2020 NA NA NA
#> 20 2019-12-31 19:10:00 January 2020 133.5230 148.2000 49.2241
#> 21 2019-12-31 19:20:00 January 2020 61.2601 NA NA
#> 22 2019-12-31 19:40:00 January 2020 NA NA NA
#> 23 2019-12-31 19:50:00 January 2020 NA NA NA
#> 24 2019-12-31 20:00:00 January 2020 NA NA NA
#> 25 2019-12-31 20:10:00 January 2020 NA NA NA
#> 26 2019-12-31 20:20:00 January 2020 42.8723 74.6047 24.7797
#> 27 2019-12-31 20:30:00 January 2020 42.9722 74.7785 24.8375
#> 28 2019-12-31 20:40:00 January 2020 NA NA NA
#> 29 2019-12-31 20:50:00 January 2020 NA 7.2200 47.0292
#> 30 2019-12-31 21:00:00 January 2020 NA NA NA
#> 31 2019-12-31 21:10:00 January 2020 96.5514 72.1565 23.9666
#> 32 2019-12-31 21:20:00 January 2020 39.7904 75.6110 64.4859
#> 33 2019-12-31 21:30:00 January 2020 96.7000 72.5003 24.0808
#> 34 2019-12-31 21:40:00 January 2020 NA NA NA
#> 35 2019-12-31 21:50:00 January 2020 NA NA NA
#> 36 2019-12-31 22:00:00 January 2020 NA NA NA
#> 37 2019-12-31 22:10:00 January 2020 NA NA NA
#> 38 2019-12-31 22:20:00 January 2020 45.0185 78.3394 26.0202
#> 39 2019-12-31 22:30:00 January 2020 61.2601 NA NA
#> 40 2019-12-31 22:40:00 January 2020 82.6597 150.5060 91.1891
#> 41 2019-12-31 22:50:00 January 2020 NA NA NA
#> 42 2019-12-31 23:00:00 January 2020 61.2601 NA NA
#> 43 2019-12-31 23:10:00 January 2020 NA NA NA
#> 44 2019-12-31 23:20:00 January 2020 57.7803 NA NA
#> 45 2019-12-31 23:30:00 January 2020 NA NA NA
#> 46 2019-12-31 23:40:00 January 2020 NA NA NA
#> 47 2019-12-31 23:50:00 January 2020 61.2601 NA NA
#> 48 2020-01-01 00:00:00 January 2020 NA NA NA
#> 49 2020-01-01 00:10:00 January 2020 NA NA NA
#> 50 2020-01-01 00:20:00 January 2020 NA NA NA
#> 51 2020-01-01 00:30:00 January 2020 NA NA NA
#> 52 2020-01-01 00:40:00 January 2020 35.9966 76.5024 111.1020
#> 53 2020-01-01 00:50:00 January 2020 NA NA NA
#> 54 2020-01-01 01:00:00 January 2020 NA NA NA
#> 55 2020-01-01 01:10:00 January 2020 61.2601 NA NA
#> 56 2020-01-01 01:20:00 January 2020 94.8684 76.2325 25.3204
#> 57 2020-01-01 01:30:00 January 2020 NA 6.6070 43.0364
#> 58 2020-01-01 01:40:00 January 2020 NA 6.9749 45.4325
#> 59 2020-01-01 01:50:00 January 2020 NA NA NA
#> 60 2020-01-01 02:20:00 January 2020 NA NA NA
#> 61 2020-01-01 02:30:00 January 2020 NA NA NA
#> 62 2020-01-01 02:40:00 January 2020 NA NA NA
#> 63 2020-01-01 02:50:00 January 2020 NA NA NA
#> 64 2020-01-01 03:00:00 January 2020 NA NA NA
#> 65 2020-01-01 03:10:00 January 2020 NA NA NA
#> 66 2020-01-01 03:20:00 January 2020 NA NA NA
#> 67 2020-01-01 03:30:00 January 2020 122.5200 NA NA
#> 68 2020-01-01 03:40:00 January 2020 NA NA NA
#> 69 2020-01-01 03:50:00 January 2020 NA NA NA
#> 70 2020-01-01 04:00:00 January 2020 NA NA NA
#> 71 2020-01-01 04:10:00 January 2020 NA NA NA
#> 72 2020-01-01 04:20:00 January 2020 129.7860 148.3430 49.2718
#> 73 2020-01-01 04:30:00 January 2020 NA NA NA
#> 74 2020-01-01 04:40:00 January 2020 NA NA NA
#> 75 2020-01-01 04:50:00 January 2020 NA NA NA
#> 76 2020-01-01 05:00:00 January 2020 NA NA NA
#> 77 2020-01-01 05:40:00 January 2020 NA NA NA
#> 78 2020-01-01 05:50:00 January 2020 NA NA NA
#> 79 2020-01-01 06:00:00 January 2020 NA NA NA
#> 80 2020-01-01 06:10:00 January 2020 NA NA NA
#> 81 2020-01-01 06:20:00 January 2020 NA 6.9749 45.4325
#> 82 2020-01-01 06:30:00 January 2020 NA NA NA
#> 83 2020-01-01 06:40:00 January 2020 NA 21.1830 137.9800
#> 84 2020-01-01 06:50:00 January 2020 NA 13.9498 90.8651
#> 85 2020-01-01 07:00:00 January 2020 NA NA NA
#> 86 2020-01-01 07:10:00 January 2020 NA NA NA
#> 87 2020-01-01 07:20:00 January 2020 122.5200 NA NA
#> 88 2020-01-01 07:30:00 January 2020 NA 7.2332 47.1152
#> 89 2020-01-01 07:40:00 January 2020 42.9722 74.7785 24.8375
#> 90 2020-01-01 07:50:00 January 2020 NA NA NA
#> 91 2020-01-01 08:00:00 January 2020 NA NA NA
#> 92 2020-01-01 08:10:00 January 2020 NA NA NA
#> 93 2020-01-01 08:20:00 January 2020 NA NA NA
#> 94 2020-01-01 08:30:00 January 2020 NA NA NA
#> 95 2020-01-01 08:40:00 January 2020 NA NA NA
#> 96 2020-01-01 08:50:00 January 2020 NA NA NA
#> 97 2020-01-01 09:00:00 January 2020 NA NA NA
#> 98 2020-01-01 09:10:00 January 2020 NA NA NA
#> 99 2020-01-01 09:20:00 January 2020 NA NA NA
#> 100 2020-01-01 09:30:00 January 2020 NA NA NA
#> 101 2020-01-01 09:40:00 January 2020 NA NA NA
#> 102 2020-01-01 09:50:00 January 2020 NA NA NA
#> 103 2020-01-01 10:00:00 January 2020 NA NA NA
#> 104 2020-01-01 10:10:00 January 2020 NA NA NA
#> 105 2020-01-01 10:20:00 January 2020 NA NA NA
#> 106 2020-01-01 10:30:00 January 2020 NA NA NA
#> 107 2020-01-01 10:40:00 January 2020 NA NA NA
#> 108 2020-01-01 10:50:00 January 2020 45.0185 78.3394 26.0202
#> 109 2020-01-01 11:00:00 January 2020 NA NA NA
#> 110 2020-01-01 11:10:00 January 2020 NA NA NA
#> 111 2020-01-01 11:20:00 January 2020 NA NA NA
#> 112 2020-01-01 11:30:00 January 2020 NA NA NA
#> 113 2020-01-01 11:40:00 January 2020 NA NA NA
#> 114 2020-01-01 11:50:00 January 2020 NA NA NA
#> 115 2020-01-01 12:00:00 January 2020 NA NA NA
#> 116 2020-01-01 12:10:00 January 2020 42.4098 73.7999 24.5124
#> 117 2020-01-01 12:20:00 January 2020 45.0185 78.3394 26.0202
#> 118 2020-01-01 12:30:00 January 2020 NA NA NA
#> 119 2020-01-01 12:40:00 January 2020 NA 6.8972 44.9267
#> 120 2020-01-01 12:50:00 January 2020 NA NA NA
#> 121 2020-01-01 13:00:00 January 2020 NA NA NA
#> 122 2020-01-01 13:10:00 January 2020 NA NA NA
#> 123 2020-01-01 13:20:00 January 2020 NA NA NA
#> 124 2020-01-01 13:30:00 January 2020 NA NA NA
#> 125 2020-01-01 13:40:00 January 2020 NA NA NA
#> 126 2020-01-01 13:50:00 January 2020 NA NA NA
#> 127 2020-01-01 14:00:00 January 2020 NA NA NA
#> 128 2020-01-01 14:10:00 January 2020 NA NA NA
#> 129 2020-01-01 14:20:00 January 2020 60.9227 6.9600 45.3353
#> 130 2020-01-01 14:30:00 January 2020 NA NA NA
#> 131 2020-01-01 14:40:00 January 2020 NA 7.2332 47.1152
#> 132 2020-01-01 14:50:00 January 2020 NA NA NA
#> 133 2020-01-01 15:00:00 January 2020 NA NA NA
#> 134 2020-01-01 15:10:00 January 2020 NA NA NA
#> 135 2020-01-01 15:20:00 January 2020 NA NA NA
#> 136 2020-01-01 15:30:00 January 2020 NA 6.9422 45.2198
#> 137 2020-01-01 15:40:00 January 2020 NA NA NA
#> 138 2020-01-01 15:50:00 January 2020 NA NA NA
#> 139 2020-01-01 16:00:00 January 2020 NA NA NA
#> 140 2020-01-01 16:10:00 January 2020 57.1449 NA NA
#> 141 2020-01-01 16:20:00 January 2020 NA 9.3776 47.8526
#> 142 2020-01-01 16:30:00 January 2020 NA NA NA
#> 143 2020-01-01 16:40:00 January 2020 NA NA NA
#> 144 2020-01-01 16:50:00 January 2020 46.2837 72.0913 21.9483
#> 145 2020-01-01 17:00:00 January 2020 NA NA NA
#> 146 2020-01-01 17:10:00 January 2020 NA NA NA
#> 147 2020-01-01 17:20:00 January 2020 42.4445 75.6723 68.9153
#> 148 2020-01-01 17:30:00 January 2020 NA NA NA
#> 149 2020-01-01 17:40:00 January 2020 NA NA NA
#> 150 2020-01-01 17:50:00 January 2020 NA NA NA
#> 151 2020-01-01 18:00:00 January 2020 NA NA NA
#> 152 2020-01-01 18:10:00 January 2020 NA NA NA
#> 153 2020-01-01 18:20:00 January 2020 57.1449 NA NA
#> 154 2020-01-01 18:30:00 January 2020 NA NA NA
#> 155 2020-01-01 18:40:00 January 2020 NA NA NA
#> 156 2020-01-01 18:50:00 January 2020 NA NA NA
#> 157 2020-01-01 19:00:00 January 2020 NA NA NA
#> 158 2020-01-01 19:10:00 January 2020 NA NA NA
#> 159 2020-01-01 19:20:00 January 2020 NA NA NA
#> 160 2020-01-01 19:30:00 January 2020 NA NA NA
#> 161 2020-01-01 19:40:00 January 2020 NA NA NA
#> 162 2020-01-01 19:50:00 January 2020 NA NA NA
#> 163 2020-01-01 20:00:00 January 2020 96.1242 69.8581 21.2684
#> 164 2020-01-01 20:10:00 January 2020 NA NA NA
#> 165 2020-01-01 20:20:00 January 2020 NA NA NA
#> 166 2020-01-01 20:30:00 January 2020 NA NA NA
#> 167 2020-01-01 20:40:00 January 2020 NA NA NA
#> 168 2020-01-01 20:50:00 January 2020 NA 9.3871 47.9009
#> 169 2020-01-01 21:00:00 January 2020 NA NA NA
#> 170 2020-01-01 21:10:00 January 2020 57.1449 NA NA
#> 171 2020-01-01 21:20:00 January 2020 NA NA NA
#> 172 2020-01-01 21:30:00 January 2020 NA NA NA
#> 173 2020-01-01 21:40:00 January 2020 NA NA NA
#> 174 2020-01-01 21:50:00 January 2020 NA NA NA
#> 175 2020-01-01 22:00:00 January 2020 NA 9.3871 47.9009
#> 176 2020-01-01 22:10:00 January 2020 NA NA NA
#> 177 2020-01-01 22:20:00 January 2020 NA NA NA
#> 178 2020-01-01 22:30:00 January 2020 NA NA NA
#> 179 2020-01-01 22:40:00 January 2020 NA NA NA
#> 180 2020-01-01 22:50:00 January 2020 NA NA NA
#> 181 2020-01-01 23:00:00 January 2020 NA NA NA
#> 182 2020-01-01 23:10:00 January 2020 NA NA NA
#> 183 2020-01-01 23:20:00 January 2020 NA NA NA
#> 184 2020-01-01 23:30:00 January 2020 NA NA NA
#> 185 2020-01-01 23:40:00 January 2020 NA NA NA
#> 186 2020-01-01 23:50:00 January 2020 NA NA NA
#> 187 2020-01-02 00:00:00 January 2020 57.1449 NA NA
#> 188 2020-01-02 00:10:00 January 2020 NA NA NA
#> 189 2020-01-02 00:20:00 January 2020 NA NA NA
#> 190 2020-01-02 00:30:00 January 2020 NA NA NA
#> 191 2020-01-02 00:40:00 January 2020 NA NA NA
#> 192 2020-01-02 00:50:00 January 2020 46.2837 72.0913 21.9483
#> 193 2020-01-02 01:00:00 January 2020 49.8677 77.6738 23.6479
#> 194 2020-01-02 01:10:00 January 2020 NA NA NA
#> 195 2020-01-02 01:20:00 January 2020 NA NA NA
#> 196 2020-01-02 01:30:00 January 2020 58.8257 NA NA
#> 197 2020-01-02 01:40:00 January 2020 NA NA NA
#> 198 2020-01-02 01:50:00 January 2020 NA NA NA
#> 199 2020-01-02 02:00:00 January 2020 NA NA NA
#> 200 2020-01-02 02:10:00 January 2020 NA NA NA