dplyr code produces two tibbles but write_xlsx only exports one (the wrong one)

[cathoms]

Hi, new user here and new to R. I am working from an "ugly" Excel sheet with lots of extraneous columns and merged cells, etc. I want to create a new calculated column and then export it to Excel. I can do this, but what gets exported doesn't include the newly created column.

Here is my code:

# load needed libraries
library(readxl)
library(dplyr)
library(tidyr)

#read excel file into data frame
df_sch <- read_excel(path="C:/Users/USERNAME/OneDrive - NAME/Project Areas/Q&A Heatmap/LargeMedCenters/QACalculator_SCH_PostCOVID_2020.xlsm",sheet="Calculator",skip=7)

#pare down data frame to columns of interest using select()
df_sch2 <- select(df_sch,Metric,Domain,Measure,`Metric Value...23`,`Z-Score...24`,`% of Domain Score...25`,`Domain Rank...27`)

df_sch2 %>% 
  mutate(  
    Z_SCORE_24 = as.numeric(`Z-Score...24`),
    PERCENTILE_RANK_NO = pnorm(Z_SCORE_24, lower.tail = FALSE)
  )

df_sch2 %>%  fill(Domain)

That produces the following:

I export to Excel using this code:

write_xlsx(df_sch2, "C:/Users/USERNAME/Documents/WorkingFiles/CC-Vizient_SystemRanking/Viz_ZscorePrctl_SCH.xlsx")

But what gets exported is the version with 7 columns. I need the version with 9 columns. Is there a way to specify which tibble to export? Is my fill code line overriding the mutate code? Something else? I would appreciate any help!

[martin.R]

You haven't assigned the changes to df_sch2, so the changes are only printed to the console. Add this for each change:

df_sch2 <- df_sch2 %>% ...

[cathoms]

Oh, of course! Argh! Newbie mistake. I keep forgetting about the need to do that!

That worked for exporting the file, except now in RStudio I get just the "R console" info from before and no preview of the output...

[cathoms]

Nevermind. I just realized I need to just call df_sch2.

Thanks for the super fast help!

[jrkrideau]

There is no output to the console. It is in df_sch2

Try

df_sch2

or to be more formal

print(df_sch2)

to see what is there,