Splits a df with 1 colum of numeric values into multiple columns

LuisDosantos1 · February 1, 2022, 5:20pm

Hello dear R community,
I have a folder with documents from the tpy file, all of which have the same structure. My goal is to delete specific lines of each file and calculate the average from the remaining lines. So far I have been able to read in a file and perform the further processing steps. Now I have a problem with splitting the 1 column into several columns. So in the following picture you see sitation (DF1) and the aim (Df2) for the seperation step.

I already tried the following code:
dfTest<-separate_rows(df1, sep = "",convert = TRUE)

here is df1:
structure(list(Mount_Tai19120200 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 19 12 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 " 1 1 )), class = Mount_Tai19120200
1 2 1 1 19 12 3 1 1 19 12 4 1 1 19 12 5 1 1 19 12 6 1 1 19 12 7 1 1 19 12 8 1 1 19 12 9 1 1 19 12 10 1 1 19 12 11 1 1 19 12 12 1 1 19 12 13 1 1 19 12 14 1 1 19 12 15 1 1 19 12 16 1 1 19 12 17 1 1 19 12 18 1 1 19 12 19 1 1 19 12 20 1 1 19 12 21 1 1 19 12 22 1 1 19 12 23 1 1 19 12 24 1 1 19 12 25 1 1 19 12 26 1 1 19 12 27 1 1 28 1 1 29 1 1 30 1 1 31 1 1 32 1 1 33 1 1 34 1 1 35 1 1 36 1 1 37 1 1 38 1 1 39 1 1 40 1 1 41 1 1 42 1 1 43 1 1 44 1 1 45 1 1 46 1 1 47 1 1 48 1 1 49 1 1 50 1 1 51 1 1 52 1 1 53 1 1 54 1 1 55 1 1 56 1 1 57 1 1 58 1 1 59 1 1 60 1 1 61 1 1 62 1 1 63 1 1 64 1 1 65 1 1 66 1 1 67 1 1 68 1 1 69 1 1 70 1 1 71 1 1 72 1 1 73 1 1 74 1 1 = c(" 10 PRESSURE THETA AIR_TEMP RAINFALL MIXDEPTH RELHUMID SPCHUMID H2OMIXRA TERR_MSL SUN_FLUX",
2 0 0 0 0.0 36.256 117.106 1250.0 849.2 276.2 263.6 0.0 81.6 42.4 0.9 0.9 280.5 7.0",
1 23 0 1 -1.0 36.535 116.821 1313.0 859.5 275.5 263.8 0.0 186.2 40.4 0.9 0.9 118.5 4.7",
1 22 0 2 -2.0 36.833 116.596 1336.7 866.8 275.0 264.0 0.0 193.5 34.6 0.8 0.8 23.4 2.3",
1 21 0 3 -3.0 37.131 116.385 1356.1 865.8 274.8 263.7 0.0 177.6 36.2 0.8 0.8 23.4 0.0",
1 20 0 2 -4.0 37.427 116.132 1391.0 861.2 275.0 263.5 0.0 68.7 32.5 0.7 0.7 23.4 0.0",
1 19 0 1 -5.0 37.747 115.868 1416.7 860.1 275.0 263.4 0.0 54.2 27.7 0.6 0.6 23.4 0.0",
1 18 0 0 -6.0 38.067 115.596 1365.0 861.5 275.4 263.9 0.0 113.5 23.8 0.5 0.5 23.4 0.0",
1 17 0 1 -7.0 38.371 115.330 1283.5 879.5 275.3 265.4 0.0 489.4 25.2 0.6 0.6 23.4 0.0",
1 16 0 2 -8.0 38.709 114.974 1692.2 828.5 275.7 261.3 0.0 534.1 25.1 0.5 0.5 91.0 0.0",
1 15 0 3 -9.0 39.207 114.464 2035.1 699.9 283.5 256.0 0.0 770.0 17.0 0.2 0.2 957.3 0.0",
1 14 0 2 -10.0 39.793 113.873 2283.2 637.1 285.8 251.2 0.0 589.4 26.2 0.3 0.3 1417.9 28.2",
1 13 0 1 -11.0 40.292 113.306 2371.0 641.8 285.1 251.2 0.0 768.6 26.3 0.3 0.3 1282.8 46.7",
1 12 0 0 -12.0 40.834 112.739 2326.8 616.7 287.0 250.0 0.0 930.3 28.0 0.3 0.3 1575.1 62.0",
1 11 0 1 -13.0 41.436 112.128 2347.9 601.5 287.7 248.8 0.1 687.9 31.2 0.3 0.3 1763.3 69.1",
1 10 0 2 -14.0 42.036 111.513 2576.6 616.9 285.3 248.5 0.0 471.6 34.9 0.3 0.3 1330.6 92.0",
1 9 0 3 -15.0 42.603 110.927 2759.8 625.8 283.5 247.9 0.0 199.1 38.6 0.3 0.3 1039.7 134.3",
1 8 0 2 -16.0 43.154 110.377 2852.4 611.2 283.9 246.6 0.0 964.0 39.0 0.3 0.3 1105.8 171.4",
1 7 0 1 -17.0 43.730 109.837 2975.4 607.1 283.8 246.0 0.0 1032.1 43.2 0.3 0.3 1036.9 211.0",
1 6 0 0 -18.0 44.279 109.282 3093.3 612.8 282.6 245.6 0.0 1231.4 48.1 0.3 0.3 841.2 248.0",
1 5 0 1 -19.0 44.845 108.759 3087.7 589.9 284.0 244.2 0.0 959.3 46.4 0.3 0.3 1119.3 213.2",
1 4 0 2 -20.0 45.436 108.332 3231.4 574.0 284.3 242.6 0.0 625.0 59.5 0.3 0.3 1164.5 145.9",
1 3 0 3 -21.0 46.084 107.952 3244.4 560.7 285.1 241.6 0.0 317.9 62.0 0.3 0.3 1315.8 87.5",
1 2 0 2 -22.0 46.817 107.587 3354.6 548.8 285.0 240.1 0.0 241.6 65.9 0.3 0.3 1344.3 52.4",
1 1 0 1 -23.0 47.630 107.231 3215.6 540.0 286.3 240.1 0.0 82.4 66.9 0.3 0.3 1591.6 23.9",
1 0 0 0 -24.0 48.530 106.787 3390.4 548.2 284.2 239.3 0.0 22.3 79.6 0.3 0.3 1298.0 0.0",
19 11 30 23 0 1 -25.0 49.394 106.298 3689.5 551.1 281.6 237.5 0.0 30.4 84.3 0.3 0.3 943.0 0.0",
19 11 30 22 0 2 -26.0 50.189 105.782 3783.3 550.9 280.7 236.7 0.0 13.9 97.0 0.3 0.3 850.4 0.0",
19 11 30 21 0 3 -27.0 50.987 105.265 3312.3 548.2 283.6 238.8 0.0 298.8 86.7 0.4 0.4 1339.1 0.0",
19 11 30 20 0 2 -28.0 51.829 104.614 3580.6 592.1 277.8 239.1 0.0 1042.8 77.6 0.3 0.3 528.6 0.0",
19 11 30 19 0 1 -29.0 52.600 103.875 3572.1 597.8 277.7 239.7 0.0 316.9 77.9 0.3 0.3 468.3 0.0",
19 11 30 18 0 0 -30.0 53.360 103.048 3615.1 588.9 278.6 239.5 0.0 411.5 97.6 0.4 0.4 530.0 0.0",
19 11 30 17 0 1 -31.0 54.107 102.204 3602.2 591.3 278.8 239.9 0.0 793.2 75.8 0.3 0.3 526.9 0.0",
19 11 30 16 0 2 -32.0 54.815 101.344 3511.9 595.8 279.3 240.8 0.0 886.3 64.8 0.3 0.3 559.1 0.0",
19 11 30 15 0 3 -33.0 55.501 100.511 3374.4 610.6 279.2 242.5 0.0 934.0 69.3 0.4 0.4 502.0 0.0",
19 11 30 14 0 2 -34.0 56.159 99.650 3373.7 624.3 278.0 242.9 0.0 709.0 58.6 0.3 0.3 351.0 10.7",
19 11 30 13 0 1 -35.0 56.808 98.816 3351.9 625.0 278.2 243.2 0.0 518.0 47.8 0.3 0.3 352.2 21.3",
19 11 30 12 0 0 -36.0 57.461 97.997 3278.8 633.8 278.1 244.1 0.0 385.7 38.6 0.2 0.2 333.2 40.0",
19 11 30 11 0 1 -37.0 58.118 97.179 3242.8 639.7 277.7 244.4 0.0 253.9 43.2 0.3 0.3 301.1 52.1",
19 11 30 10 0 2 -38.0 58.761 96.327 3297.0 633.3 277.6 243.6 0.0 383.5 42.0 0.2 0.2 306.3 61.2",
19 11 30 9 0 3 -39.0 59.415 95.473 3263.2 626.3 278.4 243.6 0.0 95.5 40.9 0.2 0.2 398.8 64.3",
19 11 30 8 0 2 -40.0 60.079 94.634 3237.3 619.6 278.8 243.1 0.0 381.8 55.3 0.3 0.3 499.7 48.0",
19 11 30 7 0 1 -41.0 60.742 93.827 3344.0 627.1 277.7 243.0 0.0 376.9 43.4 0.2 0.2 303.6 40.1",
19 11 30 6 0 0 -42.0 61.400 93.014 3340.7 628.7 277.7 243.2 0.0 87.9 28.7 0.2 0.2 284.1 32.0",
19 11 30 5 0 1 -43.0 62.088 92.168 3395.4 621.7 277.4 242.1 0.0 62.7 49.9 0.2 0.2 292.7 19.4",
19 11 30 4 0 2 -44.0 62.796 91.248 3443.4 608.2 277.8 240.9 0.0 25.0 47.9 0.2 0.2 401.1 8.0",
19 11 30 3 0 3 -45.0 63.510 90.270 3526.7 605.4 277.3 240.2 0.0 9.9 18.9 0.1 0.1 344.6 0.0",
19 11 30 2 0 2 -46.0 64.221 89.218 3510.1 604.6 277.3 240.1 0.0 17.4 30.4 0.1 0.1 360.3 0.0",
19 11 30 1 0 1 -47.0 64.898 88.074 3564.8 620.6 275.7 240.6 0.0 28.6 41.3 0.2 0.2 106.5 0.0",
19 11 30 0 0 0 -48.0 65.528 86.897 3598.0 623.8 275.1 240.4 0.0 29.2 26.7 0.1 0.1 23.4 0.0",
19 11 29 23 0 1 -49.0 66.145 85.617 3600.5 619.5 275.5 240.2 0.0 35.8 25.9 0.1 0.1 81.2 0.0",
19 11 29 22 0 2 -50.0 66.724 84.226 3645.1 617.4 275.2 239.8 0.0 37.6 32.6 0.1 0.1 55.4 0.0",
19 11 29 21 0 3 -51.0 67.264 82.813 3638.0 616.9 275.1 239.6 0.0 25.8 28.9 0.1 0.1 55.4 0.0",
19 11 29 20 0 2 -52.0 67.789 81.383 3638.0 615.6 275.4 239.7 0.0 59.2 26.8 0.1 0.1 55.4 0.0",
19 11 29 19 0 1 -53.0 68.279 79.972 3641.2 619.1 275.0 239.7 0.0 95.4 29.9 0.1 0.1 23.4 0.0",
19 11 29 18 0 0 -54.0 68.751 78.560 3621.8 617.6 274.7 239.3 0.0 179.1 33.0 0.1 0.1 55.4 0.0",
19 11 29 17 0 1 -55.0 69.215 77.129 3598.1 622.3 274.6 239.8 0.0 221.4 31.1 0.1 0.1 21.8 0.0",
19 11 29 16 0 2 -56.0 69.672 75.659 3584.9 621.3 275.2 240.1 0.0 188.8 27.8 0.1 0.1 37.4 0.0",
19 11 29 15 0 3 -57.0 70.124 74.109 3584.2 622.1 275.5 240.5 0.0 86.5 27.5 0.1 0.1 23.4 0.0",
19 11 29 14 0 2 -58.0 70.579 72.588 3615.7 621.1 275.3 240.3 0.0 73.1 26.0 0.1 0.1 4.8 0.0",
19 11 29 13 0 1 -59.0 71.039 71.116 3646.3 615.9 275.4 239.7 0.0 19.0 27.3 0.1 0.1 23.4 0.0",
19 11 29 12 0 0 -60.0 71.518 69.625 3677.5 612.2 275.5 239.4 0.0 10.0 28.9 0.1 0.1 39.4 0.0",
19 11 29 11 0 1 -61.0 72.027 68.222 3847.5 599.3 275.3 237.8 0.0 68.5 30.4 0.1 0.1 0.0 0.0",
19 11 29 10 0 2 -62.0 72.551 66.877 3979.7 588.0 275.5 236.6 0.0 308.9 35.1 0.1 0.1 0.0 0.0",
19 11 29 9 0 3 -63.0 73.048 65.440 4065.6 579.4 275.5 235.7 0.0 116.1 40.8 0.1 0.1 0.0 0.0",
19 11 29 8 0 2 -64.0 73.613 64.053 4176.2 569.8 275.4 234.4 0.0 60.5 44.1 0.1 0.1 0.0 0.0",
19 11 29 7 0 1 -65.0 74.246 62.706 4372.5 553.3 275.8 232.9 0.0 86.5 51.6 0.1 0.1 0.0 0.0",
19 11 29 6 0 0 -66.0 74.842 61.139 4491.0 542.3 276.2 231.9 0.0 32.1 54.5 0.1 0.1 3.8 0.0",
19 11 29 5 0 1 -67.0 75.440 59.475 4309.1 496.9 277.9 227.5 0.0 32.3 56.7 0.1 0.1 771.9 0.0",
19 11 29 4 0 2 -68.0 76.184 57.627 4692.7 524.9 275.8 229.4 0.0 1082.6 73.5 0.1 0.1 0.0 0.0",
19 11 29 3 0 3 -69.0 76.966 55.382 4732.3 521.8 275.7 228.8 0.0 797.0 78.1 0.1 0.1 0.0 0.0",
19 11 29 2 0 2 -70.0 77.756 52.433 4817.3 514.5 275.7 227.9 0.0 394.5 88.0 0.1 0.1 0.0 0.0",
19 11 29 1 0 1 -71.0 78.390 48.754 4896.2 507.9 275.6 227.1 0.0 316.6 92.4 0.1 0.1 0.0 0.0",
19 11 29 0 0 0 -72.0 78.852 44.409 4976.2 501.2 275.6 226.2 0.0 342.5 96.9 0.1 0.1 0.0 0.0"
"data.frame", row.names = c(NA, -74L))
10 PRESSURE THETA AIR_TEMP RAINFALL MIXDEPTH RELHUMID SPCHUMID H2OMIXRA TERR_MSL SUN_FLUX
2 0 0 0 0.0 36.256 117.106 1250.0 849.2 276.2 263.6 0.0 81.6 42.4 0.9 0.9 280.5 7.0
1 23 0 1 -1.0 36.535 116.821 1313.0 859.5 275.5 263.8 0.0 186.2 40.4 0.9 0.9 118.5 4.7
1 22 0 2 -2.0 36.833 116.596 1336.7 866.8 275.0 264.0 0.0 193.5 34.6 0.8 0.8 23.4 2.3
1 21 0 3 -3.0 37.131 116.385 1356.1 865.8 274.8 263.7 0.0 177.6 36.2 0.8 0.8 23.4 0.0
1 20 0 2 -4.0 37.427 116.132 1391.0 861.2 275.0 263.5 0.0 68.7 32.5 0.7 0.7 23.4 0.0
1 19 0 1 -5.0 37.747 115.868 1416.7 860.1 275.0 263.4 0.0 54.2 27.7 0.6 0.6 23.4 0.0
1 18 0 0 -6.0 38.067 115.596 1365.0 861.5 275.4 263.9 0.0 113.5 23.8 0.5 0.5 23.4 0.0
1 17 0 1 -7.0 38.371 115.330 1283.5 879.5 275.3 265.4 0.0 489.4 25.2 0.6 0.6 23.4 0.0
1 16 0 2 -8.0 38.709 114.974 1692.2 828.5 275.7 261.3 0.0 534.1 25.1 0.5 0.5 91.0 0.0
1 15 0 3 -9.0 39.207 114.464 2035.1 699.9 283.5 256.0 0.0 770.0 17.0 0.2 0.2 957.3 0.0
1 14 0 2 -10.0 39.793 113.873 2283.2 637.1 285.8 251.2 0.0 589.4 26.2 0.3 0.3 1417.9 28.2
1 13 0 1 -11.0 40.292 113.306 2371.0 641.8 285.1 251.2 0.0 768.6 26.3 0.3 0.3 1282.8 46.7
1 12 0 0 -12.0 40.834 112.739 2326.8 616.7 287.0 250.0 0.0 930.3 28.0 0.3 0.3 1575.1 62.0
1 11 0 1 -13.0 41.436 112.128 2347.9 601.5 287.7 248.8 0.1 687.9 31.2 0.3 0.3 1763.3 69.1
1 10 0 2 -14.0 42.036 111.513 2576.6 616.9 285.3 248.5 0.0 471.6 34.9 0.3 0.3 1330.6 92.0
1 9 0 3 -15.0 42.603 110.927 2759.8 625.8 283.5 247.9 0.0 199.1 38.6 0.3 0.3 1039.7 134.3
1 8 0 2 -16.0 43.154 110.377 2852.4 611.2 283.9 246.6 0.0 964.0 39.0 0.3 0.3 1105.8 171.4
1 7 0 1 -17.0 43.730 109.837 2975.4 607.1 283.8 246.0 0.0 1032.1 43.2 0.3 0.3 1036.9 211.0
1 6 0 0 -18.0 44.279 109.282 3093.3 612.8 282.6 245.6 0.0 1231.4 48.1 0.3 0.3 841.2 248.0
1 5 0 1 -19.0 44.845 108.759 3087.7 589.9 284.0 244.2 0.0 959.3 46.4 0.3 0.3 1119.3 213.2
1 4 0 2 -20.0 45.436 108.332 3231.4 574.0 284.3 242.6 0.0 625.0 59.5 0.3 0.3 1164.5 145.9
1 3 0 3 -21.0 46.084 107.952 3244.4 560.7 285.1 241.6 0.0 317.9 62.0 0.3 0.3 1315.8 87.5
1 2 0 2 -22.0 46.817 107.587 3354.6 548.8 285.0 240.1 0.0 241.6 65.9 0.3 0.3 1344.3 52.4
1 1 0 1 -23.0 47.630 107.231 3215.6 540.0 286.3 240.1 0.0 82.4 66.9 0.3 0.3 1591.6 23.9
1 0 0 0 -24.0 48.530 106.787 3390.4 548.2 284.2 239.3 0.0 22.3 79.6 0.3 0.3 1298.0 0.0
19 11 30 23 0 1 -25.0 49.394 106.298 3689.5 551.1 281.6 237.5 0.0 30.4 84.3 0.3 0.3 943.0 0.0
19 11 30 22 0 2 -26.0 50.189 105.782 3783.3 550.9 280.7 236.7 0.0 13.9 97.0 0.3 0.3 850.4 0.0
19 11 30 21 0 3 -27.0 50.987 105.265 3312.3 548.2 283.6 238.8 0.0 298.8 86.7 0.4 0.4 1339.1 0.0
19 11 30 20 0 2 -28.0 51.829 104.614 3580.6 592.1 277.8 239.1 0.0 1042.8 77.6 0.3 0.3 528.6 0.0
19 11 30 19 0 1 -29.0 52.600 103.875 3572.1 597.8 277.7 239.7 0.0 316.9 77.9 0.3 0.3 468.3 0.0
19 11 30 18 0 0 -30.0 53.360 103.048 3615.1 588.9 278.6 239.5 0.0 411.5 97.6 0.4 0.4 530.0 0.0
19 11 30 17 0 1 -31.0 54.107 102.204 3602.2 591.3 278.8 239.9 0.0 793.2 75.8 0.3 0.3 526.9 0.0
19 11 30 16 0 2 -32.0 54.815 101.344 3511.9 595.8 279.3 240.8 0.0 886.3 64.8 0.3 0.3 559.1 0.0
19 11 30 15 0 3 -33.0 55.501 100.511 3374.4 610.6 279.2 242.5 0.0 934.0 69.3 0.4 0.4 502.0 0.0
19 11 30 14 0 2 -34.0 56.159 99.650 3373.7 624.3 278.0 242.9 0.0 709.0 58.6 0.3 0.3 351.0 10.7
19 11 30 13 0 1 -35.0 56.808 98.816 3351.9 625.0 278.2 243.2 0.0 518.0 47.8 0.3 0.3 352.2 21.3
19 11 30 12 0 0 -36.0 57.461 97.997 3278.8 633.8 278.1 244.1 0.0 385.7 38.6 0.2 0.2 333.2 40.0
19 11 30 11 0 1 -37.0 58.118 97.179 3242.8 639.7 277.7 244.4 0.0 253.9 43.2 0.3 0.3 301.1 52.1
19 11 30 10 0 2 -38.0 58.761 96.327 3297.0 633.3 277.6 243.6 0.0 383.5 42.0 0.2 0.2 306.3 61.2
19 11 30 9 0 3 -39.0 59.415 95.473 3263.2 626.3 278.4 243.6 0.0 95.5 40.9 0.2 0.2 398.8 64.3
19 11 30 8 0 2 -40.0 60.079 94.634 3237.3 619.6 278.8 243.1 0.0 381.8 55.3 0.3 0.3 499.7 48.0
19 11 30 7 0 1 -41.0 60.742 93.827 3344.0 627.1 277.7 243.0 0.0 376.9 43.4 0.2 0.2 303.6 40.1
19 11 30 6 0 0 -42.0 61.400 93.014 3340.7 628.7 277.7 243.2 0.0 87.9 28.7 0.2 0.2 284.1 32.0
19 11 30 5 0 1 -43.0 62.088 92.168 3395.4 621.7 277.4 242.1 0.0 62.7 49.9 0.2 0.2 292.7 19.4
19 11 30 4 0 2 -44.0 62.796 91.248 3443.4 608.2 277.8 240.9 0.0 25.0 47.9 0.2 0.2 401.1 8.0
19 11 30 3 0 3 -45.0 63.510 90.270 3526.7 605.4 277.3 240.2 0.0 9.9 18.9 0.1 0.1 344.6 0.0
19 11 30 2 0 2 -46.0 64.221 89.218 3510.1 604.6 277.3 240.1 0.0 17.4 30.4 0.1 0.1 360.3 0.0
19 11 30 1 0 1 -47.0 64.898 88.074 3564.8 620.6 275.7 240.6 0.0 28.6 41.3 0.2 0.2 106.5 0.0
19 11 30 0 0 0 -48.0 65.528 86.897 3598.0 623.8 275.1 240.4 0.0 29.2 26.7 0.1 0.1 23.4 0.0
19 11 29 23 0 1 -49.0 66.145 85.617 3600.5 619.5 275.5 240.2 0.0 35.8 25.9 0.1 0.1 81.2 0.0
19 11 29 22 0 2 -50.0 66.724 84.226 3645.1 617.4 275.2 239.8 0.0 37.6 32.6 0.1 0.1 55.4 0.0
19 11 29 21 0 3 -51.0 67.264 82.813 3638.0 616.9 275.1 239.6 0.0 25.8 28.9 0.1 0.1 55.4 0.0
19 11 29 20 0 2 -52.0 67.789 81.383 3638.0 615.6 275.4 239.7 0.0 59.2 26.8 0.1 0.1 55.4 0.0
19 11 29 19 0 1 -53.0 68.279 79.972 3641.2 619.1 275.0 239.7 0.0 95.4 29.9 0.1 0.1 23.4 0.0
19 11 29 18 0 0 -54.0 68.751 78.560 3621.8 617.6 274.7 239.3 0.0 179.1 33.0 0.1 0.1 55.4 0.0
19 11 29 17 0 1 -55.0 69.215 77.129 3598.1 622.3 274.6 239.8 0.0 221.4 31.1 0.1 0.1 21.8 0.0
19 11 29 16 0 2 -56.0 69.672 75.659 3584.9 621.3 275.2 240.1 0.0 188.8 27.8 0.1 0.1 37.4 0.0
19 11 29 15 0 3 -57.0 70.124 74.109 3584.2 622.1 275.5 240.5 0.0 86.5 27.5 0.1 0.1 23.4 0.0
19 11 29 14 0 2 -58.0 70.579 72.588 3615.7 621.1 275.3 240.3 0.0 73.1 26.0 0.1 0.1 4.8 0.0
19 11 29 13 0 1 -59.0 71.039 71.116 3646.3 615.9 275.4 239.7 0.0 19.0 27.3 0.1 0.1 23.4 0.0
19 11 29 12 0 0 -60.0 71.518 69.625 3677.5 612.2 275.5 239.4 0.0 10.0 28.9 0.1 0.1 39.4 0.0
19 11 29 11 0 1 -61.0 72.027 68.222 3847.5 599.3 275.3 237.8 0.0 68.5 30.4 0.1 0.1 0.0 0.0
19 11 29 10 0 2 -62.0 72.551 66.877 3979.7 588.0 275.5 236.6 0.0 308.9 35.1 0.1 0.1 0.0 0.0
19 11 29 9 0 3 -63.0 73.048 65.440 4065.6 579.4 275.5 235.7 0.0 116.1 40.8 0.1 0.1 0.0 0.0
19 11 29 8 0 2 -64.0 73.613 64.053 4176.2 569.8 275.4 234.4 0.0 60.5 44.1 0.1 0.1 0.0 0.0
19 11 29 7 0 1 -65.0 74.246 62.706 4372.5 553.3 275.8 232.9 0.0 86.5 51.6 0.1 0.1 0.0 0.0
19 11 29 6 0 0 -66.0 74.842 61.139 4491.0 542.3 276.2 231.9 0.0 32.1 54.5 0.1 0.1 3.8 0.0
19 11 29 5 0 1 -67.0 75.440 59.475 4309.1 496.9 277.9 227.5 0.0 32.3 56.7 0.1 0.1 771.9 0.0
19 11 29 4 0 2 -68.0 76.184 57.627 4692.7 524.9 275.8 229.4 0.0 1082.6 73.5 0.1 0.1 0.0 0.0
19 11 29 3 0 3 -69.0 76.966 55.382 4732.3 521.8 275.7 228.8 0.0 797.0 78.1 0.1 0.1 0.0 0.0
19 11 29 2 0 2 -70.0 77.756 52.433 4817.3 514.5 275.7 227.9 0.0 394.5 88.0 0.1 0.1 0.0 0.0
19 11 29 1 0 1 -71.0 78.390 48.754 4896.2 507.9 275.6 227.1 0.0 316.6 92.4 0.1 0.1 0.0 0.0
19 11 29 0 0 0 -72.0 78.852 44.409 4976.2 501.2 275.6 226.2 0.0 342.5 96.9 0.1 0.1 0.0 0.0

gsapijaszko · February 1, 2022, 6:44pm

Is it what you are looking for?

df1  |>
  tidyr::separate(Mount_Tai19120200, 
    into = c("ID","xx", "month", "day", "year", "xyz", "define the rest"), sep = " ")

Regards,
Grzegorz

LuisDosantos1 · February 2, 2022, 11:07am

Hello Grzegorz,

thanks for your answer, I have adapted your suggestion and it has partially worked. I played around with the delimter and was able to separate some columns, but unfortunately not all. This is probably because the spacing between the columns is not equidistant. Do you have another idea how to separate the columns? In Excel there is an option called "Fixes width" which allows you to define the width of the columns to be created. In the following pic you
can see the result of the apllied code and the "Excel option".

Regards,
Luis

LuisDosantos1 · February 3, 2022, 12:17pm

Hey Grzegroz,

that's very generous of you, I sent you the email with the subject:
"Splits a df with 1 colum of numeric values into multiple columns".

Many greetings,
Luis

nirgrahamuk · February 3, 2022, 12:31pm

if its a fixed width file, I would suggest you try to use : Read a fixed width file into a tibble — read_fwf • readr (tidyverse.org)

gsapijaszko · February 5, 2022, 8:17pm

Based on the file I got, my attempt:

Reading the file with ReadLines, trimming spaces, and removing the first row with data name:

a <- stringr::str_squish(readLines("Mount_Tai19120200.txt"))
a <- a[-1]

looking for elements of our list, just to get the number of it, creating a matrix and data frame. The matrix is my lazy approach to get data frame columns.

elements <- unlist(strsplit(a[1] , " " ))

m <- matrix(ncol = length(elements), byrow = TRUE)
df <- data.frame(m)

Then applying it to list:

for (i in 1:length(a)) {
  elements <- unlist(strsplit(a[i] , " " ))
  df <- rbind(df, elements)
}
df <- df[-1,]
head(df)
#>   X1 X2 X3 X4 X5 X6 X7 X8   X9    X10     X11    X12   X13   X14   X15 X16
#> 2  1  1 19 12  2  0  0  0  0.0 36.256 117.106 1250.0 849.2 276.2 263.6 0.0
#> 3  1  1 19 12  1 23  0  1 -1.0 36.535 116.821 1313.0 859.5 275.5 263.8 0.0
#> 4  1  1 19 12  1 22  0  2 -2.0 36.833 116.596 1336.7 866.8 275.0 264.0 0.0
#> 5  1  1 19 12  1 21  0  3 -3.0 37.131 116.385 1356.1 865.8 274.8 263.7 0.0
#> 6  1  1 19 12  1 20  0  2 -4.0 37.427 116.132 1391.0 861.2 275.0 263.5 0.0
#> 7  1  1 19 12  1 19  0  1 -5.0 37.747 115.868 1416.7 860.1 275.0 263.4 0.0
#>     X17  X18 X19 X20   X21 X22
#> 2  81.6 42.4 0.9 0.9 280.5 7.0
#> 3 186.2 40.4 0.9 0.9 118.5 4.7
#> 4 193.5 34.6 0.8 0.8  23.4 2.3
#> 5 177.6 36.2 0.8 0.8  23.4 0.0
#> 6  68.7 32.5 0.7 0.7  23.4 0.0
#> 7  54.2 27.7 0.6 0.6  23.4 0.0

@nirgrahamuk solution with read.fwf works fantastic:

head(read.fwf("/home/sapi/projekty/test/Mount_Tai19120200.txt", 
              widths = c(6, 6, 6, 6, 6, 6, 6, 6, 8, 9, 9, 9, 9, 9, 9, 9, 10, 9, 9, 9, 9, 9), 
              skip = 1))
#>   V1 V2 V3 V4 V5 V6 V7 V8 V9    V10     V11    V12   V13   V14   V15 V16   V17
#> 1  1  1 19 12  2  0  0  0  0 36.256 117.106 1250.0 849.2 276.2 263.6   0  81.6
#> 2  1  1 19 12  1 23  0  1 -1 36.535 116.821 1313.0 859.5 275.5 263.8   0 186.2
#> 3  1  1 19 12  1 22  0  2 -2 36.833 116.596 1336.7 866.8 275.0 264.0   0 193.5
#> 4  1  1 19 12  1 21  0  3 -3 37.131 116.385 1356.1 865.8 274.8 263.7   0 177.6
#> 5  1  1 19 12  1 20  0  2 -4 37.427 116.132 1391.0 861.2 275.0 263.5   0  68.7
#> 6  1  1 19 12  1 19  0  1 -5 37.747 115.868 1416.7 860.1 275.0 263.4   0  54.2
#>    V18 V19 V20   V21 V22
#> 1 42.4 0.9 0.9 280.5 7.0
#> 2 40.4 0.9 0.9 118.5 4.7
#> 3 34.6 0.8 0.8  23.4 2.3
#> 4 36.2 0.8 0.8  23.4 0.0
#> 5 32.5 0.7 0.7  23.4 0.0
#> 6 27.7 0.6 0.6  23.4 0.0

Just make sure if the width of the columns doesn't change from file to file.

Regards,
Grzegorz

LuisDosantos1 · February 6, 2022, 4:38pm

Your code worked perfect ! Thank you for your help !!!

Unfortunately, I wanted to offset the values of the df in the further process. Unfortunately, this is not possible because R does not "recognise" the values in the df. I have read that I have to tell R that these are doubles...for this you can use the function "unlist" in combination with "as.numeric". But this leads to all columns becoming one.

Best wishes,
Luis

nirgrahamuk · February 6, 2022, 9:30pm

I dont know what offsetting the values might mean.
You could use a reprex to show us where you are stuck given it seems you have at least succeeded to load the initial data ?

gsapijaszko · February 6, 2022, 10:48pm

Seems you have to convert character to numeric, so

df <- df |> mutate_all(as.numeric)

Should do the trick.

Regards,
Grzegorz

LuisDosantos1 · February 12, 2022, 4:35pm

Sorry that i didn't contact you for so long. by modifying your code,
i was able to solve the problem myself.

Many greetings,
Locardas

system · February 19, 2022, 4:36pm

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.