I have made SPEI values for a time series from 1987-2022 and I am trying to export these values corresponding with the year and month into an excel file - but the output file is not giving me the correct values. It is outputting everything except "vals" in my excel file which is the info I want.

Here is my code:

spei$pet<-hargreaves(Tmin = spei$tmin, Tmax = spei$tmax, lat =  51.411843)

cwbal<-spei$precip-spei$pet
cwbal

spei1<-spei(cwbal, scale = 1)
spei1

plot(spei1)

vals <- spei1$fitted
spei$values <- as.data.frame(vals)

write_xlsx(spei, "spei_df.xlsx")

I was hoping to get an output file with columns of my original dataframe and also a corresponding column with my newly outputted SPEI values.

I have made SPEI values for a time series from 1987-2022 and I am trying to export these values corresponding with the year and month into an excel file - but the output file is not giving me the correct values. It is outputting everything except "vals" in my excel file which is the info I want.

Here is my code:

spei$pet<-hargreaves(Tmin = spei$tmin, Tmax = spei$tmax, lat =  51.411843)

cwbal<-spei$precip-spei$pet
cwbal

spei1<-spei(cwbal, scale = 1)
spei1

plot(spei1)

vals <- spei1$fitted
spei$values <- as.data.frame(vals)

write_xlsx(spei, "spei_df.xlsx")

I was hoping to get an output file with columns of my original dataframe and also a corresponding column with my newly outputted SPEI values.

• r-xlsx

1 Answer 1

The Error appears to be in spei$values <- as.data.frame(vals).

The way you handle spei suggest that is a data.frame. Using as.data.frame() coerces it into a list, that has spei as a list and inside the spei list is a data.frame() that contains your fitted values.

write_xlsx appears not to be equiped to handle nested objects in that fashion.

# Example for : How do I export a .xlsx file for SPEI  --------

library(writexl)
library(SPEI)

# Some Dataframe
df = data.frame(x=c(1, 3, 3, 4, 5, 5, 6, 8, 9, 12),
                y=c(12, 14, 14, 13, 17, 19, 22, 26, 24, 22))

# model with fitted values
model <- lm(y ~ x, data=df)
myVals <- fitted(model)

# Checking how write_xlsx works
?write_xlsx

# if the values should all appear on one sheet ------------------

# just the original data to be sure not to mess something up
notAListDf <- df 

# assuming myVals contains just a vector with the same length as the data
# as fitted values often do.
notAListDf$values <- myVals

write_xlsx(notAListDf, "notAListDf.xlsx")


# If a .xlsx with multiple sheets was needed ------------------------------------
listFromDf <-  list()
listFromDf[[1]] <- df

# as.numeric is needed because fitted() returns Named num, which is not supported
# in writexl
listFromDf[[2]] <- as.data.frame(as.numeric(myVals))

# these will appear as sheet names in the exel
names(listFromDf) <- c('df', 'myVals') 

write_xlsx(listFromDf, "listFromDf.xlsx")

Now that that’s out of the way, welcome to the Community!
I hope that helped, I am a little new to some aspects still, but wanted to add, that I had to make some assumptions and search the web to answer your question. There are at least 34 packages that can write .xlsx files (https://rbasics./best-r-packages-for-reading-and-writing-excel-files/) and i just picked one that had used write_xlsx() instead of write.xlsx(). So if you did not use writexl, the above example might not work.

So please, next time, be very specific about:

·       What packages you used

And it would have been easier if the code you provided would work right out of the box. I believe this is called a minimum working example. This way I would not have to assume that your fitted values had this or that structure but could just run your example and get right to answering your question.

Anyway, have a good time and keep on asking questions.