CODEWITHSUNDEEP
rtranspose
SHWETA JAIN
SHWETA JAIN

Reputation: 11

What if I want to transpose values in multiple columns based on unique names in another column?

Suppose I have dataframe containing columns A, B, C and D. I now want to transpose values in columns B, C and D based on unique values in column A.

Dataframe:

Key Date Occ Amount
123 03-04-18 45000
345 31-12-18 92045
123 17-04-18 2400
345 04-07-19 1045

I would require:

Key Date Occ1 Amount1 Date Occ2 Amount2
123 03-04-18 45000 17-04-18 2400
345 31-12-18 92045 04-07-19 1045

Upvotes: 1

Views: 91

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 269870

1) First add a name column to identify the first and second occurrence of each Key, use pivot_wider with multiple values_from columns and finally rearrange the columns to be as shown. The last line could be omitted if the order of the columns is not important. The names_sep = "" argument could be omitted if the default of separating the final number in the column names from the prefix using underscore is acceptable.

library(dplyr)
library(tidyr)

DF %>%
  group_by(Key) %>%
  mutate(name = 1:n()) %>%
  ungroup %>%
  pivot_wider(values_from = c(`Date Occ`, Amount), names_sep = "") %>%
  select(Key, `Date Occ1`, Amount1, `Date Occ2`, Amount2)

giving

# A tibble: 2 × 5
    Key `Date Occ1` Amount1 `Date Occ2` Amount2
  <int> <chr>         <int> <chr>         <int>
1   123 03-04-18      45000 17-04-18       2400
2   345 31-12-18      92045 04-07-19       1045

2) If the first instances of each key come at the beginning and the second instance at the end then this base R code could be used.

nr <- nrow(DF)
merge(head(DF, nr/2), tail(DF, nr/2), by = 1, suffixes = 1:2)

giving:

  Key Date Occ1 Amount1 Date Occ2 Amount2
1 123  03-04-18   45000  17-04-18    2400
2 345  31-12-18   92045  04-07-19    1045

Note

The input in reproducible form

DF <-
structure(list(Key = c(123L, 345L, 123L, 345L), `Date Occ` = c("03-04-18", 
"31-12-18", "17-04-18", "04-07-19"), Amount = c(45000L, 92045L, 
2400L, 1045L)), class = "data.frame", row.names = c(NA, -4L))

Upvotes: 1

Related Questions