How do you pivot_wider where in cross-country, time-series data, with characters

Aga Breczko 0
Viewed 33

I am dealing with cross-country time series with character variables:

   Country_name                     Year Policy_area Type 
   <chr>                           <dbl> <chr>       <chr>

 1 Antigua and Barbuda              2012 FIN         SB   
 2 Antigua and Barbuda              2011 RTP         SB   
 3 Antigua and Barbuda              2012 RTP         SB   
 4 Antigua and Barbuda              2012 FP          SB   
 5 Antigua and Barbuda              2010 INS         SB   
 6 Antigua and Barbuda              2011 DEB         SB   
 7 Antigua and Barbuda              2012 TRANS       SB   
 8 Antigua and Barbuda              2012 RTP         SB   
 9 Antigua and Barbuda              2011 SOE         SB   
10 Antigua and Barbuda              2013 SOE         SB   
 … with 2,740 more rows

I need to transform this data into a wide format and modify character variables to numeric by counting them (names for new columns would become: FIN_SB, RTP_SB, FP_SB, DEB_SB, TRANS_SB, SOE_SB, FIN_SPC, FIN_PA, etc.).

Do you know any code that could help me count how many times each policy area (FIN, RTP, FP, INS, etc.) of a specific type (SB, SPC, PA) appears per year for each country?

Thank you in advance!

pivot character-encoding string r
1 Answers

We could unite the columns, use pivot_wider with values_fn as length

library(dplyr)
library(tidyr)
df1 %>% 
  unite(Policy_type, Policy_area, Type) %>%
  pivot_wider(names_from = Policy_type, values_from = Policy_type,
    values_fill = 0, values_fn = length)

-output

# A tibble: 4 × 9
  Country_name         Year FIN_SB RTP_SB FP_SB INS_SB DEB_SB TRANS_SB SOE_SB
  <chr>               <int>  <int>  <int> <int>  <int>  <int>    <int>  <int>
1 Antigua and Barbuda  2012      1      2     1      0      0        1      0
2 Antigua and Barbuda  2011      0      1     0      0      1        0      1
3 Antigua and Barbuda  2010      0      0     0      1      0        0      0
4 Antigua and Barbuda  2013      0      0     0      0      0        0      1

data

df1 <- structure(list(Country_name = c("Antigua and Barbuda", 
"Antigua and Barbuda", 
"Antigua and Barbuda", "Antigua and Barbuda", "Antigua and Barbuda", 
"Antigua and Barbuda", "Antigua and Barbuda", "Antigua and Barbuda", 
"Antigua and Barbuda", "Antigua and Barbuda"), Year = c(2012L, 
2011L, 2012L, 2012L, 2010L, 2011L, 2012L, 2012L, 2011L, 2013L
), Policy_area = c("FIN", "RTP", "RTP", "FP", "INS", "DEB", "TRANS", 
"RTP", "SOE", "SOE"), Type = c("SB", "SB", "SB", "SB", "SB", 
"SB", "SB", "SB", "SB", "SB")), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10"))
akrun0
Related