Pivoting 4x4 into 16x1

0 votes

I've been looking for a method to combine a dataset with four columns into one row. I'll give an illustration of what I mean.

enter image description here

I want this dataset to be transformed into...

enter image description here

My issue is that when I try to utilise Excel's pivot function, it won't let me pivot the data into a single row. I've also tried a tonne of different Excel functions, but I haven't had any success.

The data is currently in Excel, where it would probably be simpler to alter it. However, R would function just as well if Excel only lacked that feature. Thank you for your help.

Jan 30, 2023 in Others by Kithuzzz
• 38,010 points
220 views

1 answer to this question.

0 votes

Try this:

df <- tibble::tribble(
  ~ID, ~sit1, ~sit2, ~sit3, ~sit4,
  1,   -1,    2,    0,    0,
  2,   -3,   -2,   -3,   -2,
  3,   -1,    2,    0,    4,
  4,   -1,    2,    0,    0 
)

library(tidyr)

col_order <- expand.grid(names(df)[-1], df$ID) |> 
  unite("Var", c(Var1, Var2))

df |> 
  pivot_wider(names_from = ID, values_from = starts_with("sit")) |> 
  dplyr::relocate(col_order$Var)

# A tibble: 1 × 16
  sit1_1 sit2_1 sit3_1 sit4_1 sit1_2 sit2_2 sit3_2 sit4_2 sit1_3 sit2_3 sit3_3
   <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1     -1      2      0      0     -3     -2     -3     -2     -1      2      0
# … with 5 more variables: sit4_3 <dbl>, sit1_4 <dbl>, sit2_4 <dbl>, sit3_4 <dbl>,
#   sit4_4 <dbl>
answered Jan 30, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
475 views
0 votes
1 answer

How do I log into edureka Master and slave Virtual Machine

Hey @Adetayo, If you are asking about ...READ MORE

answered Apr 1, 2020 in Others by Sirajul
• 59,230 points
830 views
0 votes
1 answer

Reverse back functional principle components into original data

Hi@Faheem, It depends on your Dataset. You have ...READ MORE

answered Oct 21, 2020 in Others by MD
• 95,440 points
342 views
0 votes
1 answer
0 votes
1 answer

Download multiple excel files linked through urls in R

Try something along the lines of: for (i ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,700 points
1,069 views
0 votes
1 answer

Excel, How to split cells by comma delimiter into new cells

The Excel manual method: choose Text to Column ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
671 views
0 votes
1 answer

Comparing two Excel files in R and returning different titles

Solution: library(tidyverse) dat <- read_xlsx("Book1.xlsx") dat2 <- read_xlsx("Book2.xlsx") book1_output <- anti_join(dat,dat2, ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,700 points
1,532 views
0 votes
1 answer

Conversion of PDF file to Excel in R

I looked at the pdf, and it ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,700 points
1,367 views
0 votes
1 answer

Excel web query to login into a website

To be recognized by the web server ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,700 points
2,420 views
0 votes
1 answer

How to get the JasperReports into Excel format with proper alignment

Remove at least one parameter: exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN,Boolean.TRUE) Note that JRXlsExportParameter is deprecated the correct ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,700 points
3,256 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP