Joining data

Code for Quiz 6, more dplyr and our first interactive chart using echarts4r.

Steps 1-6

  1. Load the R packages we will use.
library(tidyverse)
library(echarts4r)  #install this package before using
library(hrbrthemes) #install this package before using
  1. Read the data in the files, drug_cos.csv, health_cos.csv in to R and assign to the variables drug_cos and health_cos, respectively.
drug_cos  <- read_csv("https://estanny.com/static/week6/drug_cos.csv")
health_cos  <- read_csv("https://estanny.com/static/week6/health_cos.csv")
  1. Use glimpse to get a glimpse of the data.
drug_cos %>% glimpse()
Rows: 104
Columns: 9
$ ticker       <chr> "ZTS", "ZTS", "ZTS", "ZTS", "ZTS", "ZTS", "ZTS…
$ name         <chr> "Zoetis Inc", "Zoetis Inc", "Zoetis Inc", "Zoe…
$ location     <chr> "New Jersey; U.S.A", "New Jersey; U.S.A", "New…
$ ebitdamargin <dbl> 0.149, 0.217, 0.222, 0.238, 0.182, 0.335, 0.36…
$ grossmargin  <dbl> 0.610, 0.640, 0.634, 0.641, 0.635, 0.659, 0.66…
$ netmargin    <dbl> 0.058, 0.101, 0.111, 0.122, 0.071, 0.168, 0.16…
$ ros          <dbl> 0.101, 0.171, 0.176, 0.195, 0.140, 0.286, 0.32…
$ roe          <dbl> 0.069, 0.113, 0.612, 0.465, 0.285, 0.587, 0.48…
$ year         <dbl> 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018…
health_cos %>% glimpse() 
Rows: 464
Columns: 11
$ ticker      <chr> "ZTS", "ZTS", "ZTS", "ZTS", "ZTS", "ZTS", "ZTS"…
$ name        <chr> "Zoetis Inc", "Zoetis Inc", "Zoetis Inc", "Zoet…
$ revenue     <dbl> 4233000000, 4336000000, 4561000000, 4785000000,…
$ gp          <dbl> 2581000000, 2773000000, 2892000000, 3068000000,…
$ rnd         <dbl> 427000000, 409000000, 399000000, 396000000, 364…
$ netincome   <dbl> 245000000, 436000000, 504000000, 583000000, 339…
$ assets      <dbl> 5711000000, 6262000000, 6558000000, 6588000000,…
$ liabilities <dbl> 1975000000, 2221000000, 5596000000, 5251000000,…
$ marketcap   <dbl> NA, NA, 16345223371, 21572007994, 23860348635, …
$ year        <dbl> 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018,…
$ industry    <chr> "Drug Manufacturers - Specialty & Generic", "Dr…
  1. Which variables are the same in both data sets
names_drug  <- drug_cos  %>%  names() 
names_health  <- health_cos  %>%  names() 
intersect(names_drug, names_health)
[1] "ticker" "name"   "year"  
  1. Select subset of variables to work with.

-For health_cos select (in this order): ticker, year, revenue, gp, industry

-Extract observations for 2018

-Assign output to health_subset

drug_subset  <- drug_cos  %>% 
  select(ticker, year, grossmargin)  %>% 
  filter(year == 2018)

health_subset  <- health_cos  %>%
  select(ticker, year, revenue, gp, industry)  %>% 
  filter(year == 2018)
  1. Keep all the rows and columns drug_subset join with columns in health_subset
