CODEWITHSUNDEEP
r
Andrew Bannerman
Andrew Bannerman

Reputation: 1305

full_join using dplyr to make one dataframe - issues with melt and ggplot2

I joined two dataframes using fulljoin using dplyr.

This is the result: 

> head(newdf1)
    spdate SP500close artprice
1 19870330     289.20     83.6
2 19870331     291.70       NA
3 19870401     292.39       NA
4 19870402     293.63       NA
5 19870403     300.41       NA
6 19870406     301.95       NA

Then using reshape2 to melt:

library(reshape2)

df.melted <- melt(newdf1, id.vars = "spdate", na.rm = FALSE, value.name = "value", factorsAsStrings = TRUE)

So after melting... the dataframe changes... 

> head(df.melted)
    spdate   variable  value
1 19870330 SP500close 289.20
2 19870331 SP500close 291.70
3 19870401 SP500close 292.39
4 19870402 SP500close 293.63
5 19870403 SP500close 300.41
6 19870406 SP500close 301.95

The melt actually appended the artprice column to the bottom of the column above... however I wish to plot with ggplot2 artprice column and also spdate and SP500close.

x axis want to be spdate.

two Y axis.... SP500close, artprice.

How do i melt this correctly?

Thanks

EDIT** I found the answer. The answer was to plot as geom_point. Both columns can share the same Y axis as the scaling was the same. Here is my fix below: 

  #Create Plot
library(ggplot2)
p1 <- ggplot(df.melted, aes(x=spdate, y=value, colour=variable,)) + 
  geom_point() +
  theme_bw() +
  labs(title = "Most Expensive Art Sales - S&P500 Plot", 
       subtitle = "1987 to Present", 
       y = "S&P500 Cose - Expensive Art Prices", 
       x = "Date") +
theme(plot.title = element_text(hjust = 0.5)) +
theme(plot.subtitle = element_text(hjust = 0.5))

# Melt Dataframes For Plotting
library(reshape2)
df.melted <- melt(newdf, id.vars = "spdate", na.rm = FALSE)

#Save Plot
ggsave(filename="C:/R Projects/plot_1.png", plot=p1)

enter image description here

Upvotes: 1

Views: 191

Answers (1)

austensen
austensen

Reputation: 3017

I'm not exactly sure what you want to plot to look like, but I think you already have the date in the correct format there after you melted it.

I still had you data handy from an earlier question you posted, and so here's an example.

library(readr)
library(dplyr)
library(tidyr)
library(lubridate)
library(ggplot2)

df1 <- readr::read_csv(
'artdate,artprice
19870330,"$83.60"
19871111,"$113.60"
19881128,"$78.00"
19890509,"$92.50"
19890531,"$68.00"
19890801,"$115.90"'
)

df2 <- readr::read_csv(
'SP500close,SP500date
289.20,19870330
291.70,19870331
292.39,19870401
293.63,19870402
300.41,19870403
301.95,19870406'
)

full_join(df1, df2, by = c("artdate" = "SP500date")) %>% 
  gather("var", "val", -artdate) %>% 
  mutate(val = readr::parse_number(val),
         date = lubridate::ymd(artdate)) %>% 
  drop_na(val) %>% 
  ggplot(aes(date, val, color = var)) +
  geom_point()

Upvotes: 0

Related Questions