Skip to content

Instantly share code, notes, and snippets.

@jonrobinson2
Created February 28, 2017 16:42
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jonrobinson2/c8f2913d32bf65d91d0fd905bfc22ffa to your computer and use it in GitHub Desktop.
Save jonrobinson2/c8f2913d32bf65d91d0fd905bfc22ffa to your computer and use it in GitHub Desktop.
wedding_wire.R
remove(list=ls())
require(googlesheets)
require(dplyr)
require(stringr)
#options(error = expression(NULL))
list_sheets=gs_ls()
wedding_list=gs_title("Jonarobin Master Wedding List")
# df <- data.frame(wedding_list %>%
# gs_read(ws = "Guest List"), stringsAsFactors = F)
# for(u in unique(df$Address.Name[!is.na(df$Address.Name)])){
#
# if(!is.na(u)){
#
# if(any(which(df$Address.Name==u)==1) & (sqldf::sqldf(paste0("select count(*), \"Address.Name\" from df where \"Address.Name\"='",u,"' group by 2")))[,1]==1){
#
# df$Household.ID[df$Address.Name==u & !is.na(df$Address.Name)]=1
#
# }else{
#
# df$Household.ID[df$Address.Name==u & !is.na(df$Address.Name)]=(max(df$Household.ID, na.rm = T))+1
#
# }
# }
# }
#
# df$Guest.ID=1:nrow(df)
#
# dim(na.omit(df[,!(names(df) %in% c('Guest ID','Household.ID','Address'))]))
# cbind(table(df$Invite.List))
#
# gs_edit_cells(ss=wedding_list,ws = 'Guest List',verbose = T,input = df$Household.ID,col_names = F,anchor = 'B2',byrow = FALSE,trim = F)
#
# gs_edit_cells(ss=wedding_list,ws = 'Guest List',verbose = T,input = df$Guest.ID,col_names = F,anchor = 'A2',byrow = FALSE,trim = F)
##################
# df <- data.frame(wedding_list %>%
# gs_read(ws = "Guest List"), stringsAsFactors = F)
#
# addresses=read.csv('~/Downloads/GuestsWeddingWire02.12.2017.csv', skip = 1, stringsAsFactors = F)
# names(addresses)=gsub('\\.','_',names(addresses))
#
# addresses=addresses[addresses$Household__Address1!='',]
# row.names(addresses)=1:nrow(addresses)
#
# addresses$Household_Address_Comb=paste0(str_trim(addresses$Household__Address1)," ",str_trim(addresses$Household__Address2)," ",str_trim(addresses$Household__City),", ",str_trim(addresses$Household__Country_State)," ",str_trim(addresses$Household__Postal_Code))
#
# addresses$Household_Address_Comb=gsub('apt','Apt', addresses$Household_Address_Comb)
# addresses$Household_Address_Comb=gsub(' ',' ',addresses$Household_Address_Comb)
# addresses$Household_Address_Comb=gsub('york','York',addresses$Household_Address_Comb)
#
# char_add=str_split(addresses$Household_Address_Comb,' ')
# for(s in 1:length(char_add)){
#
# char_flag=is.na(as.numeric(char_add[[s]][1]))
#
# if(char_flag){
#
# message(addresses$Household_Address_Comb[s])
#
# char_add[[s]]<-char_add[[s]][which(!is.na(as.numeric(char_add[[s]])))[1]:length(char_add[[s]])]
#
# addresses$Household_Address_Comb[s]=paste0(char_add[[s]],collapse=' ')
#
# }
#
# }
#
# df=merge(df, addresses, by.x='Address.Name',by.y='Household__Name', all=T)
#
# df$Household_Address_Comb[is.na(df$Household_Address_Comb)]=df$Address
#
# gs_edit_cells(ss=wedding_list,ws = 'Guest List',verbose = T,input = df$Household_Address_Comb[order(df$Guest.ID)],col_names = F,anchor = 'G2',byrow = FALSE,trim = F)
##################
df <- data.frame(wedding_list %>%
gs_read(ws = "Guest List"), stringsAsFactors = F)
df$PaperInThePark=paste0(df$Address.Name," \n",df$Address)
pitp=data.frame(address=unique(df$PaperInThePark), stringsAsFactors = F)
# openxlsx::write.xlsx(x = pitp, file = '~/Downloads/robinson_janofsky_weddinginvitation_addresslist_20170214_v5.xlsx')
for(add in unique(df$Address.Name)){df$Count[df$Address.Name==add]=1:nrow(df[df$Address.Name==add,])}
####################
gnp=openxlsx::read.xlsx('~/Downloads/guestnameparsing.xlsx')
df$GivenName=gnp$GivenName
df$SurName=gnp$Surname
df2=merge(reshape2::dcast(df,`Address.Name`+`Invite.List`~Count, value.var='GivenName'),
reshape2::dcast(df,`Address.Name`+`Invite.List`~Count, value.var='SurName'), by=c('Address.Name','Invite.List'), suffixes = c('firstname','lastname'))
df2$Address.Name=gsub('\\\n',' ',df2$Address.Name)
write.table(df2, file='~/Downloads/jonarobinweddingwireguestlist.csv', sep = ',',row.names = F, na = '',fileEncoding = 'UTF-8')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment