Data Wrangling Week 5 - Assignment

Packages Required

I used the following packages for this assignment:

library(tidyr) 
library(tibble) 
library(dplyr)
library(ggplot2)
library(reshape2)

---

Answers

1

library(tidyr)
library(tibble)
bomber_wide <- as_data_frame(readRDS("bomber_wide.rds"))
head(bomber_wide)

## # A tibble: 3 × 21
##     Type    MD `1996` `1997` `1998` `1999` `2000` `2001` `2002` `2003`
##    <chr> <chr>  <int>  <int>  <int>  <int>  <int>  <int>  <int>  <int>
## 1 Bomber   B-1  26914  25219  24205  23306  25013  25059  26581  21491
## 2 Bomber   B-2   2364   2776   2166   3672   4543   4754   5969   6801
## 3 Bomber  B-52  28511  26034  25639  24500  24387  24813  36687  30230
## # ... with 11 more variables: `2004` <int>, `2005` <int>, `2006` <int>,
## #   `2007` <int>, `2008` <int>, `2009` <int>, `2010` <int>, `2011` <int>,
## #   `2012` <int>, `2013` <int>, `2014` <int>

bomber_wide <- gather(bomber_wide,"year","fh",3:21)
head(bomber_wide)

## # A tibble: 6 × 4
##     Type    MD  year    fh
##    <chr> <chr> <chr> <int>
## 1 Bomber   B-1  1996 26914
## 2 Bomber   B-2  1996  2364
## 3 Bomber  B-52  1996 28511
## 4 Bomber   B-1  1997 25219
## 5 Bomber   B-2  1997  2776
## 6 Bomber  B-52  1997 26034

2

library(tidyr)
library(tibble)
bomber_long <- as_data_frame(readRDS("bomber_long.rds"))
head(bomber_long)

## # A tibble: 6 × 5
##     Type    MD    FY Output Value
##    <chr> <chr> <int>  <chr> <int>
## 1 Bomber   B-1  1996     FH 26914
## 2 Bomber   B-1  1997     FH 25219
## 3 Bomber   B-1  1998     FH 24205
## 4 Bomber   B-1  1999     FH 23306
## 5 Bomber   B-1  2000     FH 25013
## 6 Bomber   B-1  2001     FH 25059

bomber_long <- spread(bomber_long,Output,Value)
head(bomber_long)

## # A tibble: 6 × 6
##     Type    MD    FY     Cost    FH  Gallons
##    <chr> <chr> <int>    <int> <int>    <int>
## 1 Bomber   B-1  1996 72753781 26914 88594449
## 2 Bomber   B-1  1997 71297263 25219 85484074
## 3 Bomber   B-1  1998 84026805 24205 85259038
## 4 Bomber   B-1  1999 71848336 23306 79323816
## 5 Bomber   B-1  2000 58439777 25013 86230284
## 6 Bomber   B-1  2001 94946077 25059 86892432

3

library(tidyr)
library(tibble)
bomber_combined <- as_data_frame(readRDS("bomber_combined.rds"))
head(bomber_combined)

## # A tibble: 6 × 5
##           AC    FY     Cost    FH  Gallons
##        <chr> <int>    <int> <int>    <int>
## 1 Bomber B-1  1996 72753781 26914 88594449
## 2 Bomber B-1  1997 71297263 25219 85484074
## 3 Bomber B-1  1998 84026805 24205 85259038
## 4 Bomber B-1  1999 71848336 23306 79323816
## 5 Bomber B-1  2000 58439777 25013 86230284
## 6 Bomber B-1  2001 94946077 25059 86892432

bomber_combined <- separate(bomber_combined,AC,c("Type","MD"),sep = " ")
bomber_combined <- as_data_frame(bomber_combined)
head(bomber_combined)

## # A tibble: 6 × 6
##     Type    MD    FY     Cost    FH  Gallons
##    <chr> <chr> <int>    <int> <int>    <int>
## 1 Bomber   B-1  1996 72753781 26914 88594449
## 2 Bomber   B-1  1997 71297263 25219 85484074
## 3 Bomber   B-1  1998 84026805 24205 85259038
## 4 Bomber   B-1  1999 71848336 23306 79323816
## 5 Bomber   B-1  2000 58439777 25013 86230284
## 6 Bomber   B-1  2001 94946077 25059 86892432

4

library(tidyr)
library(tibble)
bomber_prefix <- as_data_frame(readRDS("bomber_prefix.rds"))
head(bomber_prefix)

## # A tibble: 6 × 6
##     Type prefix number    FY Output Value
##    <chr>  <chr>  <chr> <int>  <chr> <int>
## 1 Bomber      B      1  1996     FH 26914
## 2 Bomber      B      1  1997     FH 25219
## 3 Bomber      B      1  1998     FH 24205
## 4 Bomber      B      1  1999     FH 23306
## 5 Bomber      B      1  2000     FH 25013
## 6 Bomber      B      1  2001     FH 25059

