Turn Specific Observation and Row into Column in R

Question

I have data with rows and columns similar to the below example and want to basically convert every other row into a column. However, I also want to extract each specific observation and put that result into the corresponding column. So I would take:

       X1 x y xx xy           yy  
ci.lo1  0 0 0  0  0 -19350.63073
ci.up1  0 0 0  0  0    104.23538
ci.lo2  0 0 0  0  0  -8740.25544
ci.up2  0 0 0  0  0     52.97149 

and trying to turn it into:

      X1.lo  X1.up x.lo x.up y.lo y.up xx.lo xx.up xy.lo xy.up        yy.lo     yy.up  
1   0            0    0    0    0    0     0     0     0     0 -19350.63073 104.23538
2   0            0    0    0    0    0     0     0     0     0  -8740.25544  52.97149

I was wondering if it was possible to do this type of conversion in tidy, if not in base R as well.

Thanks in advance.

Answer 1

data %>% 
    rownames_to_column() %>% 
    mutate(rowname2=sub("^ci\\.(.*)\\d$","\\1",rowname), 
           rowname =sub("[^0-9]+","", rowname)) %>% 
    pivot_wider(names_from = rowname2, 
                values_from = -c(rowname2, rowname), 
                id_cols = rowname,
                names_sep = ".") %>%
    column_to_rownames()
  X1.lo X1.up x.lo x.up y.lo y.up xx.lo xx.up xy.lo xy.up      yy.lo     yy.up
1     0     0    0    0    0    0     0     0     0     0 -19350.631 104.23538
2     0     0    0    0    0    0     0     0     0     0  -8740.255  52.97149