wedding_wire.R

gistfile1.txt

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')