drug_subset  %>% left_join(health_subset)
# A tibble: 13 x 6
   ticker  year grossmargin   revenue        gp industry              
   <chr>  <dbl>       <dbl>     <dbl>     <dbl> <chr>                 
 1 ZTS     2018       0.672   5.82e 9   3.91e 9 Drug Manufacturers - …
 2 PRGO    2018       0.387   4.73e 9   1.83e 9 Drug Manufacturers - …
 3 PFE     2018       0.79    5.36e10   4.24e10 Drug Manufacturers - …
 4 MYL     2018       0.35    1.14e10   4.00e 9 Drug Manufacturers - …
 5 MRK     2018       0.681   4.23e10   2.88e10 Drug Manufacturers - …
 6 LLY     2018       0.738   2.46e10   1.81e10 Drug Manufacturers - …
 7 JNJ     2018       0.668   8.16e10   5.45e10 Drug Manufacturers - …
 8 GILD    2018       0.781   2.21e10   1.73e10 Drug Manufacturers - …
 9 BMY     2018       0.71    2.26e10   1.60e10 Drug Manufacturers - …
10 BIIB    2018       0.865   1.35e10   1.16e10 Drug Manufacturers - …
11 AMGN    2018       0.827   2.37e10   1.96e10 Drug Manufacturers - …
12 AGN     2018       0.861   1.58e10   1.36e10 Drug Manufacturers - …
13 ABBV    2018       0.764   3.28e10   2.50e10 Drug Manufacturers - …

Question: join_ticker

drug_cos_subset  <- drug_cos  %>% 
  filter(ticker == "MYL")

drug_cos_subset
# A tibble: 8 x 9
  ticker name  location ebitdamargin grossmargin netmargin   ros   roe
  <chr>  <chr> <chr>           <dbl>       <dbl>     <dbl> <dbl> <dbl>
1 MYL    Myla… United …        0.245       0.418     0.088 0.161 0.146
2 MYL    Myla… United …        0.244       0.428     0.094 0.163 0.184
3 MYL    Myla… United …        0.228       0.44      0.09  0.153 0.209
4 MYL    Myla… United …        0.242       0.457     0.12  0.169 0.283
5 MYL    Myla… United …        0.243       0.447     0.09  0.133 0.089
6 MYL    Myla… United …        0.19        0.424     0.043 0.052 0.044
7 MYL    Myla… United …        0.272       0.402     0.058 0.121 0.054
8 MYL    Myla… United …        0.258       0.35      0.031 0.074 0.028
# … with 1 more variable: year <dbl>
combo_df  <- drug_cos_subset  %>% 
  left_join(health_cos)

combo_df
# A tibble: 8 x 17
  ticker name  location ebitdamargin grossmargin netmargin   ros   roe
  <chr>  <chr> <chr>           <dbl>       <dbl>     <dbl> <dbl> <dbl>
1 MYL    Myla… United …        0.245       0.418     0.088 0.161 0.146
2 MYL    Myla… United …        0.244       0.428     0.094 0.163 0.184
3 MYL    Myla… United …        0.228       0.44      0.09  0.153 0.209
4 MYL    Myla… United …        0.242       0.457     0.12  0.169 0.283
5 MYL    Myla… United …        0.243       0.447     0.09  0.133 0.089
6 MYL    Myla… United …        0.19        0.424     0.043 0.052 0.044
7 MYL    Myla… United …        0.272       0.402     0.058 0.121 0.054
8 MYL    Myla… United …        0.258       0.35      0.031 0.074 0.028
# … with 9 more variables: year <dbl>, revenue <dbl>, gp <dbl>,
#   rnd <dbl>, netincome <dbl>, assets <dbl>, liabilities <dbl>,
#   marketcap <dbl>, industry <chr>

*Note: the variables ticker, name, location and industry are the same for all the observations


co_name  <- combo_df  %>% 
  distinct(name) %>% 
  pull()

co_location  <- combo_df  %>% 
  distinct(location)  %>% 
  pull() 

Assign the industry to co_industry group

co_industry  <- combo_df  %>% 
  distinct(industry)  %>% 
  pull()

Put the r inline commands used in the blanks below. When you knit the document the results of the commands will be displayed in your text.

The company Mylan NV is located in co_location and is a member of the co_industry industry group.


combo_df_subset  <- combo_df  %>% 
  select(year, grossmargin, netmargin, revenue, gp, netincome)
