R Reordering many columns

Question

I have looked at some threads here but could not find the solution to my question. I have a data.frame with 1284 columns (214 countries by 6 parameters) and I would like to reorder those. The principle is like the one in the example below.

# Reorder [A1 A2 A3 B1 B2 B3] to [A1 B1 A2 B2 A3 B3]
x <- data.frame("A1"=1, "A2"=2, "A3"=3, "B1"=1, "B2"=2, "B3"=3)
y <- x[,c(1, 4, 2, 5, 3, 6)]
> x
  A1 A2 A3 B1 B2 B3
1  1  2  3  1  2  3
> y
  A1 B1 A2 B2 A3 B3
1  1  1  2  2  3  3

That works very well but my data.frame has more columns and when I try the same idea I get an error, probably because the line is too long.

dfx<-dfy[,c(1,215,429,643,857,1071,2,216,430,644,858,1072,3,217,431,645,859,1073,4,218,432,646,860,1074,5,219,433,647,861,1075,6,220,434,648,862,1076,7,221,435,649,863,1077,8,222,436,650,864,1078,9,223,437,651,865,1079,10,224,438,652,866,1080,11,225,439,653,867,1081,12,226,440,654,868,1082,13,227,441,655,869,1083,14,228,442,656,870,1084,15,229,443,657,871,1085,16,230,444,658,872,1086,17,231,445,659,873,1087,18,232,446,660,874,1088,19,233,447,661,875,1089,20,234,448,662,876,1090,21,235,449,663,877,1091,22,236,450,664,878,1092,23,237,451,665,879,1093,24,238,452,666,880,1094,25,239,453,667,881,1095,26,240,454,668,882,1096,27,241,455,669,883,1097,28,242,456,670,884,1098,29,243,457,671,885,1099,30,244,458,672,886,1100,31,245,459,673,887,1101,32,246,460,674,888,1102,33,247,461,675,889,1103,34,248,462,676,890,1104,35,249,463,677,891,1105,36,250,464,678,892,1106,37,251,465,679,893,1107,38,252,466,680,894,1108,39,253,467,681,895,1109,40,254,468,682,896,1110,41,255,469,683,897,1111,42,256,470,684,898,1112,43,257,471,685,899,1113,44,258,472,686,900,1114,45,259,473,687,901,1115,46,260,474,688,902,1116,47,261,475,689,903,1117,48,262,476,690,904,1118,49,263,477,691,905,1119,50,264,478,692,906,1120,51,265,479,693,907,1121,52,266,480,694,908,1122,53,267,481,695,909,1123,54,268,482,696,910,1124,55,269,483,697,911,1125,56,270,484,698,912,1126,57,271,485,699,913,1127,58,272,486,700,914,1128,59,273,487,701,915,1129,60,274,488,702,916,1130,61,275,489,703,917,1131,62,276,490,704,918,1132,63,277,491,705,919,1133,64,278,492,706,920,1134,65,279,493,707,921,1135,66,280,494,708,922,1136,67,281,495,709,923,1137,68,282,496,710,924,1138,69,283,497,711,925,1139,70,284,498,712,926,1140,71,285,499,713,927,1141,72,286,500,714,928,1142,73,287,501,715,929,1143,74,288,502,716,930,1144,75,289,503,717,931,1145,76,290,504,718,932,1146,77,291,505,719,933,1147,78,292,506,720,934,1148,79,293,507,721,935,1149,80,294,508,722,936,1150,81,295,509,723,937,1151,82,296,510,724,938,1152,83,297,511,725,939,1153,84,298,512,726,940,1154,85,299,513,727,941,1155,86,300,514,728,942,1156,87,301,515,729,943,1157,88,302,516,730,944,1158,89,303,517,731,945,1159,90,304,518,732,946,1160,91,305,519,733,947,1161,92,306,520,734,948,1162,93,307,521,735,949,1163,94,308,522,736,950,1164,95,309,523,737,951,1165,96,310,524,738,952,1166,97,311,525,739,953,1167,98,312,526,740,954,1168,99,313,527,741,955,1169,100,314,528,742,956,1170,101,315,529,743,957,1171,102,316,530,744,958,1172,103,317,531,745,959,1173,104,318,532,746,960,1174,105,319,533,747,961,1175,106,320,534,748,962,1176,107,321,535,749,963,1177,108,322,536,750,964,1178,109,323,537,751,965,1179,110,324,538,752,966,1180,111,325,539,753,967,1181,112,326,540,754,968,1182,113,327,541,755,969,1183,114,328,542,756,970,1184,115,329,543,757,971,1185,116,330,544,758,972,1186,117,331,545,759,973,1187,118,332,546,760,974,1188,119,333,547,761,975,1189,120,334,548,762,976,1190,121,335,549,763,977,1191,122,336,550,764,978,1192,123,337,551,765,979,1193,124,338,552,766,980,1194,125,339,553,767,981,1195,126,340,554,768,982,1196,127,341,555,769,983,1197,128,342,556,770,984,1198,129,343,557,771,985,1199,130,344,558,772,986,1200,131,345,559,773,987,1201,132,346,560,774,988,1202,133,347,561,775,989,1203,134,348,562,776,990,1204,135,349,563,777,991,1205,136,350,564,778,992,1206,137,351,565,779,993,1207,138,352,566,780,994,1208,139,353,567,781,995,1209,140,354,568,782,996,1210,141,355,569,783,997,1211,142,356,570,784,998,1212,143,357,571,785,999,1213,144,358,572,786,1000,1214,145,359,573,787,1001,1215,146,360,574,788,1002,1216,147,361,575,789,1003,1217,148,362,576,790,1004,1218,149,363,577,791,1005,1219,150,364,578,792,1006,1220,151,365,579,793,1007,1221,152,366,580,794,1008,1222,153,367,581,795,1009,1223,154,368,582,796,1010,1224,155,369,583,797,1011,1225,156,370,584,798,1012,1226,157,371,585,799,1013,1227,158,372,586,800,1014,1228,159,373,587,801,1015,1229,160,374,588,802,1016,1230,161,375,589,803,1017,1231,162,376,590,804,1018,1232,163,377,591,805,1019,1233,164,378,592,806,1020,1234,165,379,593,807,1021,1235,166,380,594,808,1022,1236,167,381,595,809,1023,1237,168,382,596,810,1024,1238,169,383,597,811,1025,1239,170,384,598,812,1026,1240,171,385,599,813,1027,1241,172,386,600,814,1028,1242,173,387,601,815,1029,1243,174,388,602,816,1030,1244,175,389,603,817,1031,1245,176,390,604,818,1032,1246,177,391,605,819,1033,1247,178,392,606,820,1034,1248,179,393,607,821,1035,1249,180,394,608,822,1036,1250,181,395,609,823,1037,1251,182,396,610,824,1038,1252,183,397,611,825,1039,1253,184,398,612,826,1040,1254,185,399,613,827,1041,1255,186,400,614,828,1042,1256,187,401,615,829,1043,1257,188,402,616,830,1044,1258,189,403,617,831,1045,1259,190,404,618,832,1046,1260,191,405,619,833,1047,1261,192,406,620,834,1048,1262,193,407,621,835,1049,1263,194,408,622,836,1050,1264,195,409,623,837,1051,1265,196,410,624,838,1052,1266,197,411,625,839,1053,1267,198,412,626,840,1054,1268,199,413,627,841,1055,1269,200,414,628,842,1056,1270,201,415,629,843,1057,1271,202,416,630,844,1058,1272,203,417,631,845,1059,1273,204,418,632,846,1060,1274,205,419,633,847,1061,1275,206,420,634,848,1062,1276,207,421,635,849,1063,1277,208,422,636,850,1064,1278,209,423,637,851,1065,1279,210,424,638,852,1066,1280,211,425,639,853,1067,1281,212,426,640,854,1068,1282,213,427,641,855,1069,1283,214,428,642,856,1070,1284)]

