R/Shiny: Transpose and add data to Dataframe

Question

I'm trying to figure out a way to transpose data and then add that data to a seperate dataframe.

I have

Column 1 | Column 2| Column 3
________________________________
Data 1   | Data 2  | Data 3

And in a seperate data frame I have

actualColumn1| actualColumn2
________________________________
Column 1   | otherData 2   
Column 2   | otherData 2   
Column 3   | otherData 2

And want to get to:

actualColumn1| actualColumn2|addedData
________________________________
Column 1   | otherData 2 | Data 1  
Column 2   | otherData 2 | Data 2  
Column 3   | otherData 2 | Data 3

Since the column names from the first dataframe correspond perfectly to the rows in actualcolumn1. I apologize if I didn't explain this very well. I'm still new to R/Shiny and dataframe manipulation.

Do [this](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) and [this](https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format) answer your question? — ekoam, Dec 29 '21 at 19:56
`t()` will give you the transpose, but if you are working with data frames, you have to change it back to a data frame. (`t()` will change it to a matrix) — Kat, Dec 29 '21 at 20:04

Mr.Rlover · Accepted Answer · 2021-12-29T20:10:20.503

2

You can reshape the dataframe to have the 1 observation of 3 variables to 1 variable with 3 observations using pivot_longer(), then join to the other dataframe.

library(tidyverse)

df1 %>% 
  mutate(x = 1) %>% 
  pivot_longer(cols = -x, names_to = "actualColumn1", values_to = "addedData") %>% 
  select(-x) %>% 
  left_join(x = df2, y = .)

# Joining, by = "actualColumn1"
#    actualColumn1 actualColumn2 addedData
# 1:      Column 1   otherData 2    Data 1
# 2:      Column 2   otherData 2    Data 2
# 3:      Column 3   otherData 2    Data 3

Data:

df1 <- data.table::fread("Column 1 | Column 2| Column 3
Data 1   | Data 2  | Data 3   ")

df2 <- data.table::fread("actualColumn1| actualColumn2
Column 1   | otherData 2   
Column 2   | otherData 2   
Column 3   | otherData 2   ")

edited Dec 29 '21 at 20:10

answered Dec 29 '21 at 20:04

Mr.Rlover

2,523
3
14
32

I think this would do it but i'm having one issue `Warning: Error in : Can't join on `x$id` x `y$id` because of incompatible types. i `x$id` is of type >. i `y$id` is of type >.` Is there anyway to convert without adding to much code? – Bob Dec 29 '21 at 20:18
You need to add snippets of what your actual data is. I guessed based on your question that actualColumn1 in your other data frame contained the same values as the column names of the pretransposed dataframe. Based on your error, clearly they are not? If I am wrong, then you can simply do `mutate(id = as.double(id))` before piping into the join. – Mr.Rlover Dec 29 '21 at 20:39
It is no longer erroring out, but for some reason the "addedData" column is not being added to df2 – Bob Dec 29 '21 at 20:58
1

Have you assigned the result? `df2 <- df1 %>%... left_join(x=df2, y = .)` – Mr.Rlover Dec 29 '21 at 21:05

R/Shiny: Transpose and add data to Dataframe

1 Answers1