Jump to content

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

From Wikipedia, the free encyclopedia
Computing desk
< October 27 << Sep | October | Nov >> October 29 >
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

[edit]

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

[edit]

Hi. Please see the country table here:

The source is here:

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:

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:
  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)[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:
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)[reply]
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:
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)[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 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