CODEWITHSUNDEEP
r
LEE
LEE

Reputation: 328

Data conversion using tidyverse?

I have a data frame like below,

all_long<- structure(list(site = c("DK_B", "DK_M", "HU", "IT", "NL", "UK", 
"DK_B", "DK_M", "HU", "IT", "NL", "UK", "DK_B", "DK_M", "HU", 
"IT", "NL", "UK"), group = c("NEE_D", "NEE_D", "NEE_D", "NEE_D", 
"NEE_D", "NEE_D", "NEE_D_se", "NEE_D_se", "NEE_D_se", "NEE_D_se", 
"NEE_D_se", "NEE_D_se", "NEE_W", "NEE_W", "NEE_W", "NEE_W", "NEE_W", 
"NEE_W"), y = c(21.3096546929104, 32.9239223290104, -9.76133775037159, 
-1.17370950853892, -10.950117466455, 84.8932664133039, 17.4144184458657, 
21.4374932407618, 28.7765100449838, 8.86774764999355, 42.0054504158082, 
121.363143982517, 29.8709775563496, -28.2034713980476, -62.2858574112861, 
56.3557615426375, 52.3723812566745, 2.26453931798947)), row.names = c(NA, 
18L), class = "data.frame")

I want to convert the data frame to a new one like below,

site   group      y                  SE
DK_B   NEE_D  21.3096546929104     17.414418
DK_M   NEE_D  32.9239223290104     21.437493
HU     NEE_D  -9.76133775037159    28.77651
IT     NEE_D  -1.17370950853892    8.867747
NL     NEE_D  -10.950117           42.005450
UK     NEE_D  84.893266           121.363144
DK_B   NEE_W    29.9               15.3 
DK_M   NEE_W   -28.2                39.5 
HU     NEE_W   -62.3               19.2 
IT     NEE_W   56.4                25.6 



......

I don't know how to realize this?

Upvotes: 0

Views: 40

Answers (2)

dario
dario

Reputation: 6485

Does this work for you?

1.To me it looks like the simplest approach was to just bind the SE values to the existing data. So we take all the rows containing the SE data and rename the column y to SE and get rid of the no longer needed group variable and then join them to the data without the rows containing the SE data.

  library(dplyr)
  library(tidyr)
  all_long %>%
  filter(!grepl("se$", group)) %>% 
  left_join(all_long %>% filter(grepl("se$", group)) %>% rename(SE = y) %>% select(-group))
  
  Returns:
  

    site group          y         SE
  1 DK_B NEE_D  21.309655  17.414418
  2 DK_M NEE_D  32.923922  21.437493
  3   HU NEE_D  -9.761338  28.776510
  4   IT NEE_D  -1.173710   8.867748
  5   NL NEE_D -10.950117  42.005450
  6   UK NEE_D  84.893266 121.363144

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388907

Here is a possible approach with tidyverse -

library(tidyverse)

all_long %>%
  mutate(col = str_extract(group, 'se'),
         col = replace_na(col, 'y'),
         group = trimws(str_remove(group, col), whitespace = '_')) %>%
  pivot_wider(names_from = col, values_from = y)

#  site  group      y     se
#  <chr> <chr>  <dbl>  <dbl>
#1 DK_B  NEE_D  21.3   17.4 
#2 DK_M  NEE_D  32.9   21.4 
#3 HU    NEE_D  -9.76  28.8 
#4 IT    NEE_D  -1.17   8.87
#5 NL    NEE_D -11.0   42.0 
#6 UK    NEE_D  84.9  121.

Create a new column with 'se' text from group if present or replace it with 'y'. Clean up the group column to leave it with only the value 'NEE_D' and get data in wide format.

Upvotes: 2

Related Questions