R Chart Repeats itself

Question

I am trying to use R to draw a chart.

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

Ind <- read_csv("Industrial.csv")

Ind1 <- Ind %>%
  select(Period,Year,City,Sub_Market,GeoLevel7,Property_Type,Average_Equivalent_Yield_Prime_Grade) %>%
  filter(Sub_Market == "Sydney Metro" & GeoLevel7 == "South Sydney" & Property_Type == "Distribution Warehouse/Logistics")%>%
  unite(Time,Period,Year,sep=".")

ggplot(data=Ind1, aes(x=Time, y=Average_Equivalent_Yield_Prime_Grade,group=1)) +
  geom_line(color="#aa0022", size=1) +
  geom_point(color="#aa0022", size=1) +
  scale_x_discrete(breaks=c("Q1.1976","Q1.1981","Q1.1986","Q1.1991","Q1.1996","Q1.2001","Q1.2006","Q1.2011","Q1.2016","Q1F.2021","Q1F.2026")) +
  ggtitle("South Sydney Cap Rates") +
  labs(x="", y="(%)") +
  theme(axis.title.y = element_text(size=12, family="Times", color="#666666")) +
  theme(axis.text = element_text(size=12, family="Times")) +
  theme(plot.title = element_text(size=14, family="Times", face="bold", hjust=0, color="#666666")

My issue is that the output is a chart that appears to repeat itself.

Screenshot of chart outcome

I am struggling to work out why this is happening. I have written the data table to a CSV file and have drawn it in Excel to get this result.

Excel Chart Screenshot

Some of the data is not available hence the broken lines in Excel, is this the cause of the R issue?

A sample of the Ind1 tibble is as below Image of Ind1 tibble. Sorry I am not sure how to insert a table or CSV file of the data.

Welcome to SO. Would you be able to provide a minimal sample data? Then, SO users can look into your case and provide some supports for you. — jazzurro, Jan 24 '18 at 04:03
Thanks, I have added a screenshot of the data. Probably best in a table or csv file but not sure how to add that in the comment box? — swalk88, Jan 24 '18 at 21:10
Can your type dput(Ind) in R console? Then you’ll see some texts there. That’s your data. — jazzurro, Jan 25 '18 at 00:26

jazzurro · Answer 1 · 2018-01-25T02:36:13.733

Since this is your second post, you probably have some difficulties in creating a sample data. So I had a look of your image and create a minimal sample data. You will need to modify my code in a way it works for you.

I noticed that you do not have all four quarters in each year. So, you need to create a full data set. Time contains year and quarter. I extracted characters for each using substr(). In order to have a full data set, I used complete(). By this time, you have all four quarters for each year. For a labeling purpose, I updated Time, which contain year and quarter information.

Now you have a clean data set. You can draw a figure. I simplified your code. You need to add whatever you want to complete your task. Finally, if you need to produce a sample data, use dput(). Have a look of this question.

library(tidyverse)

mydf <- data.frame(Time = c("Q1.1977", "Q2.1977", "Q3.1977", "Q4.1977",
                            "Q1.1978", "Q2.1978", "Q3.1978", "Q4.1978",
                            "Q2.2015", "Q3.2015", "Q4.2015"),
                   GeoLevel = "South Sydney",
                   Average = c(NA, NA, NA, NA, NA, 9.63, 9.61, 9.58,
                               6.88, 6.63, 6.38),
                   stringsAsFactors = FALSE)


mutate(mydf,
       Quarter = substr(Time, 1, 2),
       Time = substr(Time, 4, 7)) %>%
complete(Time, nesting(Quarter), fill = list(Average = NA)) %>%
mutate(Time = paste(Time, Quarter, sep = "-")) -> out


ggplot(data = out, aes(x = Time, y = Average)) +
geom_point() +
geom_path(group = 1, color = "red") +
theme(axis.text.x = element_text(angle = 90, hjust = 1))

R Chart Repeats itself

1 Answers1