bomber_prefix <- bomber_prefix %>%
  unite(MD,prefix,number,sep='-') %>% as_data_frame()
head(bomber_prefix)

## # A tibble: 6 × 5
##     Type    MD    FY Output Value
##    <chr> <chr> <int>  <chr> <int>
## 1 Bomber   B-1  1996     FH 26914
## 2 Bomber   B-1  1997     FH 25219
## 3 Bomber   B-1  1998     FH 24205
## 4 Bomber   B-1  1999     FH 23306
## 5 Bomber   B-1  2000     FH 25013
## 6 Bomber   B-1  2001     FH 25059

5

library(tidyr)
library(tibble)
library(dplyr)
library(ggplot2)
bomber_mess <- as_data_frame(readRDS("bomber_mess.rds"))
head(bomber_mess)

## # A tibble: 6 × 5
##     Type prefix number  Metric Value
##    <chr>  <chr>  <chr>   <chr> <int>
## 1 Bomber      B      1 1996_FH 26914
## 2 Bomber      B      1 1997_FH 25219
## 3 Bomber      B      1 1998_FH 24205
## 4 Bomber      B      1 1999_FH 23306
## 5 Bomber      B      1 2000_FH 25013
## 6 Bomber      B      1 2001_FH 25059

bomber_mess <- bomber_mess %>%
  unite(MD,prefix,number,sep='-') %>%
  separate(Metric,c("FY","output"),sep="_") %>%
  spread(output,Value)
head(bomber_mess)

## # A tibble: 6 × 6
##     Type    MD    FY     Cost    FH  Gallons
##    <chr> <chr> <chr>    <int> <int>    <int>
## 1 Bomber   B-1  1996 72753781 26914 88594449
## 2 Bomber   B-1  1997 71297263 25219 85484074
## 3 Bomber   B-1  1998 84026805 24205 85259038
## 4 Bomber   B-1  1999 71848336 23306 79323816
## 5 Bomber   B-1  2000 58439777 25013 86230284
## 6 Bomber   B-1  2001 94946077 25059 86892432

Graph comparing the Cost, flying hours and Fallons for the three bomber type:

6

library(ggplot2)
library(tidyr)
library(dplyr)
ws_programmatics <- as_data_frame(readRDS("ws_programmatics.rds"))
ws_categorizations <- as_data_frame(readRDS("ws_categorizations.rds"))

ws6 <- merge(x=ws_programmatics,y=ws_categorizations,by=c("Base","MD"),all.x=TRUE) %>%
      filter(FY==2014, Base == "MINOT AFB (ND)") %>%
      filter(System %in% c("AIRCRAFT","MISSILES")) %>%
      group_by(System) %>%
      summarise(Total_O_S=sum(Total_O.S,na.rm=TRUE),End_Strength_sum=sum(End_Strength,na.rm=TRUE))

7

library(ggplot2)
library(tidyr)
library(dplyr)
ws7 <- merge(x=ws_programmatics,y=ws_categorizations,by=c("Base","MD"),all.x=TRUE) %>%
  filter(FY==2014) %>%
  group_by(Base) %>%
  summarise(cpfh=(sum(Total_O.S,na.rm=T)/sum(FH,na.rm=T))) %>%
  filter(cpfh != 'Inf') %>%
  arrange(desc(cpfh)) %>%
  top_n(10)

The base with the max CPFH is Tinker AFB(OK)

ggplot(data=ws7,aes(x=reorder(Base,cpfh),y=cpfh)) + 
  geom_bar(stat="identity")+
  coord_flip() +
  ylab("Cost per flying hour")+
  xlab("")+
  ggtitle("CPFH for top 10 Bases")

8

library(ggplot2)
library(tidyr)
library(dplyr)
ws8 <- merge(x=ws_programmatics,y=ws_categorizations,by=c("Base","MD"),all.x=TRUE)

ggplot(ws8, aes(x = End_Strength, y = Total_O.S))+
  geom_point()+
  facet_wrap(~System)+
  ggtitle("End strength vs Total OS by Systems")

ggplot(ws8, aes(x = End_Strength, y = Total_O.S))+
  geom_point()+
  facet_wrap(~FY)+
  ggtitle("End strength vs Total OS by FY")

ggplot(ws8, aes(x = End_Strength, y = Total_O.S))+
  geom_point()+
  facet_wrap(~Base)+
  theme(axis.text.x=element_blank(),
        axis.ticks.x=element_blank(),
        axis.text.y=element_blank(),
        axis.ticks.y=element_blank()
        )+
  ggtitle("End strength vs Total OS by Bases")