Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.
Click the "bulk data download" link (name shows up when hovering over it) at the top right a couple times to download the file named:
homicide_total_rate_and_count.xls
For this homicide rate spreadsheet the latest year varies by country. I am wondering how I can quickly get the spreadsheet down to showing only the data for the latest year.
But I have limited knowledge of how to use spreadsheets. I would like to know how to get the latest year in LibreOffice Calc since I have it installed, and it is freeware.
Once I have the spreadsheet pared down to the latest year by country, then it is easy to copy it to the Wikipedia article in order to update it. --Timeshifter (talk) 21:35, 28 October 2021 (UTC)[reply]
There's probably a bunch of ways to do this, but here's the steps I would take:
Import the CSV into the spreadsheet. I assume Libre does this pretty much automatically; Excel certainly does.
Define the data set as a table (in Excel, CTRL+T). This is optional, but will make later steps easier.
Apply filters (in Excel CTRL+SHIFT+L).
If you want rates, filter to show counts and delete those rows; if you want counts, filter to show rates and delete.
You now have a clean list of data. Sort the year column, highest to lowest.
Here's the tricky part and you may want to do it across multiple columns, but it's possible to do it in one:
Do a VLOOKUP on the country to look up the year. Since VLOOKUP only returns the first value, it will return the last year.
Perform a check to see if the year you VLOOKUP'ed is the year of that row. If it is, you've got the last year.
The formula I used was =IF([@Year]=VLOOKUP([@Country],Table1[[Country]:[Year]],3,FALSE),"last year","")
I then copy-pasted values and used filtering to get rid of the extra lines.
Sorry to keep using Excel jargon, but many of the shortcuts and formula commands will be the same across many programs. Like, I'm pretty sure these steps would work for Google Sheets (also free). A couple of further notes: the rate values are currently in percentage points, like 5.9% is showing as 5.9, so you'd have to add in the % marks. Converting from spreadsheet into table will require another step, but there are free converters out there (for Excel, at least, and I assume they'd work for Libre). Matt Deres (talk) 16:57, 29 October 2021 (UTC)[reply]
@Timeshifter A bit more: you'll need to do further cleaning on the list. For example, some countries are on there multiple times due to focus on different regions and so on. There are four lines for the UK, 3 for China, 3 for Iraq, etc. In some cases that may be perfectly fine and expected, but you'll want to double-check them. You may also want to edit the names for brevity (ex: Iran, not "Iran (Islamic Republic of)" and so on). Matt Deres (talk) 17:16, 29 October 2021 (UTC)[reply]
Matt Deres. Thanks. There are various flag templates that wrap around the long country names, and only show the linked shortened country names. Adding those flag templates is easy and fast. See this section and the following section of Help:Table:
But my knowledge of spreadsheets is very limited, and I basically only know about LibreOffice Calc. What I know is pretty much described in that Help:Table section on VE, and some other sections.
So all I understand of what you wrote is up to the part of opening homicide_total_rate_and_count.xls (which Calc does automatically) and sorting by year.
I want both rates and counts. I see that the xls file has all the data needed to fill in the columns here:
And if you have the time, energy, and patience could you install LibreOffice Calc and see if it can do what Excel can do. It installs fast. See download info page. I always install the early adopter version, and its help file.
I'm not going to install a new program just because you don't feel like trying to follow instructions. :) The data set is below in table format. Luckily the sources for both the rate and count were the same, so we didn't need to double up those. Matt Deres (talk) 14:47, 2 November 2021 (UTC)[reply]
Matt Deres. Thanks for the table of data. I used it to update the Wikipedia article. For more info see:
I don't have Excel installed on my PC. I don't want to spend the money. Today I tried using the free version of Excel on the web for the first time. About all I could figure out was how to sort a column in descending or ascending order. As I said before I am a newb at spreadsheets, and only know some very basic stuff I use to edit tables on Wikipedia.
So I would need instructions that tell me exactly what to do with the Excel on the web. No missing steps. I would put those instructions in Help:Table. There are a lot of country, state, and province tables on Wikipedia and the web that use sources with multiple years. So it would greatly help with getting better, and more frequently updated, tables on Wikipedia. Many other newbs would help if they knew how to help. :) --Timeshifter (talk) 23:32, 2 November 2021 (UTC)[reply]
Extended content
The following discussion has been closed. Please do not modify it.
Region
Subregion
Country
Rate value
Count value
Year
Source
Asia
Southern Asia
Afghanistan
6.7
2474
2018
NSO
Europe
Southern Europe
Albania
2.3
66
2018
NSO
Africa
Northern Africa
Algeria
1.4
542
2015
CTS
Europe
Southern Europe
Andorra
0
0
2015
CTS
Africa
Middle Africa
Angola
4.8
1217
2012
NSO
Americas
Caribbean
Anguilla
28.3
4
2014
SDG
Americas
Caribbean
Antigua and Barbuda
11.1
10
2012
OAS
Americas
South America
Argentina
5.3
2362
2018
MoS
Asia
Western Asia
Armenia
1.7
50
2018
CTS
Americas
Caribbean
Aruba
1.9
2
2014
MD
Oceania
Australia and New Zealand
Australia
0.9
222
2018
CTS
Europe
Western Europe
Austria
1
86
2018
CTS
Asia
Western Asia
Azerbaijan
2.2
219
2018
CTS
Americas
Caribbean
Bahamas
32
122
2017
CTS
Asia
Western Asia
Bahrain
0.5
7
2014
MD
Asia
Southern Asia
Bangladesh
2.4
3830
2018
NP
Americas
Caribbean
Barbados
9.8
28
2018
CTS
Europe
Eastern Europe
Belarus
2.4
226
2018
CTS
Europe
Western Europe
Belgium
1.7
193
2017
CTS
Americas
Central America
Belize
37.8
142
2017
CTS
Africa
Western Africa
Benin
1.1
126
2017
CTS
Americas
Northern America
Bermuda
8.1
5
2017
CTS
Asia
Southern Asia
Bhutan
1.2
9
2018
CTS
Americas
South America
Bolivia (Plurinational State of)
6.2
686
2016
CTS
Europe
Southern Europe
Bosnia and Herzegovina
1.2
39
2018
CTS
Africa
Southern Africa
Botswana
15.2
303
2010
WHO
Americas
South America
Brazil
27.4
57358
2018
ABSP
Americas
Caribbean
British Virgin Islands
8.3
2
2006
MD
Asia
South-Eastern Asia
Brunei Darussalam
0.5
2
2013
WHO
Europe
Eastern Europe
Bulgaria
1.3
92
2018
CTS
Africa
Western Africa
Burkina Faso
1.3
240
2017
MoJ
Africa
Eastern Africa
Burundi
6.1
635
2016
SDG
Africa
Western Africa
Cabo Verde
6.8
37
2018
CTS
Asia
South-Eastern Asia
Cambodia
1.8
268
2011
WHO
Africa
Middle Africa
Cameroon
1.4
341
2017
CTS
Americas
Northern America
Canada
1.8
651
2018
NP
Americas
Caribbean
Cayman Islands
8.2
5
2014
NSO
Africa
Middle Africa
Central African Republic
20.1
913
2016
CTS
Europe
Northern Europe
Channel Islands
0
0
2010
NP
Americas
South America
Chile
4.4
825
2018
CTS
Asia
Eastern Asia
China
0.5
7525
2018
NSO
Asia
Eastern Asia
China, Hong Kong Special Administrative Region
0.7
48
2018
NP
Asia
Eastern Asia
China, Macao Special Administrative Region
0.3
2
2018
CTS
Asia
Eastern Asia
China, Taiwan Province of China
0.8
192
2015
SDG
Americas
South America
Colombia
25.3
12586
2018
CTS
Americas
Central America
Costa Rica
11.3
563
2018
NSO
Europe
Southern Europe
Croatia
0.6
24
2018
CTS
Americas
Caribbean
Cuba
5
572
2016
MoH
Americas
Caribbean
Curaçao
19
26
2007
PAHO
Asia
Western Asia
Cyprus
1.3
15
2018
CTS
Europe
Eastern Europe
Czechia
0.6
66
2017
CTS
Europe
Northern Europe
Denmark
1
58
2018
CTS
Americas
Caribbean
Dominica
26.6
19
2017
CTS
Americas
Caribbean
Dominican Republic
10
1068
2018
CTS
Americas
South America
Ecuador
5.8
991
2018
NSO
Africa
Northern Africa
Egypt
2.6
2207
2012
CTS
Americas
Central America
El Salvador
52
3340
2018
NP
Europe
Northern Europe
Estonia
2.1
28
2018
CTS
Africa
Southern Africa
Eswatini
11.6
130
2017
GSHRevision
Africa
Eastern Africa
Ethiopia
8.8
8153
2012
GOV Adjusted
Europe
Northern Europe
Finland
1.6
90
2018
CTS
Europe
Western Europe
France
1.2
779
2018
CTS
Americas
South America
French Guiana
13.2
30
2009
NP
Asia
Western Asia
Georgia
2.2
89
2018
CTS
Europe
Western Europe
Germany
0.9
788
2018
CTS
Africa
Western Africa
Ghana
2.1
609
2017
NP
Europe
Southern Europe
Gibraltar
3
1
2010
NP
Europe
Southern Europe
Greece
0.9
99
2018
CTS
Americas
Northern America
Greenland
5.3
3
2016
NSO
Americas
Caribbean
Grenada
10.8
12
2017
CTS
Americas
Caribbean
Guadeloupe
5.8
23
2016
GOV
Americas
Central America
Guatemala
22.5
3881
2018
NP
Africa
Western Africa
Guinea-Bissau
1.1
21
2017
CTS
Americas
South America
Guyana
14.2
111
2018
CTS
Americas
Caribbean
Haiti
6.7
743
2018
DPKO
Europe
Southern Europe
Holy See
0
0
2015
CTS
Americas
Central America
Honduras
38.9
3732
2018
CTS
Europe
Eastern Europe
Hungary
2.5
242
2017
CTS
Europe
Northern Europe
Iceland
0.9
3
2018
CTS
Asia
Southern Asia
India
3.1
41651
2018
MoI
Asia
South-Eastern Asia
Indonesia
0.4
1150
2017
CTS
Asia
Southern Asia
Iran (Islamic Republic of)
2.5
1936
2014
MD
Asia
Western Asia
Iraq
10.1
3339
2013
CTS
Asia
Western Asia
Iraq (Central Iraq)
10.1
3029
2014
CTS
Asia
Western Asia
Iraq (Kurdistan Region)
2.2
111
2013
CTS
Europe
Northern Europe
Ireland
0.9
42
2018
CTS
Europe
Northern Europe
Isle of Man
0
0
2016
NP
Asia
Western Asia
Israel
1.5
123
2017
NSO
Europe
Southern Europe
Italy
0.6
345
2018
CTS
Americas
Caribbean
Jamaica
43.9
1287
2018
NSO
Asia
Eastern Asia
Japan
0.3
334
2018
MoI
Asia
Western Asia
Jordan
1.4
133
2017
CTS
Asia
Central Asia
Kazakhstan
5.1
915
2017
CTS
Africa
Eastern Africa
Kenya
4.9
2533
2018
CTS
Europe
Southern Europe
Kosovo under UNSCR 1244
2.4
44
2018
CTS
Asia
Western Asia
Kuwait
1.8
61
2012
CTS
Asia
Central Asia
Kyrgyzstan
2.2
138
2018
CTS
Europe
Northern Europe
Latvia
4.4
84
2018
CTS
Asia
Western Asia
Lebanon
2.5
171
2018
CTS
Africa
Southern Africa
Lesotho
43.6
897
2015
NSO
Africa
Western Africa
Liberia
3.3
135
2012
UNMIL
Europe
Western Europe
Liechtenstein
2.6
1
2018
NSO
Europe
Northern Europe
Lithuania
4.6
128
2018
CTS
Europe
Western Europe
Luxembourg
0.3
2
2017
CTS
Africa
Eastern Africa
Malawi
1.8
279
2012
NP
Asia
South-Eastern Asia
Malaysia
2.1
627
2013
NP
Asia
Southern Asia
Maldives
0.7
3
2013
CTS
Europe
Southern Europe
Malta
1.6
7
2018
NSO
Americas
Caribbean
Martinique
2.8
11
2009
NP
Africa
Eastern Africa
Mauritius
2.9
37
2018
NSO
Africa
Eastern Africa
Mayotte
5.9
12
2009
NP
Americas
Central America
Mexico
29.1
36685
2018
NSO
Europe
Western Europe
Monaco
0
0
2015
CTS
Asia
Eastern Asia
Mongolia
6.2
196
2018
CTS
Europe
Southern Europe
Montenegro
2.2
14
2018
CTS
Americas
Caribbean
Montserrat
20.3
1
2012
MD
Africa
Northern Africa
Morocco
1.4
510
2018
CTS
Africa
Eastern Africa
Mozambique
3.5
849
2011
SDG
Asia
South-Eastern Asia
Myanmar
2.3
1198
2016
CTS
Africa
Southern Africa
Namibia
17.7
388
2012
NP
Asia
Southern Asia
Nepal
2.3
627
2016
CTS
Europe
Western Europe
Netherlands
0.6
100
2018
CTS
Oceania
Australia and New Zealand
New Zealand
0.7
35
2017
CTS
Americas
Central America
Nicaragua
7.2
453
2016
NP
Africa
Western Africa
Niger
4.4
788
2012
WHO
Africa
Western Africa
Nigeria
34.5
64201
2016
UNODC Estimate
Europe
Northern Europe
Norway
0.5
25
2018
CTS
Asia
Western Asia
Oman
0.3
13
2018
CTS
Asia
Southern Asia
Pakistan
3.9
8241
2018
NP
Americas
Central America
Panama
9.4
392
2018
CTS
Americas
South America
Paraguay
7.1
497
2018
CTS
Americas
South America
Peru
7.9
2487
2017
CTS
Asia
South-Eastern Asia
Philippines
6.5
6895
2018
CTS
Europe
Eastern Europe
Poland
0.7
277
2018
CTS
Europe
Southern Europe
Portugal
0.8
81
2018
CTS
Americas
Caribbean
Puerto Rico
21.1
641
2018
NSO
Asia
Western Asia
Qatar
0.4
9
2014
MD
Asia
Eastern Asia
Republic of Korea
0.6
309
2018
CTS
Europe
Eastern Europe
Republic of Moldova
4.1
166
2018
NSO
Europe
Southern Europe
Republic of North Macedonia
1.2
25
2018
CTS
Africa
Eastern Africa
Réunion
1.8
15
2009
NP
Europe
Eastern Europe
Romania
1.3
250
2018
CTS
Europe
Eastern Europe
Russian Federation
8.2
11964
2018
CTS
Africa
Eastern Africa
Rwanda
2.6
293
2015
NSO
Africa
Western Africa
Saint Helena
0
0
2009
NSO
Americas
Caribbean
Saint Kitts and Nevis
36.1
18
2012
OAS
Americas
Caribbean
Saint Lucia
21.4
39
2018
NSO
Americas
Caribbean
Saint Martin (French Part)
27.7
10
2016
GOV
Americas
Northern America
Saint Pierre and Miquelon
15.8
1
2009
NP
Americas
Caribbean
Saint Vincent and the Grenadines
36.5
40
2016
CTS
Europe
Southern Europe
San Marino
0
0
2011
WHO
Africa
Middle Africa
Sao Tome and Principe
3.3
6
2011
CTS
Asia
Western Asia
Saudi Arabia
1.3
419
2017
CTS
Africa
Western Africa
Senegal
0.3
39
2015
CTS
Europe
Southern Europe
Serbia
1.2
108
2018
CTS
Africa
Eastern Africa
Seychelles
12.5
12
2016
NSO
Africa
Western Africa
Sierra Leone
1.7
124
2015
SDG
Asia
South-Eastern Asia
Singapore
0.2
9
2018
CTS
Europe
Eastern Europe
Slovakia
1.1
62
2018
CTS
Europe
Southern Europe
Slovenia
0.5
10
2018
CTS
Africa
Southern Africa
South Africa
36.4
21036
2018
NP
Africa
Eastern Africa
South Sudan
14.9
1504
2012
NP
Europe
Southern Europe
Spain
0.6
290
2018
CTS
Asia
Southern Asia
Sri Lanka
2.4
514
2018
CTS
Asia
Western Asia
State of Palestine
0.5
24
2018
NSO
Africa
Northern Africa
Sudan
5.1
1702
2008
CTS
Americas
South America
Suriname
5.4
31
2017
CTS
Europe
Northern Europe
Sweden
1.1
108
2018
CTS
Europe
Western Europe
Switzerland
0.6
50
2018
CTS
Asia
Western Asia
Syrian Arab Republic
0.9
149
2018
CTS
Asia
Central Asia
Tajikistan
1.6
126
2011
CTS
Asia
South-Eastern Asia
Thailand
2.6
1787
2017
CTS
Asia
South-Eastern Asia
Timor-Leste
4.1
49
2015
NSO
Americas
Caribbean
Trinidad and Tobago
30.6
420
2015
CTS
Africa
Northern Africa
Tunisia
3.1
332
2012
WHO
Asia
Western Asia
Turkey
2.6
2133
2018
CTS
Asia
Central Asia
Turkmenistan
4.2
203
2006
TransMonEE
Americas
Caribbean
Turks and Caicos Islands
5.7
2
2014
MD
Africa
Eastern Africa
Uganda
10.5
4497
2018
NP
Europe
Eastern Europe
Ukraine
6.2
2751
2017
CTS
Asia
Western Asia
United Arab Emirates
0.5
44
2017
CTS
Europe
Northern Europe
United Kingdom (England and Wales)
1.2
726
2017
CTS
Europe
Northern Europe
United Kingdom (Northern Ireland)
1.2
23
2018
CTS
Europe
Northern Europe
United Kingdom (Scotland)
1.1
61
2018
CTS
Europe
Northern Europe
United Kingdom of Great Britain and Northern Ireland