String as formula

  • A+
Category:Languages

I've tried searching through the forums and unable to find help. I'm quite new to R, and am having limited success in loading in some strings to be used as a formula.

I have a csv with the following format:

            Sam, Frank, Dennis, Steven, Robert, Georgia Region_1    218,    763,    811,    812,    797,    574 Region_2    474,    983,    343,    697,    310,    335 Region_3    700,    813,    133,    212,    906,    680 Region_4    212,    581,    893,    514,    530,    795 

And am able to load this in and add additional columns to my data frame that adds specific columns (ie. Sam + Frank, Dennis + Georgia).

I can do it if I hard code the teams into my code, eg:

temp <- mutate(temp, team_1 = Sam + Robert)  

My issue is that I have another csv that contains a list of 2 columns, and these are teams that changes daily, eg:

Team 1  Sam + Robert Team 2  Frank + Georgia Team 3  Frank + Steven Team 4  Robert + Dennis Team 5  Frank + Sam 

Is there a way that I can load this csv list into R and add these team member columns together.

But I can't see to add this list in from the csv file and have a new data frame with the total of team members dynamically.

Is anyone able to help me with this?

I'm looking to end up with something like this (with the sum of the team members (team 1, team 2, etc from the second csv file) as additional columns:

         Sam,   Frank,  Dennis, Steven, Robert, Georgia,    Sam + Robert,   Frank + Georgia,    Frank + Steven, Robert + Dennis,    Frank + Sam  Region_1   218,    763,    811,    812,    797,    574,    981,    1337,   1575,   1608,   981  Region_2   474,    983,    343,    697,    310,    335,    1457,   1318,   1680,   653,    1457  Region_3   700,    813,    133,    212,    906,    680,    1513,   1493,   1025,   1039,   1513  Region_4   212,    581,    893,    514,    530,    795,    793,    1376,   1095,   1423,   793 

With much thanks.

 


Working with code as strings is sort of complicated. Here, it's simpler to turn the second data frame into a lookup table which can be joined to the first reshaped to long form. The result can then be summarized and reshaped into whatever form you'd like.

library(tidyverse)  people <- data.frame(     region = c("Region_1", "Region_2", "Region_3", "Region_4"),     Sam = c(218L, 474L, 700L, 212L),     Frank = c(763L, 983L, 813L, 581L),     Dennis = c(811L, 343L, 133L, 893L),     Steven = c(812L, 697L, 212L, 514L),     Robert = c(797L, 310L, 906L, 530L),     Georgia = c(574L, 335L, 680L, 795L),      stringsAsFactors = FALSE )  teams <- data.frame(     team = c("Team 1", "Team 2", "Team 3", "Team 4", "Team 5"),     members = c("Sam + Robert", "Frank + Georgia", "Frank + Steven", "Robert + Dennis", "Frank + Sam"),      stringsAsFactors = FALSE )  teams_long <- teams %>%      mutate(team = members) %>%    # set team name     separate_rows(members)    # separate teams to names  people %>%      gather(member, value, -region) %>%    # reshape to long form     left_join(teams_long, c('member' = 'members')) %>%    # join by names     group_by(region, team) %>%      summarise(value = sum(value)) %>%    # aggregate     spread(team, value) %>%    # reshape to wide form     left_join(people, .)    # join back to original data #>     region Sam Frank Dennis Steven Robert Georgia Frank + Georgia #> 1 Region_1 218   763    811    812    797     574            1337 #> 2 Region_2 474   983    343    697    310     335            1318 #> 3 Region_3 700   813    133    212    906     680            1493 #> 4 Region_4 212   581    893    514    530     795            1376 #>   Frank + Sam Frank + Steven Robert + Dennis Sam + Robert #> 1         981           1575            1608         1015 #> 2        1457           1680             653          784 #> 3        1513           1025            1039         1606 #> 4         793           1095            1423          742 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: