CODEWITHSUNDEEP
r
Brian A
Brian A

Reputation: 11

Right Justify Column in R

I'm trying to align my "Viewability" and "Fraud" columns to the right hand side of the cell, but I can't seem to find an easy way to do this. I've tried several different options but everything returns errors. Please see my code below:

library(tidyverse)
library(xlsx)
library(openxlsx)
library(data.table)
library(taskscheduleR)

#Create Workbook

workbook <- createWorkbook()
addWorksheet(workbook, "Viewability")
addWorksheet(workbook, "Fraud")

#Read Files and Create Data

files <- file.info(Sys.glob("C:/Users/briantos/Documents/Blacklist Raw/*.csv"))
newestfile <- row.names(files)[which.max(files[["ctime"]])]
blacklist <- fread(newestfile)

#Create Viewability Data

blacklist_viewability <- blacklist %>% 
  select(`Delivery Site`, `Video Viewable Impressions`, `Measured Impressions`) %>% 
  group_by(`Delivery Site`) %>% 
  summarize(Sum_of_Measured_Impressions = sum(`Measured Impressions`, na.rm = TRUE),
            Sum_of_Video_Viewable_Impressions = sum(`Video Viewable Impressions`, na.rm = TRUE)) %>% 
  mutate(Viewability = `Sum_of_Video_Viewable_Impressions`/`Sum_of_Measured_Impressions`) %>%
  arrange(Viewability, desc(Sum_of_Measured_Impressions)) %>% 
  select(-Sum_of_Video_Viewable_Impressions) %>% 
  mutate(Viewability = paste(round(100*Viewability, 2), "%", sep=""))

#Create Fraud Data

blacklist_fraud <- blacklist %>% 
  select(`Delivery Site`, `Fraud/SIVT Incidents`, `Monitored Impressions`) %>% 
  group_by(`Delivery Site`) %>% 
  summarize(Sum_of_Monitored_Impressions = sum(`Monitored Impressions`, na.rm = TRUE),
            Sum_of_Incidents = sum(`Fraud/SIVT Incidents`, na.rm = TRUE)) %>% 
  mutate(Fraud = `Sum_of_Incidents`/`Sum_of_Monitored_Impressions`) %>%
  arrange(desc(Fraud), desc(Sum_of_Monitored_Impressions)) %>% 
  select(-Sum_of_Incidents) %>% 
  mutate(Fraud = paste(round(100*Fraud, 2), "%", sep=""))

#Turn Into Data Frame

blacklist_viewability <- as.data.frame(blacklist_viewability)
blacklist_fraud <- as.data.frame(blacklist_fraud)

#Write Data into Workbook

writeData(workbook, sheet = "Viewability", blacklist_viewability)
writeData(workbook, sheet = "Fraud", blacklist_fraud)

#Set Column Widths

setColWidths(workbook, sheet = "Viewability", cols = 1:3, widths = "auto")
setColWidths(workbook, sheet = "Fraud", cols = 1:3, widths = "auto")

#Write File

saveWorkbook(workbook, paste("C:/Users/briantos/Documents/Blacklist Complete/",Sys.Date(),"_Blacklist.xlsx"))## Heading ##

I've tried the alignment() function, the CellStyle() fuction, etc. They all return errors. Please help!

Upvotes: 1

Views: 2509

Answers (1)

niko
niko

Reputation: 5281

Here is an example using openxlsx. What you are looking for are the arguments halign;valign in the method createStyle.

library(openxlsx)
wb <- createWorkbook()
wb$addWorksheet('test')
wb$writeData(sheet = 'test', 
             df = cbind.data.frame(matrix(1:9,3,3), let=letters[1:3]), 
             colClasses = c(rep('integer',3),'character'), keepNA = TRUE,
             colNames = TRUE, startRow = 1L, startCol = 1L)
wb$addStyle(sheet = 1, rows = 1:4, cols = 4L, 
            style = createStyle(halign = 'right'))  # set halign='right' - leave every other element to default value
saveWorkbook(wb,'d:/Test/test_wb.xlsx') # last column is right-aligned

You can also simply do something of the form 

addStyle(wb = wb, sheet = 'test', cols = 4L, rows = 1:4, 
         style = createStyle(halign = 'right'))

Edit

Rows/Columns selection: what I usually do is working with df (the data frame of the worksheet in question) and clearly identify where I want to apply the formatting

# Ex. 1: all rows, column 4
addStyle(wb = wb, sheet = 'test', cols = 4L, rows = 1:nrow(df), 
         style = createStyle(halign = 'right'))

# Ex. 2: all rows, all columns
addStyle(wb = wb, sheet = 'test', cols = 1:ncol(df), rows = 1:nrow(df),
         gridExpand = TRUE, # make sure to set this to TRUE in order to really select everything
         style = createStyle(halign = 'right'))

You can then just as easily select groups of rows or columns e.g. rows = c(1,3) (you can also just set the max. number of columns and rows but applying formatting to every single cell is not very efficient I guess).

Also if your data doesn't start at row/column number 1 but n, then you'd need to shift the selection e.g. rows = n:(nrow(df)+n-1) (might need to remove the -1 - not sure. I always make a mistake here).

Upvotes: 3

Related Questions