How can I organise and move row of data based on label matches?

Question

I have raw data shown below. I'm trying to move a row of data that corresponds to a label it matches to a new location in the dataframe.

dat<-read.table(text='RowLabels col1    col2    col3    col4    col5    col6
L   24363.7 25944.9 25646.1 25335.4 23564.2 25411.5
             610    411.4   439 437.3   436.9   420.7   516.9
             1  86.4    113.9   103.5   113.5   80.3    129
             2  102.1   99.5    96.3    100.4   99.5    86
             3  109.7   102.2   100.2   112.9   92.3    123.8
             4  88.9    87.1    103.6   102.5   93.6    134.1
             5  -50.3   -40.2   -72.3   -61.4   -27 -22.7
             6  -35.3   -9.3    25.3    -0.3    15.6    -27.3
             7  109.9   85.8    80.7    69.3    66.4    94
             181920 652.9   729.2   652.1   689.1   612.5   738.4
             1  104.3   107.3   103.5   104.2   98.3    110.1
             2  103.6   102.6   100.1   103.2   88.8    117.7
             3  53.5    99.1    46.7    70.3    53.9    32.5
             4  93.5    107.2   98.3    99.3    97.3    121.1
             5  96.8    109.3   104 102.2   98.7    112.9
             6  103.6   96.9    104.7   104.4   91.5    137.7
             7  97.6    106.8   94.8    105.5   84  106.4
             181930 732.1   709.6   725.8   729.5   554.5   873.1
             1  118.4   98.8    102.3   102 101.9   115.8
             2  96.7    103.3   104.6   105.2   81.9    128.7
             3  96  98.2    99.4    97.9    69.8    120.6
             4  100.7   101 103.6   106.6   59.6    136.2
             5  106.1   103.4   104.7   104.8   76.1    131.8
             6  105 102.1   103 108.3   81  124.7
             7  109.2   102.8   108.2   104.7   84.2    115.3
             N  3836.4  4395.8  4227.3  4567.4  4009.9  4434.6
             610    88.1    96.3    99.6    92  90  137.6
             1  88.1    96.3    99.6    92  90  137.6
             181920 113.1   100.6   106.5   104.2   87.3    108.2
             1  113.1   100.6   106.5   104.2   87.3    108.2
             181930 111.3   99.1    104.5   115.5   103.6   118.8
             1  111.3   99.1    104.5   115.5   103.6   118.8
             ',header=TRUE)

I want to match the values of the three N-prefix labels: 610, 181920 and 181930 with its corresponding L-prefix labels. Basically move that row of data into the L-prefix as a new row, labeled 0 or 8 for example. So, the result for label, 610 would look like:

RowLabels col1    col2    col3    col4    col5    col6
610    411.4   439 437.3   436.9   420.7   516.9
         1  86.4    113.9   103.5   113.5   80.3    129
         2  102.1   99.5    96.3    100.4   99.5    86
         3  109.7   102.2   100.2   112.9   92.3    123.8
         4  88.9    87.1    103.6   102.5   93.6    134.1
         5  -50.3   -40.2   -72.3   -61.4   -27 -22.7
         6  -35.3   -9.3    25.3    -0.3    15.6    -27.3
         7  109.9   85.8    80.7    69.3    66.4    94
         8  88.1    96.3    99.6    92  90  137.6

Is this possible? I tried searching and I found some resources pointing toward dplyr or tidyr or aggregate. But I can't find a good example that matches my case. How to combine rows based on unique values in R? and Aggregate rows by shared values in a variable

Answer 1

library(dplyr)
library(zoo)

df <- dat %>%
  filter(grepl("^\\d+$",RowLabels)) %>%
  mutate(RowLabels_temp = ifelse(grepl("^\\d{3,}$",RowLabels), as.numeric(as.character(RowLabels)), NA)) %>%
  na.locf() %>%
  select(-RowLabels) %>%
  distinct() %>%
  group_by(RowLabels_temp) %>%
  mutate(RowLabels_indexed = row_number()-1) %>%
  arrange(RowLabels_temp, RowLabels_indexed) %>%
  mutate(RowLabels_indexed = ifelse(RowLabels_indexed==0, RowLabels_temp, RowLabels_indexed)) %>%
  rename(RowLabels=RowLabels_indexed) %>%
  data.frame()
df <- df %>% select(-RowLabels_temp)
df

Output is

    col1  col2  col3  col4  col5  col6 RowLabels
1  411.4 439.0 437.3 436.9 420.7 516.9       610
2   86.4 113.9 103.5 113.5  80.3 129.0         1
3  102.1  99.5  96.3 100.4  99.5  86.0         2
4  109.7 102.2 100.2 112.9  92.3 123.8         3
5   88.9  87.1 103.6 102.5  93.6 134.1         4
6  -50.3 -40.2 -72.3 -61.4 -27.0 -22.7         5
7  -35.3  -9.3  25.3  -0.3  15.6 -27.3         6
8  109.9  85.8  80.7  69.3  66.4  94.0         7
9   88.1  96.3  99.6  92.0  90.0 137.6         8
...

Answer 2

It sounds like you want to use the match() function, for example:

target<-c(the values of your target order)
df<-df[match(target, df$column_to_reorder),]