combo_df_subset
# A tibble: 8 x 6
   year grossmargin netmargin     revenue         gp netincome
  <dbl>       <dbl>     <dbl>       <dbl>      <dbl>     <dbl>
1  2011       0.418     0.088  6129825000 2563364000 536810000
2  2012       0.428     0.094  6796100000 2908300000 640900000
3  2013       0.44      0.09   6909100000 3040300000 623700000
4  2014       0.457     0.12   7719600000 3528000000 929400000
5  2015       0.447     0.09   9429300000 4216100000 847600000
6  2016       0.424     0.043 11076900000 4697000000 480000000
7  2017       0.402     0.058 11907700000 4783100000 696000000
8  2018       0.35      0.031 11433900000 4001600000 352500000

Create the variable grossmargin_check to compare with the variable grossmargin. They should be equal. grossmargin_check = gp / revenue

*Create the variable close_enough to check that the absolute value of the difference between grossmargin_check and grossmargin is less than 0.001

combo_df_subset  %>% 
  mutate(grossmargin_check =gp / revenue,
  close_enough = abs(grossmargin_check - grossmargin) < 0.001)
# A tibble: 8 x 8
   year grossmargin netmargin revenue     gp netincome
  <dbl>       <dbl>     <dbl>   <dbl>  <dbl>     <dbl>
1  2011       0.418     0.088 6.13e 9 2.56e9 536810000
2  2012       0.428     0.094 6.80e 9 2.91e9 640900000
3  2013       0.44      0.09  6.91e 9 3.04e9 623700000
4  2014       0.457     0.12  7.72e 9 3.53e9 929400000
5  2015       0.447     0.09  9.43e 9 4.22e9 847600000
6  2016       0.424     0.043 1.11e10 4.70e9 480000000
7  2017       0.402     0.058 1.19e10 4.78e9 696000000
8  2018       0.35      0.031 1.14e10 4.00e9 352500000
# … with 2 more variables: grossmargin_check <dbl>,
#   close_enough <lgl>

combo_df_subset  %>% 
  mutate(netmargin_check = netincome / revenue,
  close_enough = abs(netmargin_check - netmargin) < 0.001)
# A tibble: 8 x 8
   year grossmargin netmargin revenue     gp netincome netmargin_check
  <dbl>       <dbl>     <dbl>   <dbl>  <dbl>     <dbl>           <dbl>
1  2011       0.418     0.088 6.13e 9 2.56e9 536810000          0.0876
2  2012       0.428     0.094 6.80e 9 2.91e9 640900000          0.0943
3  2013       0.44      0.09  6.91e 9 3.04e9 623700000          0.0903
4  2014       0.457     0.12  7.72e 9 3.53e9 929400000          0.120 
5  2015       0.447     0.09  9.43e 9 4.22e9 847600000          0.0899
6  2016       0.424     0.043 1.11e10 4.70e9 480000000          0.0433
7  2017       0.402     0.058 1.19e10 4.78e9 696000000          0.0584
8  2018       0.35      0.031 1.14e10 4.00e9 352500000          0.0308
# … with 1 more variable: close_enough <lgl>

Question: summarize_industry

health_cos  %>% 
  group_by(industry)  %>% 
  summarize(mean_netmargin_percent = mean(netincome / revenue) * 100,
            median_netmargin_percent = median(netincome / revenue) * 100,
            min_netmargin_percent = min(netincome / revenue) * 100,
            max_netmargin_percent = max(netincome / revenue) * 100
  ) 
# A tibble: 9 x 5
  industry mean_netmargin_… median_netmargi… min_netmargin_p…
* <chr>               <dbl>            <dbl>            <dbl>
1 Biotech…            -4.66             7.62         -197.   
2 Diagnos…            13.1             12.3             0.399
3 Drug Ma…            19.4             19.5           -34.9  
4 Drug Ma…             5.88             9.01          -76.0  
5 Healthc…             3.28             3.37           -0.305
6 Medical…             6.10             6.46            1.40 
7 Medical…            12.4             14.3           -56.1  
8 Medical…             1.70             1.03           -0.102
9 Medical…            12.3             14.0           -47.1  
# … with 1 more variable: max_netmargin_percent <dbl>

