---
title: "Playfair's Graph Reproduction and Analysis"
author: "Your Name"
output: html_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
library(ggplot2)
library(readxl)
library(tidyr)
library(dplyr)
```

## 1. Reproduce Playfair's Graph

### Load and Prepare Data
```{r load-data}
# Load the Excel data (assumed in the first sheet)
data <- read_excel("F:/lin/end/lesson/New Microsoft Excel Worksheet.xlsx")

# View the structure of the data
head(data)
```

Assuming the data contains columns `Year`, `Wheat_Price` (in shillings per quarter), and `Salary` (in shillings per week), we will reproduce Playfair's original graph.

### Reproduce the Graph
```{r reproduce-graph}
# Reproduce Playfair's graph
colnames(data)=c("row","Year","Wheat_Price","Salary")
ggplot(data, aes(x = Year)) +
  geom_bar(aes(y = Wheat_Price), stat = "identity", fill = "grey",width = 3) +
  geom_area(aes(y = Salary), fill = "blue", alpha = 0.3) +
  geom_line(aes(y = Salary), color = "red", size = 1) +
  labs(
    title = "Playfair's Graph: Wheat Price and Salaries (1565-1821)",
    x = "Year",
    y = "Shillings"
  )
```

## 2. Improve the Presentation with Two Axes

To make the presentation more accurate, we will use two y-axes: one for wheat price in shillings per quarter and one for salaries in shillings per week.

### Plot with Two Y-Axes
```{r two-axes}
ggplot(data, aes(x = Year)) +
  geom_bar(aes(y = Wheat_Price), stat = "identity", fill = "grey") +
  geom_line(aes(y = Salary * 10), color = "red", size = 1) +  # Scaling salary to match wheat price
  scale_y_continuous(
    name = "Wheat Price (Shillings per Quarter)",
    sec.axis = sec_axis(~ . / 10, name = "Salary (Shillings per Week)")
  ) +
  labs(
    title = "Wheat Price and Salaries with Dual Axes",
    x = "Year"
  ) +
  theme(
    axis.title.y.right = element_text(color = "red")
  )
```

## 3. Analyze Purchasing Power Over Time

Next, we will calculate the purchasing power as the amount of wheat a worker can buy with their weekly salary and plot it over time.

### Purchasing Power Calculation
```{r purchasing-power}
# Calculate purchasing power
data <- data %>%
  mutate(Purchasing_Power = Salary / Wheat_Price)

# Plot purchasing power
ggplot(data, aes(x = Year, y = Purchasing_Power)) +
  geom_line(color = "green", size = 1) +
  labs(
    title = "Purchasing Power Over Time",
    x = "Year",
    y = "Wheat Quarters per Weekly Salary"
  )
```

## 4. Alternative Data Representation: Wheat Price vs Salary Without Time

To represent the wheat price and salary relationship without using time directly, we will make a scatter plot of wheat price versus salary and use a color gradient to show the progression of time.

### Wheat Price vs Salary
```{r wheat-vs-salary}
ggplot(data, aes(x = Wheat_Price, y = Salary, color = Year)) +
  geom_point() +
  scale_color_gradient(low = "blue", high = "red") +
  labs(
    title = "Wheat Price vs Salary Over Time",
    x = "Wheat Price (Shillings per Quarter)",
    y = "Salary (Shillings per Week)",
    color = "Year"
  )
```

## Conclusion

This analysis reproduces Playfair's famous graph, improves the clarity of the presentation with dual axes, and provides insights into the workers' purchasing power and how it evolves over time. The scatter plot of wheat price versus salary, with time represented by color, offers an alternative view of the data.