Steps 1-6

Load the R packages that we will use.

library(tidyverse)
library(echarts4r)
library(hrbrthemes)

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")

Use glimpse to get a glimpse of the data

drug_cos %>% glimpse()

Rows: 104
Columns: 9
$ ticker       <chr> "ZTS", "ZTS", "ZTS", "ZTS", "ZTS", "ZTS", "Z...
$ name         <chr> "Zoetis Inc", "Zoetis Inc", "Zoetis Inc", "Z...
$ location     <chr> "New Jersey; U.S.A", "New Jersey; U.S.A", "N...
$ ebitdamargin <dbl> 0.149, 0.217, 0.222, 0.238, 0.182, 0.335, 0....
$ grossmargin  <dbl> 0.610, 0.640, 0.634, 0.641, 0.635, 0.659, 0....
$ netmargin    <dbl> 0.058, 0.101, 0.111, 0.122, 0.071, 0.168, 0....
$ ros          <dbl> 0.101, 0.171, 0.176, 0.195, 0.140, 0.286, 0....
$ roe          <dbl> 0.069, 0.113, 0.612, 0.465, 0.285, 0.587, 0....
$ year         <int> 2011, 2012, 2013, 2014, 2015, 2016, 2017, 20...

health_cos %>% glimpse()

Rows: 464
Columns: 11
$ ticker      <chr> "ZTS", "ZTS", "ZTS", "ZTS", "ZTS", "ZTS", "ZT...
$ name        <chr> "Zoetis Inc", "Zoetis Inc", "Zoetis Inc", "Zo...
$ revenue     <dbl> 4233000000, 4336000000, 4561000000, 478500000...
$ gp          <dbl> 2581000000, 2773000000, 2892000000, 306800000...
$ rnd         <dbl> 427000000, 409000000, 399000000, 396000000, 3...
$ netincome   <dbl> 245000000, 436000000, 504000000, 583000000, 3...
$ assets      <dbl> 5711000000, 6262000000, 6558000000, 658800000...
$ liabilities <dbl> 1975000000, 2221000000, 5596000000, 525100000...
$ marketcap   <dbl> NA, NA, 16345223371, 21572007994, 23860348635...
$ year        <int> 2011, 2012, 2013, 2014, 2015, 2016, 2017, 201...
$ industry    <chr> "Drug Manufacturers - Specialty & Generic", "...

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"

Select subset of variables to work with

For drug_cos select (in this order): ticker, year, grossmargin
Extract observations for 2018
Assign output to drug_subset
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)

Keep all the rows and columns drug_subset join with columns in health_subset

drug_subset  %>% left_join(health_subset)

   ticker year grossmargin     revenue          gp
1     ZTS 2018       0.672  5825000000  3914000000
2    PRGO 2018       0.387  4731700000  1831500000
3     PFE 2018       0.790 53647000000 42399000000
4     MYL 2018       0.350 11433900000  4001600000
5     MRK 2018       0.681 42294000000 28785000000
6     LLY 2018       0.738 24555700000 18125700000
7     JNJ 2018       0.668 81581000000 54490000000
8    GILD 2018       0.781 22127000000 17274000000
9     BMY 2018       0.710 22561000000 16014000000
10   BIIB 2018       0.865 13452900000 11636600000
11   AMGN 2018       0.827 23747000000 19646000000
12    AGN 2018       0.861 15787400000 13596000000
13   ABBV 2018       0.764 32753000000 25035000000
                                   industry
1  Drug Manufacturers - Specialty & Generic
2  Drug Manufacturers - Specialty & Generic
3              Drug Manufacturers - General
4  Drug Manufacturers - Specialty & Generic
5              Drug Manufacturers - General
6              Drug Manufacturers - General
7              Drug Manufacturers - General
8              Drug Manufacturers - General
9              Drug Manufacturers - General
10             Drug Manufacturers - General
11             Drug Manufacturers - General
12             Drug Manufacturers - General
13             Drug Manufacturers - General

Question: join_ticker

start with drug_cos
Extract observations for the ticker BIIB from drug_cos
Assign output to the variable drug_cos_subset

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

Display drug_cos_subset

drug_cos_subset

  ticker       name             location ebitdamargin grossmargin
1   BIIB Biogen Inc Massachusetts; U.S.A        0.404       0.908
2   BIIB Biogen Inc Massachusetts; U.S.A        0.402       0.901
3   BIIB Biogen Inc Massachusetts; U.S.A        0.432       0.876
4   BIIB Biogen Inc Massachusetts; U.S.A        0.475       0.879
5   BIIB Biogen Inc Massachusetts; U.S.A        0.493       0.885
6   BIIB Biogen Inc Massachusetts; U.S.A        0.491       0.871
7   BIIB Biogen Inc Massachusetts; U.S.A        0.495       0.867
8   BIIB Biogen Inc Massachusetts; U.S.A        0.511       0.865
  netmargin   ros   roe year
1     0.245 0.333 0.204 2011
2     0.250 0.335 0.211 2012
3     0.269 0.355 0.233 2013
4     0.302 0.404 0.294 2014
5     0.330 0.437 0.321 2015
6     0.323 0.431 0.322 2016
7     0.207 0.407 0.209 2017
8     0.329 0.435 0.334 2018

