Change structure of a dataframe

user_11514409 0
Viewed 91

I am confronted to a very weird dataframe structure, here is an example

# A tibble: 3 x 4
  ColName...1 Sample1 ColName...2 Sample2
  <chr>         <dbl> <chr>         <dbl>
1 A                 1 A                 4
2 B                 2 B                 5
3 NA               NA C                 6

Whose code can be obtained from :

ColName...1 <- c("A","B",NA)
Sample1 <- c(1,2,NA)
ColName...2 <- c("A","B","C")
Sample2 <- c(4,5,6)

I wish to transform my data into a more conventional format :

  A B  C Sample
1 1 2 NA 1
2 4 5  6 2

Which can be obtained from :

# Desired output 
df <- data.frame(c(1,4),c(2,5),c(NA,6),c(1,2))
colnames(df) <- c("A","B","C","Sample")
df

In other words, I need to tell R that ColName...1, ColName...2, etc are variables containing the names of the dataframe and I need the columns Sample1, Sample2, etc... to be transposed so they are the rows in this dataframe. How can I code this ?

EDIT : The dataframe I am actually working with is messier. Here is how it looks :

# A tibble: 10 x 6
   Element...1 GeoPT8 Element...3 GeoPT9 Element...5 GeoPT10
   <chr>        <dbl> <chr>        <dbl> <chr>         <dbl>
 1 SiO2            66 SiO2            59 SiO2             64
 2 TiO2            67 TiO2            63 TiO2             69
 3 Al2O3           69 Al2O3           63 Al2O3            71
 4 Fe2O3           71 Fe2O3           68 Fe2O3            74
 5 Fe(II)O         16 Fe(II)O         17 MnO              73
 6 MnO             70 MnO             68 MgO              70
 7 MgO             69 MgO             64 CaO              73
 8 CaO             70 CaO             65 Na2O             73
 9 Na2O            71 Na2O            66 P2O5             60
10 K2O             69 K2O             64 LOI              54

Code to get this dataframe :

df <- structure(list(Element...1 = c("SiO2", "TiO2", "Al2O3", "Fe2O3", "Fe(II)O", "MnO", "MgO", "CaO", "Na2O", "K2O"), 
               GeoPT8 = c(66,67, 69, 71, 16, 70, 69, 70, 71, 69), 
               Element...3 = c("SiO2", "TiO2", "Al2O3", "Fe2O3", "Fe(II)O", "MnO", "MgO", "CaO", "Na2O", "K2O"),
               GeoPT9 = c(59, 63, 63, 68, 17, 68, 64, 65, 66, 64),
               Element...5 = c("SiO2", "TiO2", "Al2O3", "Fe2O3", "MnO", "MgO", "CaO", "Na2O", "P2O5", "LOI"), 
               GeoPT10 = c(64, 69, 71, 74, 73, 70, 73, 73, 60, 54)), row.names = c(NA, -10L),
          class = c("tbl_df", "tbl", "data.frame"))

As one can see, the columns Element...1 and Element...5 are not matching (Element...5 contains MnO but Element...1 does not). How can I say to R that the column containing the key to GeoPT8 is column Element...1, column containing the key to GeoPT10 is Element...5 and so forth ?

data-manipulation dataframe r
2 Answers

One option is to reshape to 'long' with pivot_longer and then do the reshaping back to 'wide'

library(dplyr)
library(tidyr)
df1 %>% 
  pivot_longer(cols = everything(), names_to = c(".value", "grp"), 
      names_sep = "(?<=\\D)(?=\\d)", values_drop_na = TRUE) %>% 
  pivot_wider(names_from = ColName..., values_from = Sample)  %>%
  select(-grp, everything(), Sample = grp)

-output

# A tibble: 2 x 4
#      A     B     C Sample
#  <int> <int> <int> <chr> 
#1     1     2    NA 1     
#2     4     5     6 2

Or this can be done by transposing the subset of data and set the names with the subset of columns with names in base R

nm1 <- do.call(pmax, c(df1[c(TRUE, FALSE)], na.rm = TRUE))
setNames(as.data.frame(t(unname(df1[c(FALSE, TRUE)]))), nm1)

Update

If the column names are not matching, then extract the suffix digit from the ... columns and paste it on the Geo columns assuming they are in the same sequence

library(stringr)
v1 <- str_extract(names(df)[c(TRUE, FALSE)], "\\d+$")
df %>% 
  rename_at(vars(starts_with('Geo')),
    ~ str_replace(., '\\d+$', str_c("...", v1)) ) %>% 
  pivot_longer(cols = everything(), names_to = c(".value", "grp"),
       names_sep = "(?<=\\.{3})(?=\\d$)", values_drop_na = TRUE) %>% 
  pivot_wider(names_from = 'Element...', values_from = 'GeoPT...') %>%
  select(-grp, everything(), Sample = grp)

# A tibble: 3 x 13
#   SiO2  TiO2 Al2O3 Fe2O3 `Fe(II)O`   MnO   MgO   CaO  Na2O  P2O5   K2O   LOI Sample
#  <dbl> <dbl> <dbl> <dbl>     <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> 
#1    66    67    69    71        16    70    69    70    71    NA    69    NA 1     
#2    59    63    63    68        17    68    64    65    66    NA    64    NA 3     
#3    64    69    71    74        NA    73    70    73    73    60    NA    54 5

data

df1 <- structure(list(ColName...1 = c("A", "B", NA), Sample1 = c(1L, 
2L, NA), ColName...2 = c("A", "B", "C"), Sample2 = 4:6),
class = "data.frame", row.names = c("1", 
"2", "3"))
user_37322710

This is longer than akrun's solution.

library(tidyverse)
df %>% 
  as_tibble() %>% 
  pivot_longer(
    cols = starts_with("Sample"),
    names_to = "names", 
    values_to = "values"
  ) %>% 
  select(-ColName...1, -names) %>% 
  type.convert(is.as = TRUE) %>% 
  group_by(ColName...2) %>% 
  mutate(row = row_number()) %>%
  pivot_wider(
    names_from = "ColName...2",
    values_from = "values"
  ) %>% 
  select(-row)

Output:

      A     B     C
  <int> <int> <int>
1     1     2    NA
2     4     5     6
user_133216470
Related