Wikipedia:Reference desk/Archives/Computing/2021 October 28

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.

October 28

How can I see only the latest year for each country in a spreadsheet?

Hi. Please see the country table here:

• List of countries by intentional homicide rate#By country, region, or dependent territory

The source is here:

• https://dataunodc.un.org/content/homicide-rate-option-2

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.

I wrote this section:

• Help:Table#Picking selected dates from massive .csv files

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)

There's probably a bunch of ways to do this, but here's the steps I would take:

1. Import the CSV into the spreadsheet. I assume Libre does this pretty much automatically; Excel certainly does.
2. Define the data set as a table (in Excel, CTRL+T). This is optional, but will make later steps easier.
3. Apply filters (in Excel CTRL+SHIFT+L).
4. If you want rates, filter to show counts and delete those rows; if you want counts, filter to show rates and delete.
5. You now have a clean list of data. Sort the year column, highest to lowest.
6. Here's the tricky part and you may want to do it across multiple columns, but it's possible to do it in one:
7. Do a VLOOKUP on the country to look up the year. Since VLOOKUP only returns the first value, it will return the last year.
8. 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.
9. The formula I used was =IF([@Year]=VLOOKUP([@Country],Table1[[Country]:[Year]],3,FALSE),"last year","")
10. 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)

@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)

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:

Help:Table#Adding flags and linking country names in country lists

And converting the finished spreadsheet table to a wikitable is easy. I wrote the Visual Editor section of Help:Table:

Help:Table#Tables and the Visual Editor (VE)

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:

List of countries by intentional homicide rate#By country, region, or dependent territory

Could you paste the Excel table you have so far into tab2wiki and copy the wikitext result to a user sandbox? From Help:Table:

Visual Editor will load very fast in empty sandboxes: Special:MyPage/Sandbox, Special:MyPage/Sandbox2, Special:MyPage/Sandbox3. As many as you want. ... To find all your sandboxes: Special:PrefixIndex/User: – click link, add user name to the spot labeled "Display pages with prefix:".

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.

--Timeshifter (talk) 17:11, 30 October 2021 (UTC)

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)

Matt Deres. Thanks for the table of data. I used it to update the Wikipedia article. For more info see:

User:Timeshifter/Sandbox165

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)

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 1.2 809 2018 CTS Adjusted Africa Eastern Africa United Republic of Tanzania 6.5 3439 2016 NSO Americas Northern America United States of America 5 16214 2018 FBI Americas Caribbean United States Virgin Islands 49.3 52 2012 MD Americas South America Uruguay 12.1 416 2018 CTS Asia Central Asia Uzbekistan 1.1 363 2017 CTS Americas South America Venezuela (Bolivarian Republic of) 36.7 10598 2018 CTS Asia South-Eastern Asia Viet Nam 1.5 1358 2011 WHO Asia Western Asia Yemen 6.8 1703 2013 NSO Africa Eastern Africa Zambia 5.4 853 2015 NP Africa Eastern Africa Zimbabwe 7.5 981 2012 WHO