Use left_join to combine the rows and columns of drug_cos_subset with the columns of health_cos
Assign the output to combo_df

combo_df  <- drug_cos_subset  %>% 
  left_join(health_cos)

Display combo_df

combo_df

  ticker       name             location ebitdamargin grossmargin
1   BIIB Biogen Inc Massachusetts; U.S.A        0.404       0.908
2   BIIB Biogen Inc Massachusetts; U.S.A        0.402       0.901
3   BIIB Biogen Inc Massachusetts; U.S.A        0.432       0.876
4   BIIB Biogen Inc Massachusetts; U.S.A        0.475       0.879
5   BIIB Biogen Inc Massachusetts; U.S.A        0.493       0.885
6   BIIB Biogen Inc Massachusetts; U.S.A        0.491       0.871
7   BIIB Biogen Inc Massachusetts; U.S.A        0.495       0.867
8   BIIB Biogen Inc Massachusetts; U.S.A        0.511       0.865
  netmargin   ros   roe year     revenue          gp        rnd
1     0.245 0.333 0.204 2011  5048634000  4581854000 1219602000
2     0.250 0.335 0.211 2012  5516461000  4970967000 1334919000
3     0.269 0.355 0.233 2013  6932200000  6074500000 1444100000
4     0.302 0.404 0.294 2014  9703300000  8532300000 1893400000
5     0.330 0.437 0.321 2015 10763800000  9523400000 2012800000
6     0.323 0.431 0.322 2016 11448800000  9970100000 1973300000
7     0.207 0.407 0.209 2017 12273900000 10643900000 2253600000
8     0.329 0.435 0.334 2018 13452900000 11636600000 2597200000
   netincome      assets liabilities   marketcap
1 1234428000  9049604000  2622617000 26733054258
2 1380033000 10130118000  3166323000 34630691473
3 1862300000 11863335000  3242497000 66038521266
4 2934800000 14314700000  3500700000 80162952906
5 3547000000 19504800000 10129900000 68286367442
6 3702800000 22876800000 10748200000 61699770755
7 2539100000 23652600000 11054500000 67370207502
8 4430700000 25288900000 12257300000 60630142487
                      industry
1 Drug Manufacturers - General
2 Drug Manufacturers - General
3 Drug Manufacturers - General
4 Drug Manufacturers - General
5 Drug Manufacturers - General
6 Drug Manufacturers - General
7 Drug Manufacturers - General
8 Drug Manufacturers - General

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

Assign the company name to co_name

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

Assign the company location to co_location

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 bricks blanks below. When you knit the document the results of the commands will be displayed in your test

The company Biogen Inc is located in Massachusetts; U.S.A and is a member of the Drug Manufacturers - General industry group.

Start with combo_df
Select variables (in this order): year, grossmargin, netmargin, revenue, gp, netincome
Assign the output to combo_df_subset

combo_df_subset  <- combo_df  %>% 
  select(year, grossmargin, netmargin, revenue, gp, netincome)

Display combo_df_subset

combo_df_subset

  year grossmargin netmargin     revenue          gp  netincome
1 2011       0.908     0.245  5048634000  4581854000 1234428000
2 2012       0.901     0.250  5516461000  4970967000 1380033000
3 2013       0.876     0.269  6932200000  6074500000 1862300000
4 2014       0.879     0.302  9703300000  8532300000 2934800000
5 2015       0.885     0.330 10763800000  9523400000 3547000000
6 2016       0.871     0.323 11448800000  9970100000 3702800000
7 2017       0.867     0.207 12273900000 10643900000 2539100000
8 2018       0.865     0.329 13452900000 11636600000 4430700000

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)

  year grossmargin netmargin     revenue          gp  netincome
1 2011       0.908     0.245  5048634000  4581854000 1234428000
2 2012       0.901     0.250  5516461000  4970967000 1380033000
3 2013       0.876     0.269  6932200000  6074500000 1862300000
4 2014       0.879     0.302  9703300000  8532300000 2934800000
5 2015       0.885     0.330 10763800000  9523400000 3547000000
6 2016       0.871     0.323 11448800000  9970100000 3702800000
7 2017       0.867     0.207 12273900000 10643900000 2539100000
8 2018       0.865     0.329 13452900000 11636600000 4430700000
  grossmargin_check close_enough
1         0.9075433         TRUE
2         0.9011152         TRUE
3         0.8762730         TRUE
4         0.8793194         TRUE
5         0.8847619         TRUE
6         0.8708424         TRUE
7         0.8671979         TRUE
8         0.8649882         TRUE

Create the variable netmargin_check to compare with the variable netmargin. They should be equal.
Create the variable close_enough to check that the absolute value of the difference between netmargin_check and netmargin is less than 0.001

combo_df_subset  %>% 
  mutate(netmargin_check = netincome / revenue, 
         close_enough = abs(netmargin_check - netmargin) < 0.001)

  year grossmargin netmargin     revenue          gp  netincome
