join data frames row values with columns

Question

ID <- c('1', '2', '3', '4')
Field_Name <- c('Location', 'Status', 'Company', 'Priority')
Field_Value <- c('Arizona', 'New', 'MyCompany', 'Urgent')

df_1<-data.frame(ID, Field_Name, Field_Value)

field_name_v <- as.factor(df_1$Field_Name)

custom_fields_df2 <- data.frame(matrix(ncol =((length(field_name_v))), 
                                   nrow = (nrow(df_1))))
colnames(custom_fields_df2) <- field_name_v
custom_fields_df2$ID <-ID

This may seem like a weird ask, but it is needed for a rather large dataset. I know this is probably super simple but I cannot figure it out. If you look at df_1 it has the Field_Name value which denotes a custom field added by users. In the custom_fields_df2 I am trying to create a horizontal chart where each unique value in the df_1$Field_Name is a column and the Field_Value is joined in under the column where the Field_Name = column name and ID matches. Any help would be greatly appreciated.

score 0 · Accepted Answer · answered Jan 06 '22 at 04:57

It looks like you want to reshape (join has a different connotation) your dataframe from long format to wide format. The package dplyr from tidyverse has a convenient function for this. You can read about it here and also try other suggested solutions.

Here is my go at your problem:


    library(tidyverse)
    
    df_1 <- data.frame(ID = c('1', '2', '3', '4'), 
                       Field_Name = c('Location', 'Status', 'Company', 'Priority'), 
                       Field_Value = c('Arizona', 'New', 'MyCompany', 'Urgent'))
    
    custom_fields_df2 <- df_1 %>% 
      pivot_wider(names_from = Field_Name, values_from = Field_Value)

This produces:


    > custom_fields_df2
    # A tibble: 4 x 5
      ID    Location Status Company   Priority
      <chr> <chr>    <chr>  <chr>     <chr>   
    1 1     Arizona  NA     NA        NA      
    2 2     NA       New    NA        NA      
    3 3     NA       NA     MyCompany NA      
    4 4     NA       NA     NA        Urgent

I am not sure what purpose exactly your column ID serves, but it seems redundant to me so I remove it:


    custom_fields_df3 <- df_1 %>% 
      select(-ID) %>% 
      pivot_wider(names_from = Field_Name, values_from = Field_Value)

to get:


    > custom_fields_df3
    # A tibble: 1 x 4
      Location Status Company   Priority
      <chr>    <chr>  <chr>     <chr>   
    1 Arizona  New    MyCompany Urgent

The tibble can be converted to a dataframe if required using as.data.frame().

@AndrewR.McPherson If the answer resolved your issue, please consider "accepting" it. See [here](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) for more information. — Karthik Thrikkadeeri, Jan 06 '22 at 05:50
I actually had to modify quite a bit because i did need the ID(and some other fields), but for what i posted your solution works and it put me on the track to my solution. — Andrew R. McPherson, Jan 06 '22 at 18:11

join data frames row values with columns

1 Answers1