Joining data

Code For quiz 6

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 into R and assign to 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", "Zoet…
$ 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.366…
$ grossmargin  <dbl> 0.610, 0.640, 0.634, 0.641, 0.635, 0.659, 0.666…
$ netmargin    <dbl> 0.058, 0.101, 0.111, 0.122, 0.071, 0.168, 0.163…
$ ros          <dbl> 0.101, 0.171, 0.176, 0.195, 0.140, 0.286, 0.321…
$ roe          <dbl> 0.069, 0.113, 0.612, 0.465, 0.285, 0.587, 0.488…
$ 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", "Zoeti…
$ revenue     <dbl> 4233000000, 4336000000, 4561000000, 4785000000, …
$ gp          <dbl> 2581000000, 2773000000, 2892000000, 3068000000, …
$ rnd         <dbl> 427000000, 409000000, 399000000, 396000000, 3640…
$ netincome   <dbl> 245000000, 436000000, 504000000, 583000000, 3390…
$ assets      <dbl> 5711000000, 6262000000, 6558000000, 6588000000, …
$ liabilities <dbl> 1975000000, 2221000000, 5596000000, 5251000000, …
$ marketcap   <dbl> NA, NA, 16345223371, 21572007994, 23860348635, 2…
$ year        <dbl> 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, …
$ industry    <chr> "Drug Manufacturers - Specialty & Generic", "Dru…
  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 drug_cos select (in this order):ticker,year,grossmargin

Extract observations for 2018

Assign output todrug_subset

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

Extract observations for 2018

Assign output tohealth_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 the columns in health_subset
drug_subset %>% left_join(health_subset)
# A tibble: 13 × 6
   ticker  year grossmargin     revenue          gp industry          
   <chr>  <dbl>       <dbl>       <dbl>       <dbl> <chr>             
 1 ZTS     2018       0.672  5825000000  3914000000 Drug Manufacturer…
 2 PRGO    2018       0.387  4731700000  1831500000 Drug Manufacturer…
 3 PFE     2018       0.79  53647000000 42399000000 Drug Manufacturer…
 4 MYL     2018       0.35  11433900000  4001600000 Drug Manufacturer…
 5 MRK     2018       0.681 42294000000 28785000000 Drug Manufacturer…
 6 LLY     2018       0.738 24555700000 18125700000 Drug Manufacturer…
 7 JNJ     2018       0.668 81581000000 54490000000 Drug Manufacturer…
 8 GILD    2018       0.781 22127000000 17274000000 Drug Manufacturer…
 9 BMY     2018       0.71  22561000000 16014000000 Drug Manufacturer…
10 BIIB    2018       0.865 13452900000 11636600000 Drug Manufacturer…
11 AMGN    2018       0.827 23747000000 19646000000 Drug Manufacturer…
12 AGN     2018       0.861 15787400000 13596000000 Drug Manufacturer…
13 ABBV    2018       0.764 32753000000 25035000000 Drug Manufacturer…

Question: **join_ticker*

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

drug_cos_subset
# A tibble: 8 × 9
  ticker name  location ebitdamargin grossmargin netmargin   ros   roe
  <chr>  <chr> <chr>           <dbl>       <dbl>     <dbl> <dbl> <dbl>
1 BIIB   Biog… Massach…        0.404       0.908     0.245 0.333 0.204
2 BIIB   Biog… Massach…        0.402       0.901     0.25  0.335 0.211
3 BIIB   Biog… Massach…        0.432       0.876     0.269 0.355 0.233
4 BIIB   Biog… Massach…        0.475       0.879     0.302 0.404 0.294
5 BIIB   Biog… Massach…        0.493       0.885     0.33  0.437 0.321
6 BIIB   Biog… Massach…        0.491       0.871     0.323 0.431 0.322
7 BIIB   Biog… Massach…        0.495       0.867     0.207 0.407 0.209
8 BIIB   Biog… Massach…        0.511       0.865     0.329 0.435 0.334
# … with 1 more variable: year <dbl>
combo_df <- drug_cos_subset %>%
 left_join(health_cos)

*Display combo_df

combo_df
# A tibble: 8 × 17
  ticker name  location ebitdamargin grossmargin netmargin   ros   roe
  <chr>  <chr> <chr>           <dbl>       <dbl>     <dbl> <dbl> <dbl>
1 BIIB   Biog… Massach…        0.404       0.908     0.245 0.333 0.204
2 BIIB   Biog… Massach…        0.402       0.901     0.25  0.335 0.211
3 BIIB   Biog… Massach…        0.432       0.876     0.269 0.355 0.233
4 BIIB   Biog… Massach…        0.475       0.879     0.302 0.404 0.294
5 BIIB   Biog… Massach…        0.493       0.885     0.33  0.437 0.321
6 BIIB   Biog… Massach…        0.491       0.871     0.323 0.431 0.322
7 BIIB   Biog… Massach…        0.495       0.867     0.207 0.407 0.209
8 BIIB   Biog… Massach…        0.511       0.865     0.329 0.435 0.334
# … with 9 more variables: year <dbl>, revenue <dbl>, gp <dbl>,
#   rnd <dbl>, netincome <dbl>, assets <dbl>, liabilities <dbl>,
#   marketcap <dbl>, industry <chr>

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

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

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

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


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

combo_df_subset
# A tibble: 8 × 6
   year grossmargin netmargin     revenue          gp  netincome
  <dbl>       <dbl>     <dbl>       <dbl>       <dbl>      <dbl>
1  2011       0.908     0.245  5048634000  4581854000 1234428000
2  2012       0.901     0.25   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.33  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

combo_df_subset %>%
 mutate(grossmargin_check = gp / revenue, 
 close_enough = abs(grossmargin_check - grossmargin) < 0.001)
# A tibble: 8 × 8
   year grossmargin netmargin     revenue          gp  netincome
  <dbl>       <dbl>     <dbl>       <dbl>       <dbl>      <dbl>
1  2011       0.908     0.245  5048634000  4581854000 1234428000
2  2012       0.901     0.25   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.33  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
# … with 2 more variables: grossmargin_check <dbl>,
#   close_enough <lgl>

combo_df_subset %>%
 mutate(netmargin_check = gp / revenue, 
 close_enough = abs(netmargin_check - netmargin) < 0.001)
# A tibble: 8 × 8
   year grossmargin netmargin     revenue          gp  netincome
  <dbl>       <dbl>     <dbl>       <dbl>       <dbl>      <dbl>
1  2011       0.908     0.245  5048634000  4581854000 1234428000
2  2012       0.901     0.25   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.33  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
# … with 2 more variables: netmargin_check <dbl>, 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 × 5
  industry       mean_netmargin_pe… median_netmargin… min_netmargin_p…
  <chr>                       <dbl>             <dbl>            <dbl>
1 Biotechnology               -4.66              7.62         -197.   
2 Diagnostics &…              13.1              12.3             0.399
3 Drug Manufact…              19.4              19.5           -34.9  
4 Drug Manufact…               5.88              9.01          -76.0  
5 Healthcare Pl…               3.28              3.37           -0.305
6 Medical Care …               6.10              6.46            1.40 
7 Medical Devic…              12.4              14.3           -56.1  
8 Medical Distr…               1.70              1.03           -0.102
9 Medical Instr…              12.3              14.0           -47.1  
# … with 1 more variable: max_netmargin_percent <dbl>