Question: inline_ticker

health_cos_subset  <- health_cos  %>% 
  filter(ticker == "ILMN")
health_cos_subset
# A tibble: 8 x 11
  ticker name  revenue     gp    rnd netincome assets liabilities
  <chr>  <chr>   <dbl>  <dbl>  <dbl>     <dbl>  <dbl>       <dbl>
1 ILMN   Illu…  1.06e9 7.09e8 1.97e8  86628000 2.20e9  1120625000
2 ILMN   Illu…  1.15e9 7.74e8 2.31e8 151254000 2.57e9  1247504000
3 ILMN   Illu…  1.42e9 9.12e8 2.77e8 125308000 3.02e9  1485804000
4 ILMN   Illu…  1.86e9 1.30e9 3.88e8 353351000 3.34e9  1876842000
5 ILMN   Illu…  2.22e9 1.55e9 4.01e8 462000000 3.69e9  1839194000
6 ILMN   Illu…  2.40e9 1.67e9 5.04e8 454000000 4.28e9  2011000000
7 ILMN   Illu…  2.75e9 1.83e9 5.46e8 725000000 5.26e9  2508000000
8 ILMN   Illu…  3.33e9 2.30e9 6.23e8 826000000 6.96e9  3114000000
# … with 3 more variables: marketcap <dbl>, year <dbl>,
#   industry <chr>

*In the console, type ?distinct. Go to the help pane to see what distinct does

*In the console, type ?pull. Go to the help pane to see what pull does

Run the code below

health_cos_subset  %>% 
  distinct(name) %>%  
  pull(name)
[1] "Illumina Inc"
co_name <- health_cos_subset  %>% 
  distinct(name) %>% 
  pull(name)

You can take output from your code and include it in your text.

In following chuck

co_industry  <- health_cos_subset  %>% 
  distinct(industry) %>% 
  pull()

This is outside the R chunk. Put the r inline commands used in the blanks below. When you knit the document the results of the commands will be displayed in your text.

The company Illumina Inc is a member of the Diagnostics & Research.


Steps 7-11

  1. Prepare the data for the plots
df <- health_cos  %>% 
  group_by(industry)  %>%
  summarize(med_rnd_rev = median(rnd/revenue)) 
  1. Use glimpse to glimpse the data for the plots.
df  %>% glimpse()
Rows: 9
Columns: 2
$ industry    <chr> "Biotechnology", "Diagnostics & Research", "Dru…
$ med_rnd_rev <dbl> 0.48317287, 0.05620271, 0.17451442, 0.06851879,…
  1. Create a static bar chart
ggplot(data = df, 
       mapping = aes(
         x = reorder(industry, med_rnd_rev ),
         y = med_rnd_rev
         )) +
  geom_col() + 
  scale_y_continuous(labels = scales::percent) +
  coord_flip() +
  labs(
    title = "Median R&D expenditures",
    subtitle = "by industry as a percent of revenue from 2011 to 2018",
    x = NULL, y = NULL) +
  theme_ipsum()

  1. Save the previous plot to preview.png and add to the yaml chunk at the top.

  2. Create an interactive bar chart using the package echarts4r

df  %>% 
  arrange(med_rnd_rev)  %>%
  e_charts(
    x = industry
    )  %>% 
  e_bar(
    serie = med_rnd_rev, 
    name = "median"
    )  %>%
  e_flip_coords()  %>% 
  e_tooltip()  %>% 
  e_title(
    text = "Median industry R&D expenditures", 
    subtext = "by industry as a percent of revenue from 2011 to 2018",
    left = "center") %>% 
  e_legend(FALSE) %>% 
  e_x_axis(
    formatter = e_axis_formatter("percent", digits = 0)
    )  %>%
  e_y_axis(
    show = FALSE
  )  %>% 
  e_theme("vintage")