2

all.

Very new to R and working with data more generally. Have also never written anything in Stack Overflow or similar, so please forgive any dodgy formatting or terminology.

I have a dataframe that needs converting from long to wide format ready for a paired T test. The data frame is:

> head(df)
# A tibble: 6 x 13
  Assessor Product               Ap    Ar       F       T
     <dbl> <chr>               <dbl> <dbl>   <dbl>   <dbl>
1        1 MC                    10    10      10      10
2        1 MV                    10    10      10      10
3        2 MC                     6     7       8       8
4        2 MV                     7     5       4       8
5        3 MV                     9     9      10       9
6        3 MC                     6     8       7       6
# ... with 7 more variables:
#   C1 <dbl>,
#   JCo <dbl>,
#   JSt1 <dbl>,
#   JSt2 <dbl>,
#   JSw <dbl>,
#   JCr <dbl>,
#   OA <dbl>

I need to put it in the following wide format but for all variables, where each column after Product is converted into two columns, one for each product (MC & MV):

  Assessor  Ap_MC  Ap_MV  Ar_MC   Ar_MV      
     <dbl>   <dbl>  <dbl>  <dbl>   <dbl>  
1        1    10     10      10      10   
2        2     6      7       7       5    
3        3     9      6       9       8

The headings don't strictly need to be these, but need to differentiate that they are grouped by the different products as well as the original column.

I've seen various approaches to converting between long and wide format on here, but I can't make any work. This was an attempt I made using pivot_wider() (error message included):

df <- pivot_wider(df, id_cols = df[1], names_from = df[2], values_from = df[3:13])

Error: Must subset columns with a valid subscript vector.
x Subscript has the wrong type `tbl_df<Product:character>`.
i It must be numeric or character.
Run `rlang::last_error()` to see where the error occurred.

I have no idea what's causing this error, let alone if my attempt was anywhere close to giving me the output I want.

Thank you in advance!

Dee G
  • 133
  • 9
  • I think this has been answered here: https://stackoverflow.com/questions/68043121/collapse-duplicated-rows-with-different-values-in-different-columns-using-r/68043732#68043732 – Zaw Jun 21 '21 at 15:22

1 Answers1

0

Your solution is actually very close! The only thing is that pivot_wider is smart enough to just accept the column names as inputs. In other words, rather than passing df[1] (the actual column) as the id_cols, we should just pass the name of the column Assessor.

df = data.frame(Assessor=c(1,1,2,2), Product=c('MC', 'MV', 'MC', 'MV'), App=c(1,2,3, 3), Ar=c(4,5,6, 6))

pivot_wider(df, id_cols = Assessor, names_from = Product, values_from = c('App', 'Ar'))

  Assessor  Ap_MC  Ap_MV  Ar_MC   Ar_MV      
     <dbl>   <dbl>  <dbl>  <dbl>   <dbl>  
1        1    10     10      10      10   
2        2     6      7       7       5

In general, when working with functions from the tidyverse, you refer to columns by name (usually unquoted).

Interpreting the error:

We got a hint of the problem in the error message. You can see that the error has something to do with the way we selected our columns (subset columns with valid subscript vector). It tells us that our subscript was of the type tbl_df (it even tells us the name "Product"), when really what it's expecting is either a numeric (column index) or character (column name)

P.S. your formatting was great.

lkleinbrodt
  • 94
  • 4
  • Thank you for the response. Is there no straightforward way to do it using column number references? I know I only have 13 columns and most of the variables are very short, but this still seems inefficient and error-prone, especially in cases of more columns and/or longer names. – Dee G Jun 21 '21 at 17:50
  • Sure, you can refer to columns either by name or by index. So in this case you could use `...values_from = 3:ncol(df)` – lkleinbrodt Jun 21 '21 at 17:56