PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Tuesday, November 1, 2022

[FIXED] How to pivot a data set longer when columns have no common type

 November 01, 2022     dataframe, dplyr, pivot, r, tidyr     No comments   

Issue

How do you pivot the first dataset to look like the 2nd?

I'm trying to use the dplyr pivot_longer function without any luck. Below is a sample data set that mirrors my much larger dataset with 50 plus columns.

https://tidyr.tidyverse.org/reference/pivot_longer.html


library(tidyverse)

df1
#> # A tibble: 9 x 6
#>   column_label  val1  val2  cat1  cat2 cat3             
#>          <dbl> <dbl> <dbl> <dbl> <dbl> <chr>            
#> 1            2 0.989  9.89     0    NA <NA>             
#> 2            2 0.622  6.22     1    NA <NA>             
#> 3            3 0.619  6.19    NA     0 <NA>             
#> 4            3 0.119  1.19    NA     1 <NA>             
#> 5           10 0.407  4.07    NA    NA BABY BOOMERS     
#> 6           10 0.800  8.00    NA    NA GEN Z            
#> 7           10 0.305  3.05    NA    NA GENERATION X     
#> 8           10 0.158  1.58    NA    NA MILLENNIALS      
#> 9           10 0.439  4.39    NA    NA SILENT GENERATION

# how do you pivot_longer to create this data set?

df2
#> # A tibble: 9 x 5
#>   column_label  val1  val2 variables Values           
#>          <dbl> <dbl> <dbl> <chr>     <chr>            
#> 1            2 0.989  9.89 cat1      0                
#> 2            2 0.622  6.22 cat1      1                
#> 3            3 0.619  6.19 cat2      0                
#> 4            3 0.119  1.19 cat2      1                
#> 5           10 0.407  4.07 cat3      BABY BOOMERS     
#> 6           10 0.800  8.00 cat3      GEN Z            
#> 7           10 0.305  3.05 cat3      GENERATION X     
#> 8           10 0.158  1.58 cat3      MILLENNIALS      
#> 9           10 0.439  4.39 cat3      SILENT GENERATION

Data

df1 <- structure(list(column_label = c(2, 2, 3, 3, 10, 10, 10, 10, 10
), val1 = c(0.989049526, 0.622384581, 0.618576065, 0.11864823, 
            0.406763475, 0.799564365, 0.3053153, 0.158456912, 0.438528606
), val2 = c(9.890495264, 6.223845807, 6.185760647, 1.186482297, 
            4.067634747, 7.995643651, 3.053153001, 1.584569123, 4.385286057
), cat1 = c(0, 1, NA, NA, NA, NA, NA, NA, NA), cat2 = c(NA, NA, 
                                                        0, 1, NA, NA, NA, NA, NA), cat3 = c(NA, NA, NA, NA, "BABY BOOMERS", 
                                                                                            "GEN Z", "GENERATION X", "MILLENNIALS", "SILENT GENERATION")), class = c("spec_tbl_df", 
                                                                                                                                                                     "tbl_df", "tbl", "data.frame"), row.names = c(NA, -9L), spec = structure(list(
                                                                                                                                                                       cols = list(column_label = structure(list(), class = c("collector_double", 
                                                                                                                                                                                                                              "collector")), val1 = structure(list(), class = c("collector_double", 
                                                                                                                                                                                                                                                                                "collector")), val2 = structure(list(), class = c("collector_double", 
                                                                                                                                                                                                                                                                                                                                  "collector")), cat1 = structure(list(), class = c("collector_double", 
                                                                                                                                                                                                                                                                                                                                                                                    "collector")), cat2 = structure(list(), class = c("collector_double", 
                                                                                                                                                                                                                                                                                                                                                                                                                                      "collector")), cat3 = structure(list(), class = c("collector_character", 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        "collector"))), default = structure(list(), class = c("collector_guess", 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              "collector")), skip = 1), class = "col_spec"))

df2 <- structure(list(column_label = c(2, 2, 3, 3, 10, 10, 10, 10, 10
), val1 = c(0.989049526, 0.622384581, 0.618576065, 0.11864823, 
            0.406763475, 0.799564365, 0.3053153, 0.158456912, 0.438528606
), val2 = c(9.890495264, 6.223845807, 6.185760647, 1.186482297, 
            4.067634747, 7.995643651, 3.053153001, 1.584569123, 4.385286057
), variables = c("cat1", "cat1", "cat2", "cat2", "cat3", "cat3", 
                 "cat3", "cat3", "cat3"), Values = c("0", "1", "0", "1", "BABY BOOMERS", 
                                                     "GEN Z", "GENERATION X", "MILLENNIALS", "SILENT GENERATION")), class = c("spec_tbl_df", 
                                                                                                                              "tbl_df", "tbl", "data.frame"), row.names = c(NA, -9L), spec = structure(list(
                                                                                                                                cols = list(column_label = structure(list(), class = c("collector_double", 
                                                                                                                                                                                       "collector")), val1 = structure(list(), class = c("collector_double", 
                                                                                                                                                                                                                                         "collector")), val2 = structure(list(), class = c("collector_double", 
                                                                                                                                                                                                                                                                                           "collector")), variables = structure(list(), class = c("collector_character", 
                                                                                                                                                                                                                                                                                                                                                  "collector")), Values = structure(list(), class = c("collector_character", 
                                                                                                                                                                                                                                                                                                                                                                                                      "collector"))), default = structure(list(), class = c("collector_guess", 
                                                                                                                                                                                                                                                                                                                                                                                                                                                            "collector")), skip = 1), class = "col_spec"))

Created on 2020-03-13 by the reprex package (v0.3.0)


Solution

The columns you select in pivot_longer have no common type, i.e. cat1 and cat2 are numerics and cat3 is character. You can convert them all to characters in advance, or use the argument values_ptypes to specify the type.

df1 %>%
  pivot_longer(cat1:cat3,
               names_to = 'variables', values_to = 'Values',
               values_drop_na = TRUE,
               values_ptypes = list(Values = character()))

# # A tibble: 9 x 5
#   column_label  val1  val2 variables Values           
#          <dbl> <dbl> <dbl> <chr>     <chr>            
# 1            2 0.989  9.89 cat1      0                
# 2            2 0.622  6.22 cat1      1                
# 3            3 0.619  6.19 cat2      0                
# 4            3 0.119  1.19 cat2      1                
# 5           10 0.407  4.07 cat3      BABY BOOMERS     
# 6           10 0.800  8.00 cat3      GEN Z            
# 7           10 0.305  3.05 cat3      GENERATION X     
# 8           10 0.158  1.58 cat3      MILLENNIALS      
# 9           10 0.439  4.39 cat3      SILENT GENERATION


Answered By - Darren Tsai
Answer Checked By - Clifford M. (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing