Sum a variable by group and plot it

[Iva Mihaylova]

I have a dataset (attached in .dta format) of all aid donors (the numeric variable donors1) to the Democratic Republic of Congo (variable recipient) for two years, 2012 and 2013. The aid is the variable goal_2. It contains sums of aid in US dollars.

The rows with aid for goal_2 are not summed per donor. I mean if the USA sends 100 times aid to Congo, there will be 100 rows with data for goal_2. This makes it difficult to notice tendencies.

I wish to create a variable that sums all aid (variable goal_2) per donor per year and plots, for example in a bar chart, the variable goal_2 for the top 15 donors per year.

I will be very grateful if you show me how to do this.

[Carlo Lazzaro]

Iva:
see -collapse- (and save a copy of yuor original dataset before going -collapse-).

[Maarten Buis]

We don't sent data as a Stata dataset, instead we use dataex in Stata. Here is the example dataset for others to use. That way we don't have to download some dataset from an unknown person on the internet...

* Example generated by -dataex-. For more info, type help dataex
clear
clear frames
input long donor1 int year float goal_2
 4 2013     5305
 4 2013 1150.556
 4 2013 920655.2
 4 2013     2013
 4 2013 37663.12
 3 2013 44969.67
 3 2013 9380.643
 3 2013  29495.3
 3 2013  2746872
 3 2013  1812176
 3 2013 140776.5
 3 2013 37513.93
 3 2013 12765.67
 3 2013 676628.6
 3 2013 159723.7
 3 2013 22408.56
 3 2013   161950
 3 2013    57096
 3 2013 161151.7
 3 2013    14811
 3 2013 2553.444
 3 2013 143903.7
 3 2013   131865
 3 2013 13514.44
 3 2013 115111.7
 3 2013 8496.667
 3 2013  6482.25
 3 2013  2626026
 3 2013  1278835
 3 2013 143227.9
 3 2013 738917.7
 3 2013 11348184
 3 2013  1185532
 3 2013 382714.4
 3 2013 111223.7
 3 2013 533018.4
 3 2013  45478.8
 3 2013 3570.682
 3 2013    54411
 3 2013 11040.67
 3 2013   102551
 3 2013   102551
 3 2013     2568
 3 2013 9284.786
 3 2013   102551
 3 2013 23080.56
 3 2013    35932
 3 2013    19079
 3 2013    62786
 3 2013    41835
 3 2013    59926
 3 2013 10212.22
 3 2013 11233.44
 3 2013 9573.667
 3 2013   119094
 3 2013 6223.778
 3 2013   770273
20 2013  6243164
20 2013 7936.111
20 2013 43319.44
20 2013 3500.556
20 2013 198296.7
20 2013 131719.4
20 2013 401688.9
20 2013 7196.111
20 2013 7781.111
20 2013 68.88889
20 2013 13198.33
20 2013 3282.222
20 2013 3971.111
20 2013 8033.889
20 2013 64817.14
20 2013    44268
20 2013   561660
 6 2013  7764.75
 6 2013   388178
 6 2013   548915
 6 2013   905885
15 2013 107891.9
15 2013 87493.75
15 2013    13687
16 2012 139352.2
16 2012   683.25
12 2012    83125
12 2012   184245
12 2012   524832
21 2012    46764
21 2012    22587
21 2012 34559.14
21 2012     47.5
11 2012 415623.8
11 2012    49600
 3 2012    57156
 3 2012  9540.25
 3 2012   247469
 3 2012  1654079
 3 2012   186051
 3 2012 408986.3
 3 2012 189789.4
 3 2012 95302.43
 3 2012   199991
 3 2012   197092
 3 2012 9279.444
 3 2012  2274386
 3 2012  95304.8
 3 2012 694059.9
 3 2012 867180.2
 3 2012 142310.8
 3 2012   247469
 3 2012 123734.5
 3 2012      604
 3 2012   372999
 3 2012 310004.3
 3 2012 132466.5
 3 2012 12845.64
 3 2012   119451
 3 2012 99044.44
 3 2012 84095.79
 3 2012     1304
 4 2012   378769
 4 2012 230039.1
13 2012 63576.86
 7 2012 562677.5
 7 2012  1371905
18 2012   339617
 7 2012  1371905
 7 2012  1097524
18 2012   211611
 7 2012  1618928
 7 2012 188637.1
 7 2012 344936.4
 7 2012   205786
 7 2012 27438104
18 2012  1599249
18 2012    37860
18 2012   622468
18 2012   108231
 1 2012      386
 1 2012 33182.25
 7 2012 4254.167
17 2012 76573.71
 7 2013  28291.2
13 2013 286604.2
10 2013 191315.1
10 2013 30795.29
10 2013 67121.21
10 2013 111080.4
10 2013 16669.71
10 2013   656.25
12 2013 178716.8
12 2013 116450.7
14 2013 73731.75
15 2013 47438.33
15 2013    76536
15 2013 9839.385
15 2013 1055.333
15 2013 17078.86
15 2013 925.3333
 1 2013 14456.25
18 2013 798567.4
18 2013 315479.1
18 2013 56996.57
18 2013 992681.1
11 2013 18112.29
11 2013  2426657
11 2013   4528.5
11 2013 66328.43
11 2013  82390.5
11 2013 242665.5
 7 2013   507255
 7 2013  51415.7
15 2012 390634.5
15 2012  1263064
 2 2012 30288.89
 6 2012   684708
 6 2012  1369415
 6 2012   821649
15 2012    74052
15 2012 53912.44
 5 2012    47379
 9 2012 608505.9
