0

I have the cross tabulation below showing number of people that fall into each quintile for each location

                            Quintile
Location        Fourth Quintile Highest Quintile Lowest Quintile Second Quintile Third Quintile
  ACT          47926            56799           31146           28143          30494
  GA           117952            90323          106448          115168         125475
  GB           216805           204766          190530          195830         212645
  GD           17777            17707           10565           11362          13650
  GH           19393            14322           19122           19831          21443
  GM           403012           363902          385852          384362         396915
  GP           186868           256448          174139          165962         161256
  GS           432012           468749          399487          400870         411057
  NSW          192504           180875          218228          232050         217500
  NT           5759             5358            5151            4876           5279
  Qld          191812           192972          210659          222899         210492
  SA           25105            18248           32495           32662          30916
  Tas.         21130            14452           26584           27405          26695
  Vic.         111428            74023          131593          132609         126082
  WA           37837            60160           45700           43054          37517

I have another dataframe containing total number of people for each location. 

Location Total 
GS 2112175  
NSW 1041157  
GM 1934043 
Vic  575735  
GB 1020576  
Qld 1028834  
GA  555366  
SA  139426  
Perth  944673  
WA  224268  
GH   94111  
Tas.  116266  
GD   71061  
NT   26423  
ACT  194508

I want to divide the cross tab by the total for the corresponding location so the cross tab reflects a proportion/percentage of people in each quintile for each location. So for example I would end up with Fourth quintile location ACT being 47926/194508 = 0.24... and do this for each. How can I do this in R?

htown
  • 1
  • 1
  • Please provide an easy way for us to access your input data. `dput()` is often a good option. Also, please provide us with an example of your desitred result. What have you tried so far and why didn't it work? You reference "people" in the last line of your post. How do "people" relate to the data in your post. [This](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610) may help[ you create a simple self-contained example. – Limey Jun 01 '20 at 06:59

1 Answers1

0

You can join the two dataframes by 'Location' and divide each column by Total.

library(dplyr)
df %>%
  left_join(total_df, by = 'Location') %>%
  mutate_at(vars(`Fourth Quintile`:`Third Quintile`), ~./Total)
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213