Excel text to columns in R

Posted on: October 25, 2017 Posted by: Guy Manova Comments: 0

Excel text to columns in R

Excel text to columns in R

This is short tutorial on how to mimic Excel text to columns functionality in R, in order to parse a character column to separate text strings

 

in Excel:

Excel text to columns in R

Excel text to columns in R

Excel text to columns in R

Excel text to columns in R

 

in R:

# pre-session options

rm(list = ls())
#getwd()
#setwd(“C:\\Users\\User\\Documents”)

install and load stringr

if (!require(stringr)) install.packages(‘stringr’)
library(stringr)

load and write the dataframe:

campaigns <- read.csv(“https://www.dropbox.com/s/gv83asx8qeuog0z/google%20campaigns.csv?dl=1”)
write.csv(campaigns, “campaigns.csv”)

# first do a simple split in order to understand how it looks:

str_split(“Android_Search_UK”, “_”) # splitting the text on underscore

Excel text to columns in R

# performing excel “text to column” to extract country this creates a LIST of split elements:

split <- str_split(campaigns$Campaign, “_”)

# take a look at the list:

split

Excel text to columns in R

# this is a list of vectors. each list element “horizontally” is a charecter vector:

class(split[[1]])
is.vector(split[[1]])

Excel text to columns in R

# now let’s extract the list items vertically to proper columns in the data set:

campaigns$network <- sapply(split, “[[“, 1)
campaigns$platform <- sapply(split, “[[“, 2)
campaigns$country <- sapply(split, “[[“, 3)
campaigns$startDate <- sapply(split, “[[“, 4)
campaigns$campaignType <- sapply(split, “[[“, 5)

 

# now add as new column:

campaigns

Excel text to columns in R

Excel text to columns in R

 

Leave a Reply:

Your email address will not be published. Required fields are marked *