How do I group data with the same date into one row in R program

Question

How can I construct the data to be a triangle method in R

I have done theses manually but I have huge data if there is any code that can be developed

    dev
origin    1    2    3    4
  2005 1500 2420 2720 3020
  2006 1150 1840 2070   NA
  2007 1650 2640   NA   NA
  2008 1740   NA   NA   NA

... for [these reasons](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question/285557#285557) - and [these](https://xkcd.com/2116/). — Limey, Sep 14 '22 at 07:26
The question is a duplicate of [reshape data from long to wide in R](https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format) — Rui Barradas, Sep 14 '22 at 07:36

score 2 · Answer 1 · answered Sep 14 '22 at 07:36

Here's a tidyverse solution.

First, create the data, as you haven't done so.

library(tidyverse)

d <- tibble(
       Year=c(rep(2005, 4), rep(2006, 3), rep(2007, 2), 2008),
       Amount=c(1500, 2420, 2720, 3020, 1150, 1840, 2070, 1650, 2640, 1740)
     )

Now create an index within year. We will need this later.

d %>% 
  group_by(Year) %>% 
  mutate(Index=1:n())
# A tibble: 10 × 3
# Groups:   Year [4]
    Year Amount Index
   <dbl>  <dbl> <int>
 1  2005   1500     1
 2  2005   2420     2
 3  2005   2720     3
 4  2005   3020     4
 5  2006   1150     1
 6  2006   1840     2
 7  2006   2070     3
 8  2007   1650     1
 9  2007   2640     2
10  2008   1740     1

Now pivot_wider into the required format.

d %>% 
  group_by(Year) %>% 
  mutate(Index=1:n()) %>% 
  pivot_wider(
    names_from=Index,
    values_from=Amount
  )
# A tibble: 4 × 5
# Groups:   Year [4]
   Year   `1`   `2`   `3`   `4`
  <dbl> <dbl> <dbl> <dbl> <dbl>
1  2005  1500  2420  2720  3020
2  2006  1150  1840  2070    NA
3  2007  1650  2640    NA    NA
4  2008  1740    NA    NA    NA

Finally fix the column name and remove the grouping.

d %>% 
  group_by(Year) %>% 
  mutate(Index=1:n()) %>% 
  pivot_wider(
    names_from=Index,
    values_from=Amount
  ) %>% 
  rename(origin=Year) %>% 
  ungroup()
# A tibble: 4 × 5
  origin   `1`   `2`   `3`   `4`
   <dbl> <dbl> <dbl> <dbl> <dbl>
1   2005  1500  2420  2720  3020
2   2006  1150  1840  2070    NA
3   2007  1650  2640    NA    NA
4   2008  1740    NA    NA    NA

How do I group data with the same date into one row in R program

1 Answers1