Transposing a tibble with dates as headers and multiple observations per date

dplyr : : cheat sheet
tidyr
tidyr spread
tidyverse
tidyr gather
transpose tibble
spread function in r
pivot_wider

I have a tibble that looks like this:

   X  Form  Jan_92 Feb_92 Mar_92 (....)
<int> <fct>  <dbl> <dbl>  <dbl>
   1  var1   2.02   2.97  0.12 
   2  var2   0.23   0.28  0.33
   3  var3   0.08   0.28  0.12

I want to tidy it so that all of the month/year combos (there are 10 years worth) are in a single column, and the var1/var2/var3 measures are column names with the respective values (corresponding to the dates).

So far I've tried transposing with t(data) but that turns it into a dataframe and I get weird row/column naming problems that I can't fix when I put it back into tibble format. Help!

We can convert to 'long' format with pivot_longer and then reshape to 'wide' with pivot_wider

library(dplyr)
library(tidyr)
df1 %>% 
   pivot_longer(cols = -c(X, Form)) %>% 
   select(-X) %>%
   group_by(Form) %>%
   mutate(rn = row_number()) %>%       
   pivot_wider(names_from = Form, values_from = value)
# A tibble: 3 x 5
#      rn name    var1  var2  var3
#  <int> <chr>  <dbl> <dbl> <dbl>
#1     1 Jan_92  2.02  0.23  0.08
#2     2 Feb_92  2.97  0.28  0.28
#3     3 Mar_92  0.12  0.33  0.12
data
df1 <- structure(list(X = 1:3, Form = c("var1", "var2", "var3"), Jan_92 = c(2.02, 
0.23, 0.08), Feb_92 = c(2.97, 0.28, 0.28), Mar_92 = c(0.12, 0.33, 
0.12)), class = "data.frame", row.names = c(NA, -3L))

Convert Factor to Date/Time in R, How do I convert a factor to a date in R? the time of the last observation, specified in the same way as start. frequency: the number of observations per unit of time. deltat: the fraction of the sampling period between successive observations; e.g., 1/12 for monthly data. Only one of frequency or deltat should be provided. ts.eps: time series comparison tolerance.

If you don't care X column (and it is just a counter), then the following works in data.table:

library(data.table)

dcast(melt(setDT(df1), id=c("X","Form"), variable.name = "datetime"), datetime ~ Form)

#>    datetime var1 var2 var3
#> 1:   Jan_92 2.02 0.23 0.08
#> 2:   Feb_92 2.97 0.28 0.28
#> 3:   Mar_92 0.12 0.33 0.12

Or another tidyverse solution:

library(dplyr)
library(tidyr)

df1 %>%
  select(-X) %>% 
  gather(datetime, value, -Form) %>%
  spread(Form, value)

# >   datetime var1 var2 var3 
# > 1   Feb_92 2.97 0.28 0.28   
# > 2   Jan_92 2.02 0.23 0.08 
# > 3   Mar_92 0.12 0.33 0.12

Using akrun's data.

Date Values, How do I convert a column to a date in R? Hi All, I have the following table with patients' admission dates at different time. They are sorted by patient_ID and then by Admission_Date patient_ID Admission_Date 101 Jan-01-2014 101 Jan-04-2014 101 Jan-09-2014 102 Jan-09-2

You can do:

as_tibble(cbind(Dates = names(df), t(df))) %>% .[-c(1:2),]

# A tibble: 3 x 4
#  Dates  V2    V3    V4   
#  <chr>  <chr> <chr> <chr>
#1 Jan_92 2.02  0.23  0.08 
#2 Feb_92 2.97  0.28  0.28 
#3 Mar_92 0.12  0.33  0.12 

Data

df <- as_tibble(read.table(header = TRUE, text = "X Form Jan_92 Feb_92 Mar_92
1 var1 2.02 2.97 0.12
2 var2 0.23 0.28 0.33
3 var3 0.08 0.28 0.12"))

Transpose R matrix object: Tips and Tricks, (x, "format"), where x is the character data and format gives the appropriate format. # A tibble: 13 x 4 opponent date fight_no result 1 José Ramírez 2013-10-12 1 Win 2 Orlando Salido 2014-03-01 2 Loss 3 Gary Russell Jr. 2014-06-21 3 Win 4 Chonlatarn Piriyapinyo 2014-11-22 4 Win 5 Gamalier Rodríguez 2015-05-02 5 Win 6 Romulo Koasicha 2015-11-07 6 Win 7 Román Martínez 2016-06-11 7 Win 8 Nicholas Walters 2016-11-26 8 Win 9

Data Tidying · Data Science with R, Throughout this book we work with “tibbles” instead of R's traditional data.frame . Another way to create a tibble is with tribble() , short for transposed tibble. tribble() is customised for data entry in code: column headings are defined by formulas A tibble: 1,000 x 5 #> a b c d e #> <dttm> <date> <int>  This guide contains written and illustrated tutorials for the statistical software SAS. This SAS software tutorial shows how to use PROC TRANSPOSE to transpose a dataset's rows and columns, and discusses when to use a transpose.

10 Tibbles, Splitting rows into multiple rows based on delimited values Working with date objects · Exploring and cleaning dataframes using base functions; Week 03 we can assign each column its own variable, and each row its own observation. A tibble: 5 x 5 Weekday Q1 Q2 Q3 Q4 <fct> <dbl> <dbl> <dbl> <dbl> 1 Mon 9.9 5.4​  dplyr rename() – For Renaming Columns In this post, we will learn about dplyr rename function.dplyr rename is used to modify dataframe column names or tibble column names. dplyr rename comes from Tidyverse group of packages developed by Hadley Wickham.

Tidying/reshaping tables using tidyr, to date and have lacked consistent coding and the ability to easily flow together​. Description: Many times a single column variable will capture multiple In each of these cases, our objective may be to separate characters within the In essence, it combines two variables of a single observation into one variable. Today lets tackle a familiar data clean-up problem using Excel - Transposing data. That is, we want to take all rows in our data & make them columns. Something like this: Learn these 4 techniques to transpose data: 1. Using Paste Special > Transpose 2. Using INDEX formula & Helper cells 3. Using INDEX, ROWS & COLUMNS formulas 4. Using TRANSPOSE Formula

Comments
  • it does. Thanks. I have this syntax in one of my files with a comment saying credit goes to you: recast(df1, id.var = 1:2, variable ~ Form) you may wanna add it to your answer.