Import your data

data <- read_excel("../00_data/myData.xlsx")

Pivoting

Minimizing data

data %>%
    select(country, food_category, co2_emmission) %>%
    filter(country %in% c("Argentina", "Bermuda", "Japan"))
## # A tibble: 33 × 3
##    country   food_category            co2_emmission
##    <chr>     <chr>                            <dbl>
##  1 Argentina Pork                             37.2 
##  2 Argentina Poultry                          41.5 
##  3 Argentina Beef                           1712   
##  4 Argentina Lamb & Goat                      54.6 
##  5 Argentina Fish                              6.96
##  6 Argentina Eggs                             10.5 
##  7 Argentina Milk - inc. cheese              278.  
##  8 Argentina Wheat and Wheat Products         19.7 
##  9 Argentina Rice                             11.2 
## 10 Argentina Soybeans                          0   
## # ℹ 23 more rows
data_small <- data %>%
    select(country, food_category, co2_emmission) %>%
    filter(country %in% c("Argentina", "Bermuda", "Japan"))

Long to wide form

data_small %>% pivot_wider(names_from = food_category, values_from = co2_emmission)
## # A tibble: 3 × 12
##   country    Pork Poultry  Beef `Lamb & Goat`  Fish  Eggs `Milk - inc. cheese`
##   <chr>     <dbl>   <dbl> <dbl>         <dbl> <dbl> <dbl>                <dbl>
## 1 Argentina  37.2    41.5 1712           54.6  6.96  10.5                 278.
## 2 Bermuda   100.     35.0 1023.          94.9 53.0   13.7                 136.
## 3 Japan      73.0    20.9  282.           4.9 49.7   17.6                 103.
## # ℹ 4 more variables: `Wheat and Wheat Products` <dbl>, Rice <dbl>,
## #   Soybeans <dbl>, `Nuts inc. Peanut Butter` <dbl>
data_wide <- data_small %>% pivot_wider(names_from = food_category, values_from = co2_emmission)

Wide to long form

data_wide %>%
    pivot_longer(Pork:`Nuts inc. Peanut Butter`, names_to = "food_category", values_to = "co2_emmission")
## # A tibble: 33 × 3
##    country   food_category            co2_emmission
##    <chr>     <chr>                            <dbl>
##  1 Argentina Pork                             37.2 
##  2 Argentina Poultry                          41.5 
##  3 Argentina Beef                           1712   
##  4 Argentina Lamb & Goat                      54.6 
##  5 Argentina Fish                              6.96
##  6 Argentina Eggs                             10.5 
##  7 Argentina Milk - inc. cheese              278.  
##  8 Argentina Wheat and Wheat Products         19.7 
##  9 Argentina Rice                             11.2 
## 10 Argentina Soybeans                          0   
## # ℹ 23 more rows

Separating and Uniting

Unite two columns

data %>%
    unite(col = "newName", country:food_category, sep = "/", remove = FALSE)
## # A tibble: 1,430 × 5
##    newName                       country food_category consumption co2_emmission
##    <chr>                         <chr>   <chr>               <dbl>         <dbl>
##  1 Argentina/Pork                Argent… Pork                10.5          37.2 
##  2 Argentina/Poultry             Argent… Poultry             38.7          41.5 
##  3 Argentina/Beef                Argent… Beef                55.5        1712   
##  4 Argentina/Lamb & Goat         Argent… Lamb & Goat          1.56         54.6 
##  5 Argentina/Fish                Argent… Fish                 4.36          6.96
##  6 Argentina/Eggs                Argent… Eggs                11.4          10.5 
##  7 Argentina/Milk - inc. cheese  Argent… Milk - inc. …      195.          278.  
##  8 Argentina/Wheat and Wheat Pr… Argent… Wheat and Wh…      103.           19.7 
##  9 Argentina/Rice                Argent… Rice                 8.77         11.2 
## 10 Argentina/Soybeans            Argent… Soybeans             0             0   
## # ℹ 1,420 more rows
data_united <- data %>%
    unite(col = "newName", country:food_category, sep = "/", remove = FALSE)

Separate a column

data_united %>%
    separate(col = newName, into = c("country", "food_category"), sep = "/")
## # A tibble: 1,430 × 4
##    country   food_category            consumption co2_emmission
##    <chr>     <chr>                          <dbl>         <dbl>
##  1 Argentina Pork                           10.5          37.2 
##  2 Argentina Poultry                        38.7          41.5 
##  3 Argentina Beef                           55.5        1712   
##  4 Argentina Lamb & Goat                     1.56         54.6 
##  5 Argentina Fish                            4.36          6.96
##  6 Argentina Eggs                           11.4          10.5 
##  7 Argentina Milk - inc. cheese            195.          278.  
##  8 Argentina Wheat and Wheat Products      103.           19.7 
##  9 Argentina Rice                            8.77         11.2 
## 10 Argentina Soybeans                        0             0   
## # ℹ 1,420 more rows

Missing Values

read_excel("../00_data/dataMissing.xlsx")
## # A tibble: 33 × 4
##    country   food_category            consumption co2_emmission
##    <chr>     <chr>                          <dbl>         <dbl>
##  1 Argentina Pork                           10.5          37.2 
##  2 <NA>      Poultry                        38.7          41.5 
##  3 <NA>      Beef                           55.5        1712   
##  4 <NA>      Lamb & Goat                     1.56         54.6 
##  5 <NA>      Fish                            4.36          6.96
##  6 <NA>      Eggs                           11.4          10.5 
##  7 <NA>      Milk - inc. cheese            195.          278.  
##  8 <NA>      Wheat and Wheat Products      103.           19.7 
##  9 <NA>      Rice                            8.77         11.2 
## 10 <NA>      Soybeans                        0             0   
## # ℹ 23 more rows
data_missing <- read_excel("../00_data/dataMissing.xlsx")
data_missing %>% 
    fill(country, .direction = "down")
## # A tibble: 33 × 4
##    country   food_category            consumption co2_emmission
##    <chr>     <chr>                          <dbl>         <dbl>
##  1 Argentina Pork                           10.5          37.2 
##  2 Argentina Poultry                        38.7          41.5 
##  3 Argentina Beef                           55.5        1712   
##  4 Argentina Lamb & Goat                     1.56         54.6 
##  5 Argentina Fish                            4.36          6.96
##  6 Argentina Eggs                           11.4          10.5 
##  7 Argentina Milk - inc. cheese            195.          278.  
##  8 Argentina Wheat and Wheat Products      103.           19.7 
##  9 Argentina Rice                            8.77         11.2 
## 10 Argentina Soybeans                        0             0   
## # ℹ 23 more rows