-1

I have some panel data in long format from 2009 - 2019 for 100+ countries.

       df <- structure(list(area_name = c("Afghanistan", "Afghanistan", "Afghanistan", 
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", 
"Afghanistan", "Albania", "Albania", "Albania", "Albania", "Albania", 
"Albania", "Albania", "Albania", "Albania"), area_code = c("AFG", 
"AFG", "AFG", "AFG", "AFG", "AFG", "AFG", "AFG", "AFG", "ALB", 
"ALB", "ALB", "ALB", "ALB", "ALB", "ALB", "ALB", "ALB"), area_group = c("Asia-Pacific", 
"Asia-Pacific", "Asia-Pacific", "Asia-Pacific", "Asia-Pacific", 
"Asia-Pacific", "Asia-Pacific", "Asia-Pacific", "Asia-Pacific", 
"Eastern Europe", "Eastern Europe", "Eastern Europe", "Eastern Europe", 
"Eastern Europe", "Eastern Europe", "Eastern Europe", "Eastern Europe", 
"Eastern Europe"), pillar_name = c("Governance", "Governance", 
"Governance", "Governance", "Governance", "Governance", "Investment Environment", 
"Investment Environment", "Investment Environment", "Governance", 
"Governance", "Governance", "Governance", "Governance", "Governance", 
"Investment Environment", "Investment Environment", "Investment Environment"
), element_name = c("Executive Constraints", "Government Effectiveness", 
"Government Integrity", "Political Accountability", "Regulatory Quality", 
"Rule of Law", "Contract Enforcement", "Investor Protection", 
"Property Rights", "Executive Constraints", "Government Effectiveness", 
"Government Integrity", "Political Accountability", "Regulatory Quality", 
"Rule of Law", "Contract Enforcement", "Investor Protection", 
"Property Rights"), year = c("score_2009", "score_2009", "score_2009", 
"score_2009", "score_2009", "score_2009", "score_2009", "score_2009", 
"score_2009", "score_2009", "score_2009", "score_2009", "score_2009", 
"score_2009", "score_2009", "score_2009", "score_2009", "score_2009"
), score = c(5.94, 5.6, 5.82, 3.85, 4.62, 5.68, 4.21, 4.51, 8.75, 
8.51, 9.87, 7.79, 11.5, 7.18, 6.37, 9.2, 11.3, 13.2)), row.names = c(NA, 
-18L), class = "data.frame")

My goal is to add another row for each country that averages the different scores for a given year.

In the end I would like it to look like this:

  area_name   area_code area_group     pillar_name            element_name             year       score
   <chr>       <chr>     <chr>          <chr>                  <chr>                    <chr>      <dbl>
 1 Afghanistan AFG       Asia-Pacific   Governance             Executive Constraints    score_2009  5.94
 2 Afghanistan AFG       Asia-Pacific   Governance             Government Effectiveness score_2009  5.60
 3 Afghanistan AFG       Asia-Pacific   Governance             Government Integrity     score_2009  5.82
 4 Afghanistan AFG       Asia-Pacific   Governance             Political Accountability score_2009  3.85
 5 Afghanistan AFG       Asia-Pacific   Governance             Regulatory Quality       score_2009  4.62
 6 Afghanistan AFG       Asia-Pacific   Governance             Rule of Law              score_2009  5.68
 7 Afghanistan AFG       Asia-Pacific   Investment Environment Contract Enforcement     score_2009  4.21
 8 Afghanistan AFG       Asia-Pacific   Investment Environment Investor Protection      score_2009  4.51
 9 Afghanistan AFG       Asia-Pacific   Investment Environment Property Rights          score_2009  8.75
10 Afghanistan AFG       Asia-Pacific   Avg                    Avg                      score_2009  5.44                                                                                                    
11 Albania     ALB       Eastern Europe Governance             Executive Constraints    score_2009  8.51
12 Albania     ALB       Eastern Europe Governance             Government Effectiveness score_2009  9.87
13 Albania     ALB       Eastern Europe Governance             Government Integrity     score_2009  7.79
14 Albania     ALB       Eastern Europe Governance             Political Accountability score_2009 11.5 
15 Albania     ALB       Eastern Europe Governance             Regulatory Quality       score_2009  7.18
16 Albania     ALB       Eastern Europe Governance             Rule of Law              score_2009  6.37
17 Albania     ALB       Eastern Europe Investment Environment Contract Enforcement     score_2009  9.20
18 Albania     ALB       Eastern Europe Investment Environment Investor Protection      score_2009 11.3 
19 Albania     ALB       Eastern Europe Investment Environment Property Rights          score_2009 13.2
20 Albania     ALB       Eastern Europe Avg                    Avg                      score_2009 9.44

I am fairly new to R and am unsure how to approach this. Any help would be greatly appreciated!

Eric
  • 2,699
  • 5
  • 17
asd7
  • 19
  • 5
  • 1
    Hard to say without a reproducible example, but using the pipe, you might try: `df %>% pivot_wider(names_from = area_group, values_from = score) %>% group_by(area_group) %>% tally() %>% mutate(Average = avg(score))%>% pivot_longer(specify_columns, names_from =, values_to =)`, where df if your dataframe – johnjohn Aug 14 '20 at 14:40

1 Answers1

2

You can try this:

library(tidyverse)
#Data
df <- structure(list(area_name = c("Afghanistan", "Afghanistan", "Afghanistan", 
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", 
"Afghanistan", "Albania", "Albania", "Albania", "Albania", "Albania", 
"Albania", "Albania", "Albania", "Albania"), area_code = c("AFG", 
"AFG", "AFG", "AFG", "AFG", "AFG", "AFG", "AFG", "AFG", "ALB", 
"ALB", "ALB", "ALB", "ALB", "ALB", "ALB", "ALB", "ALB"), area_group = c("Asia-Pacific", 
"Asia-Pacific", "Asia-Pacific", "Asia-Pacific", "Asia-Pacific", 
"Asia-Pacific", "Asia-Pacific", "Asia-Pacific", "Asia-Pacific", 
"Eastern Europe", "Eastern Europe", "Eastern Europe", "Eastern Europe", 
"Eastern Europe", "Eastern Europe", "Eastern Europe", "Eastern Europe", 
"Eastern Europe"), pillar_name = c("Governance", "Governance", 
"Governance", "Governance", "Governance", "Governance", "Investment Environment", 
"Investment Environment", "Investment Environment", "Governance", 
"Governance", "Governance", "Governance", "Governance", "Governance", 
"Investment Environment", "Investment Environment", "Investment Environment"
), element_name = c("Executive Constraints", "Government Effectiveness", 
"Government Integrity", "Political Accountability", "Regulatory Quality", 
"Rule of Law", "Contract Enforcement", "Investor Protection", 
"Property Rights", "Executive Constraints", "Government Effectiveness", 
"Government Integrity", "Political Accountability", "Regulatory Quality", 
"Rule of Law", "Contract Enforcement", "Investor Protection", 
"Property Rights"), year = c("score_2009", "score_2009", "score_2009", 
"score_2009", "score_2009", "score_2009", "score_2009", "score_2009", 
"score_2009", "score_2009", "score_2009", "score_2009", "score_2009", 
"score_2009", "score_2009", "score_2009", "score_2009", "score_2009"
), score = c(5.94, 5.6, 5.82, 3.85, 4.62, 5.68, 4.21, 4.51, 8.75, 
8.51, 9.87, 7.79, 11.5, 7.18, 6.37, 9.2, 11.3, 13.2)), row.names = c(NA, 
-18L), class = "data.frame")

The code:

df1 <- df %>% bind_rows(df %>% 
                   group_by(area_name,area_code,area_group,year) %>% 
                   summarise(pillar_name='Val.Avg',element_name='Val.Avg',score=mean(score))) %>%
  arrange(area_name,area_code,area_group)

Output:

     area_name area_code     area_group            pillar_name             element_name       year     score
1  Afghanistan       AFG   Asia-Pacific             Governance    Executive Constraints score_2009  5.940000
2  Afghanistan       AFG   Asia-Pacific             Governance Government Effectiveness score_2009  5.600000
3  Afghanistan       AFG   Asia-Pacific             Governance     Government Integrity score_2009  5.820000
4  Afghanistan       AFG   Asia-Pacific             Governance Political Accountability score_2009  3.850000
5  Afghanistan       AFG   Asia-Pacific             Governance       Regulatory Quality score_2009  4.620000
6  Afghanistan       AFG   Asia-Pacific             Governance              Rule of Law score_2009  5.680000
7  Afghanistan       AFG   Asia-Pacific Investment Environment     Contract Enforcement score_2009  4.210000
8  Afghanistan       AFG   Asia-Pacific Investment Environment      Investor Protection score_2009  4.510000
9  Afghanistan       AFG   Asia-Pacific Investment Environment          Property Rights score_2009  8.750000
10 Afghanistan       AFG   Asia-Pacific                Val.Avg                  Val.Avg score_2009  5.442222
11     Albania       ALB Eastern Europe             Governance    Executive Constraints score_2009  8.510000
12     Albania       ALB Eastern Europe             Governance Government Effectiveness score_2009  9.870000
13     Albania       ALB Eastern Europe             Governance     Government Integrity score_2009  7.790000
14     Albania       ALB Eastern Europe             Governance Political Accountability score_2009 11.500000
15     Albania       ALB Eastern Europe             Governance       Regulatory Quality score_2009  7.180000
16     Albania       ALB Eastern Europe             Governance              Rule of Law score_2009  6.370000
17     Albania       ALB Eastern Europe Investment Environment     Contract Enforcement score_2009  9.200000
18     Albania       ALB Eastern Europe Investment Environment      Investor Protection score_2009 11.300000
19     Albania       ALB Eastern Europe Investment Environment          Property Rights score_2009 13.200000
20     Albania       ALB Eastern Europe                Val.Avg                  Val.Avg score_2009  9.435556
Duck
  • 39,058
  • 13
  • 42
  • 84
  • 1
    how did you go about turning the data provided into a reprex? – Eric Aug 14 '20 at 14:44
  • 1
    @EricFletcher Hi, I used web scrapping :) – Duck Aug 14 '20 at 14:45
  • Didn't think of that! Perhaps SelectorGadget will work for me. Thanks! – Eric Aug 14 '20 at 14:46
  • @asd7 Great it helped!!! Just for future questions always include a sample of your data using `dput(yourdata)` or `dput(head(yourdata,20))` and paste that result in the question. It is easier to help with that :) – Duck Aug 14 '20 at 17:37
  • @Duck I will edit my question and add the data! Thank you again :) P.S. I deleted my original "thank you" comment since it says not to thank people in the comments. – asd7 Aug 14 '20 at 17:40