1 2011       0.908     0.245  5048634000  4581854000 1234428000
2 2012       0.901     0.250  5516461000  4970967000 1380033000
3 2013       0.876     0.269  6932200000  6074500000 1862300000
4 2014       0.879     0.302  9703300000  8532300000 2934800000
5 2015       0.885     0.330 10763800000  9523400000 3547000000
6 2016       0.871     0.323 11448800000  9970100000 3702800000
7 2017       0.867     0.207 12273900000 10643900000 2539100000
8 2018       0.865     0.329 13452900000 11636600000 4430700000
  netmargin_check close_enough
1       0.2445073         TRUE
2       0.2501664         TRUE
3       0.2686449         TRUE
4       0.3024538         TRUE
5       0.3295305         TRUE
6       0.3234225         TRUE
7       0.2068699         TRUE
8       0.3293491         TRUE

Question: summarize_industry

Fill in the blanks

*Put the command you use in the Rchunks in the Rmd file for this quiz

Use the health_cos data
For each indusrty caluculate mean_grossmargin_percent = mean(gp / revenue) 100 median_grossmargin_percent = median(gp / revenue) 100 min_grossmargin_percent = min(gp / revenue) 100 max_grossmargin_percent = max(gp / revenue) 100

health_cos  %>% 
  group_by(industry) %>% 
  summarize(mean_grossmargin_percent = mean(gp / revenue) *100,
  median_grossmargin_percent = median(gp / revenue) *100,
  min_grossmargin_percent = min(gp / revenue) *100,
  max_grossmargin_percent = max(gp / revenue) *100)

# A tibble: 9 x 5
  industry mean_grossmargi~ median_grossmar~ min_grossmargin~
* <chr>               <dbl>            <dbl>            <dbl>
1 Biotech~             92.5            92.7             81.7 
2 Diagnos~             50.5            52.7             28.0 
3 Drug Ma~             75.4            76.4             36.8 
4 Drug Ma~             47.9            42.6             34.3 
5 Healthc~             20.5            19.6             10.0 
6 Medical~             55.9            37.4             28.1 
7 Medical~             70.8            72.0             53.2 
8 Medical~             10.4             5.38             2.49
9 Medical~             53.9            52.8             40.5 
# ... with 1 more variable: max_grossmargin_percent <dbl>

mean_grossmargin_percent for the industry Medical Devices is 70.8%
median_grossmargin_percent for the industry Medical Devices is 72.0%
min_grossmargin_percent for the industry Medical Devices is 53.2%
max_grossmargin_percent for the industry Medical Devices is 84.7%

Question: inline_ticker

Fill in the blanks
Use the health_cos data
Extract the observations for the ticker ZTS from health_cos and assign to the variable health_cos_subset

health_cos_subset  <- health_cos  %>% 
  filter(ticker == "ZTS")

Display health_cos_subset

health_cos_subset

  ticker       name   revenue        gp      rnd netincome     assets
1    ZTS Zoetis Inc 4.233e+09 2.581e+09 4.27e+08 2.450e+08 5.7110e+09
2    ZTS Zoetis Inc 4.336e+09 2.773e+09 4.09e+08 4.360e+08 6.2620e+09
3    ZTS Zoetis Inc 4.561e+09 2.892e+09 3.99e+08 5.040e+08 6.5580e+09
4    ZTS Zoetis Inc 4.785e+09 3.068e+09 3.96e+08 5.830e+08 6.5880e+09
5    ZTS Zoetis Inc 4.765e+09 3.027e+09 3.64e+08 3.390e+08 7.9130e+09
6    ZTS Zoetis Inc 4.888e+09 3.222e+09 3.76e+08 8.210e+08 7.6490e+09
7    ZTS Zoetis Inc 5.307e+09 3.532e+09 3.82e+08 8.640e+08 8.5860e+09
8    ZTS Zoetis Inc 5.825e+09 3.914e+09 4.32e+08 1.428e+09 1.0777e+10
  liabilities   marketcap year
1   1.975e+09          NA 2011
2   2.221e+09          NA 2012
3   5.596e+09 16345223371 2013
4   5.251e+09 21572007994 2014
5   6.822e+09 23860348635 2015
6   6.150e+09 26434855920 2016
7   6.800e+09 35104245170 2017
8   8.592e+09 41097768446 2018
                                  industry
1 Drug Manufacturers - Specialty & Generic
2 Drug Manufacturers - Specialty & Generic
3 Drug Manufacturers - Specialty & Generic
4 Drug Manufacturers - Specialty & Generic
5 Drug Manufacturers - Specialty & Generic
6 Drug Manufacturers - Specialty & Generic
7 Drug Manufacturers - Specialty & Generic
8 Drug Manufacturers - Specialty & Generic

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

Run the code below

health_cos_subset  %>% 
  distinct(name) %>% 
  pull(name)

[1] "Zoetis Inc"

Assign the output to co_name

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

health_cos_subset  %>% 
  distinct(industry) %>% 
  pull()

[1] "Drug Manufacturers - Specialty & Generic"

The name of the company Zoetis Inc is in the Drug Manufacturers - General

Joining Data

Steps 1-6

Question: join_ticker

Question: summarize_industry

Question: inline_ticker