10 2012     4592
10 2012    64812
10 2012   270050
10 2012 249958.5
10 2012 172821.8
10 2012 27134.64
10 2012 22684.36
10 2012  1414082
10 2012 37016.25
10 2012 8954.786
10 2012     1471
 8 2012  2446796
 8 2012  3314769
 8 2012  1108677
 8 2012  1959406
 8 2012 625639.4
 8 2012 183278.9
 8 2012  1320707
14 2012  2129328
20 2012  1167019
20 2012  1552197
20 2012  1218272
20 2012   767900
20 2012  1462063
20 2012  1851365
20 2012   274622
20 2012     4615
20 2012     1080
20 2012    32695
20 2012     1964
20 2012     7167
20 2012     6186
20 2012    50958
20 2012    10898
20 2012     3535
20 2012      884
20 2012    25430
20 2012     1571
20 2012  1336878
20 2012   842716
20 2012   435742
20 2012 709732.6
20 2012  4851640
20 2012 54546.67
20 2012 268370.6
20 2012 49092.22
20 2012 276552.8
20 2012  1523260
20 2012    261.5
20 2012 42955.75
20 2012  88365.6
20 2012  1665501
20 2012   664217
20 2012  2076403
20 2012  1057838
20 2012   146589
20 2012  1303790
20 2012   617776
20 2012   387926
20 2012   723422
20 2012   454201
20 2012   375555
20 2012 20544.75
20 2012   121061
20 2012   645955
20 2012   140109
20 2012    10162
20 2012    40648
20 2012   277764
20 2012   233482
20 2012    54885
20 2012  1503792
20 2012  1178605
20 2012   240355
20 2012      295
20 2012    17084
20 2012   586750
20 2012      393
20 2012    92097
20 2012   187434
20 2012      196
20 2012    13353
20 2012   591070
20 2012  1126077
20 2012   954352
20 2012   170841
20 2012   195878
20 2012    15906
20 2012   759456
20 2012   270007
20 2012   349831
20 2012    28473
20 2012     2258
20 2012     1473
20 2012      687
20 2012      295
20 2012   346885
20 2012  1226028
20 2012    34954
20 2012     9818
20 2012     2553
20 2012    60972
20 2012    27983
20 2012    15513
20 2012     5400
20 2012     1375
20 2012    19833
20 2012  1336093
20 2012  1039772
20 2012     1276
20 2012      491
20 2012   756511
19 2012   1935.5
 6 2012 10578.75
 6 2012 213.1111
 3 2012 4539.071
 3 2012     6753
 3 2012 13245.56
 3 2012 5298.222
 3 2012 6357.556
 3 2012 23344.22
 3 2012 13245.56
 3 2012 3178.778
 3 2012 3178.778
 3 2012     3406
 3 2012     1634
 3 2012    24696
 3 2012     9482
 3 2012 14835.33
 3 2012    28051
 3 2012      808
 7 2012 22683.43
end
label values donor1 donor1
label def donor1 1 "Australia", modify
label def donor1 2 "Austria", modify
label def donor1 3 "Belgium", modify
label def donor1 4 "Canada", modify
label def donor1 5 "Denmark", modify
label def donor1 6 "France", modify
label def donor1 7 "Germany", modify
label def donor1 8 "International Fund for Agricultural Development (IFAD)", modify
label def donor1 9 "Ireland", modify
label def donor1 10 "Italy", modify
label def donor1 11 "Korea", modify
label def donor1 12 "Luxembourg", modify
label def donor1 13 "Norway", modify
label def donor1 14 "OPEC Fund for International Development (OFID)", modify
label def donor1 15 "Spain", modify
label def donor1 16 "Switzerland", modify
label def donor1 17 "United Kingdom", modify
label def donor1 18 "United Nations Children`s Fund (UNICEF)", modify     
label def donor1 19 "United Nations Development Programme (UNDP)", modify
label def donor1 20 "United States", modify                               
label def donor1 21 "World Health Organization (WHO)", modify

We need to do some data preparation before we make our graph. I used labmask by Nick Cox for that. If you don't have, you will need to install it first (only once). The easiest way to do that is by typing in Stata search labmask and the third entry is the one you are looking for. Just follow the instructions. It also links to a really nice article that is pertinent to your problem.

// do the actual work
* we are going to drastically change the data
* in case you want to work with the original data afterwards, I will do that in
* a different frame

frame copy default tograph
frame change tograph


* set the scheme
set scheme s1color

* so we have made a copy of the data in the frame default to the frame tograph
* and we are now in the frame tograph
* when we are done we can go back to the frame default to get our original data

* now we sum the contribution by donor and year
collapse (sum) goal_2, by(donor1 year)

* The full name of some donors take up a lot of space in the graph
* lets replace it with just the acronym
label def donor1 8 "IFAD", modify
label def donor1 14 "OFID", modify
label def donor1 18 "UNICEF", modify     
label def donor1 19 "UNDP", modify
label def donor1 21 "WHO", modify  

* we sort the donors by their contribution in 2012
* this needs -labmask-, to install it type
* search labmask
sort year goal_2
gen Donor = _n if year == 2012
bysort donor1 (year) : replace Donor = Donor[1]
labmask Donor, values(donor1) decode

After that I just build my graph one step at the time

** now its graph time
replace goal_2 = goal_2/1e6    
twoway scatter Donor goal_2, by(year, note(""))          ///
   ylab(1/21, val angle(0) grid) ytitle("")              ///
   xtitle("total donations (milions {c S|}, log scale)") ///
   xscale(log) xlab( 0.01 0.1 1 10, format(%12.0gc))

[Iva Mihaylova]

Thank you very much, Maarten! Your graph is great.
OK, I will send data via dataex in Statalist in the future.