Calculate ratio of values within one column-CodePudding

I have created a simple data frame with simulated GDP data for Costa Rica and the US, using the following code

gdp_test <- read.table(text = "Country, Year, GDP
costa_rica  1979  200
costa_rica  1980  210
costa_rica  1981  250
usa         1979  350
usa         1980  375
usa         1981  421", header=T)
gdp_test <- as.data.frame(gdp_test)

The output is as follows

    Country. Year. GDP
1 costa_rica  1979 200
2 costa_rica  1980 210
3 costa_rica  1981 250
4        usa  1979 350
5        usa  1980 375
6        usa  1981 421

What I would like to do is to create a new variable consisting of the ratio of each country's GDP, for each year, to the usa gdp for that same year (obviously the ratio wouldl be 1 for the usa every year).

Any ideas of how to do it? It is an easy task in Excel, but I have found no way of doing it withing R

I have not been able to write any code that would do the task

CodePudding user response：

That might do the trick, using tidyverse.

if(no_NA) {
Remove last pipe line
}

gdp_test %>%filter(Country.=="usa") %>% group_by(Year.) %>% select(-Country.) %>% 
left_join(gdp_test,by="Year.") %>% 
  rename(GDPus=GDP.x,GDP=GDP.y) %>% 
  mutate(ratio=GDP/GDPus) %>% ungroup() %>% 
  mutate(ratio=ifelse(ratio==1,NA,ratio))

CodePudding user response：

Here is a very clumsy way of getting the job done. I am sure there are much better ways of doing it. Help would be enormously appreciated.

gdp_test <- read.table(text = "Country, Year, GDP
costa_rica  1979  200
costa_rica  1980  210
costa_rica  1981  250
usa         1979  350
usa         1980  375
usa         1981  421", header=T)
gdp_test <- as.data.frame(gdp_test) %>%
  mutate(ID=row_number(),)

gdp_usa <- gdp_test$GDP[4:6]
usa <- as.data.frame(c(gdp_usa,gdp_usa)) %>% 
  mutate(ID=row_number(),)
gdp <-full_join(gdp_test,usa, by = "ID")
gdp <- gdp %>% mutate(ratio = GDP/gdp_usa)