In R: make a plot (ylim is a logarithmic scale) the same as one generated by excel

0
votes

Here is the csv data: https://www.dropbox.com/s/87bwvhyo6i4f68u/test.csv , first column is the date and second column is the corresponding value.

I would like to plot such picture as made in excel: https://www.dropbox.com/s/13wyk1aeajgs6fq/test.png . Note: the ylim in the plot is changed to the logarithmic scale (base 10), which can the picture more better than the original one. I would like to this in R, especially using ggplot making it beautiful, but I can not. So I hope you can help.

ba <- as.data.frame(fread("test.csv"))
plot(as.factor(ba[, 1]), ba[, 2], log = "y", xaxt = "n", xlab = "")
idx <- seq(1, 100, length = 10)
labels <- ba[, 1]
labels <- labels[idx]
axis(1, seq(1, 100, length = 10), par("usr")[3], srt = 45, labels = labels, 
     adj = 1, xpd = TRUE)

Thanks.

EDIT:

[Below is the answer from rawr]. That is perfect.

tmp <- read.csv('~/desktop/test.csv', stringsAsFactors = FALSE,
            strip.white = TRUE, header = FALSE)

tmp$V1 <- paste0(tmp$V1, '-01')
tmp <- within(tmp, {
V1 <- as.Date(V1)
date <- format(as.Date(tmp$V1, '%Y-%m-%d'), '%Y-%m')
stuff <- V2
})


par(tcl = -.1, xpd = FALSE)
with(tmp, 
 plot(V1, log(stuff), type = 'n', ylim = c(0,6),
      col = 'royalblue1', lwd = 3, bty = 'l',
      axes = FALSE, xlab = '', ylab = ''))
abline(h = 0:6, lwd = .5)
with(tmp, points(x = V1, y = log10(stuff), type = 'l', 
             col = 'royalblue1', lwd = 3))

par(xpd = TRUE)
axis(2, at = 0:6, cex.axis = .6,
 labels = format(10 ** (0:6), scientific = FALSE, big.mark = ','), las = 2)
x <- with(tmp, seq(min(V1), max(V1), length = 12))
text(x = x, y = -.5, cex = .8, labels = format(x, '%Y-%m'), srt = 45)
2
r
is ggplot a requirement? - MrFlick
plot is also OK. But for me, even using plot function in basic R, I can not duplicate the one produced by excel. can you help? thanks. - BioChemoinformatics
Please post the code you have tried so far. - josliber
@josilber, my code does not work well. - BioChemoinformatics

2 Answers

5
votes

why you want to match what excel does is beyond me, but maybe this will get you started 

set.seed(1618)
tmp <- data.frame(date = seq(as.Date("2000-1-1"), 
                                    by = "month", length.out = 12),
                  stuff = sort(rpois(12, 5)) * 10000)

par(tcl = -.1, xpd = NA)
with(tmp, 
     plot(date, stuff, type = 'l', ylim = c(0, max(stuff)),
          col = 'royalblue1', lwd = 3, bty = 'l',
          axes = FALSE, xlab = '', ylab = ''))
axis(2, at = pretty(seq(0, max(tmp$stuff))), 
     labels = format(pretty(seq(0, max(tmp$stuff), length = 5)),
                     scientific = FALSE), las = 2)
text(x = tmp$date, y = -5000, labels = format(tmp$date, '%Y-%m'), srt = 45)
par(xpd = FALSE)
abline(h = pretty(seq(0, max(tmp$stuff))), lwd = .5)

enter image description here

EDIT

Here is how to make your own breaks and make the commas using prettyNum

par(tcl = -.1, xpd = NA)
with(tmp, 
     plot(date, stuff, type = 'l', ylim = c(0, 100000),
          col = 'royalblue1', lwd = 3, bty = 'l',
          axes = FALSE, xlab = '', ylab = ''))
nums <- c(0,10000, 30000, 70000, 100000)
prettyNum(nums, big.mark = ',', scientific = FALSE)
axis(2, at = nums, 
     labels = prettyNum(nums, big.mark = ',', scientific = FALSE),
     las = 2)
text(x = tmp$date, y = -10000, labels = format(tmp$date, '%Y-%m'), srt = 45)
par(xpd = FALSE)
abline(h = nums, lwd = .5)

enter image description here

EDIT

tmp <- read.csv('~/desktop/test.csv', stringsAsFactors = FALSE,
                strip.white = TRUE, header = FALSE)

tmp$V1 <- paste0(tmp$V1, '-01')
tmp <- within(tmp, {
  V1 <- as.Date(V1)
  date <- format(as.Date(tmp$V1, '%Y-%m-%d'), '%Y-%m')
  stuff <- V2
})


par(tcl = -.1, xpd = FALSE)
with(tmp, 
     plot(V1, log(stuff), type = 'n', ylim = c(0,6),
          col = 'royalblue1', lwd = 3, bty = 'l',
          axes = FALSE, xlab = '', ylab = ''))
abline(h = 0:6, lwd = .5)
with(tmp, points(x = V1, y = log10(stuff), type = 'l', 
                 col = 'royalblue1', lwd = 3))

par(xpd = TRUE)
axis(2, at = 0:6, cex.axis = .6,
     labels = format(10 ** (0:6), scientific = FALSE, big.mark = ','), las = 2)
x <- with(tmp, seq(min(V1), max(V1), length = 12))
text(x = x, y = -.5, cex = .8, labels = format(x, '%Y-%m'), srt = 45)

enter image description here

3
votes

With pkg:ggplot2 I would have thought you could just add +scale_y_log10("log_10 Scale")

 pl + geom_line() + scale_y_log10("log_10 Scale", limits=c(1, 1000000) )

> tmp <- read.table("~/Downloads/test.csv", sep=",")
> tmp$Date <- as.Date(paste0(tmp$V1, "-01"))
> pl <- ggplot(data=tmp, aes(x=Date, y=V2) )
> png(); print( pl+geom_line()+scale_y_log10("log_10 Scale", limits=c(1,1000000) ) ); dev.off()

enter image description here