Maybe there is a way to get it done manually but a more elegant solution would be nice.

Answer 1

Yes the line is too long as you already have assumed.

The manual of parse says: The line-length limit is 4095 bytes when reading from the console.

If you press return somewhere before you reach the 4095 bytes, then it should work.

Your indices have a pattern and maybe you can make use of seq and rep like:

dfx <- dfy[,seq(1, by=214, length.out=6) + rep(0:213, each=6)]

Answer 2

Using dplyr::select:

countrys <- c("de", "en", "fr")
attr_coun <- c("attr_1", "attr_2")

tibble::tibble("de.attr_1" = 1, "de.attr_2" = 1,
               "en.attr_1" = 1, "en.attr_2"  = 1,
               "fr.attr_1" = 1, "fr.attr_2" = 1) %>% 
  dplyr::select(as.vector(outer(countrys, attr_coun, paste, sep=".")))

A tibble: 1 x 6
  de.attr_1 en.attr_1 fr.attr_1 de.attr_2 en.attr_2 fr.attr_2
      <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>
1         1         1         1         1         1         1

Answer 3

Using recommendations from this question for sorting mixed letters/numbers, either of these should work:

y <- x[, gtools::mixedsort(names(x))]
y <- x[, stringr::str_sort(names(x), numeric = TRUE)]

Answer 4

Since you said you have 6 parameters for 214 countries, this slightly more elegant solution should work

# create index of columns
idx <- numeric(0)
for(i in 1:214)
  idx <- c(idx, seq(i, 214*6, by = 214))
# [1]    1  215  429  643  857 1071    2  216  430  644  858 1072    3  217 ...

# reorder the dataframe
dfx <- dfy[, idx]

Answer 5

Here is a more generalized Base R approach which relies on spliting the column names on the letter / digit boundary:

It uses a regular expression (?<=[A-Za-z]) ?(?=[0-9]) which looks for an optional space preceded by a letter and followed by a number. This is called lookbehind and lookahead.

data <- data.frame(t(1:25))
names(data) <- paste0(rep(c("France","Germany ","Japan","SouthAfrica","Costa Rica"),each=5),
                      rep(1:5,times=5))
data
#  France1 France2 France3 France4 France5 Germany 1 Germany 2 Germany 3 Germany 4
#1       1       2       3       4       5         6         7         8         9


split <- do.call(rbind,strsplit(names(data),"(?<=[A-Za-z]) ?(?=[0-9])",perl=TRUE))
my.order <- order(split[,2],split[,1])
data[,my.order]
# Costa Rica1 France1 Germany 1 Japan1 SouthAfrica1 Costa Rica2 France2 Germany 2
#1          21       1         6     11           16          22       2         7