Talk:List of countries by intentional homicide rate/LibreOffice Calc instructions

From WikiProjectMed
Jump to navigation Jump to search
See another, much more difficult method: How can I see only the latest year for each country in a spreadsheet?

These are instructions to create the latest-available-year table in this article:

See also: User:Timeshifter/Sandbox253.

See table reference: "dp-intentional-homicide-victims. dataUNODC". United Nations Office on Drugs and Crime. Use dataset link to download csv file. It is more complete than the table on the entry page. Make a copy, and work with it.

Open csv file with freeware LibreOffice Calc. Delete the columns for sources and country abbreviations. Delete the top rows for date and email. Delete by clicking a column or row header. Then right click and delete. Copy to a new file. It is important sometimes for reducing the file size. Save as ods file.

This leaves columns A through K. Filter in order:

  • "Victims of intentional homicide" in column D.
  • "Total" in column E.
  • "Total" in column G.

Click column head with one of the above words or phrases. Then: Data menu > More Filters > Standard Filter > Filter Criteria. Enter word or phrase in Value spot. Click OK. Only that phrase will remain in that column. Everything else in that column will be hidden. Then click "select all" from edit menu. Then edit menu > copy. Open a new file from the file menu. Then: edit menu > paste. Save with new name as .ods file. Open that file and repeat for the other 2 phrases. A new file has to be created between the use of each filter. This is necessary to greatly reduce the file size.

Delete columns D through H. This leaves 6 columns.

Sort column E into 2 sections: "Counts" and "Rate per 100,000 population".

Select the counts section: Select the lowest row number with counts. Then shift-click the highest row number with counts. Then: edit menu > cut. Then: new file > edit menu > paste. Do the same for rates.

Round off counts and rates

Round off the counts to whole numbers. See:

For the rates sheet here is how to round off to 3 decimal places without changing the single "0" to 0.000. You need to use custom cell formatting:

1. Select the column you wish to format.
2. Right-click.
3. Format Cells...
4. Under Format Code enter 0.000;-0.000;0;@
5. OK.

What exactly is happening with this?: 0.000;-0.000;0;@

The first part 0.000 is the format with three decimal places for positive numbers.
The second part -0.000 is the format with three decimal places for negative numbers (I understand that you probably don't have those, but you cannot skip the negative number part in such formatting strings).
The third part 0 is what to display in place of single zeros
The fourth part @ is what is displayed when it's not a number at all and it means that it will be displayed as is. E.g., if you have a cell with text 'word' in it, it will be displayed as 'word'.

3 decimal places is useful for distinguishing some country rates when sorted.

Latest available year

Here is how to get it down to one year per country (the latest year):

1. Sort the columns by Year descending.
2. Select column A (Location).
3. Data->More Filters->Standard Filter.
4. Column A value = Not Empty.
5. Options: No duplications.
6. OK.
7. Note that extra rows aren't deleted but hidden. So copy to a new file to lessen the size.

The number of rows in both sheets (rates and counts) should be the same.

Sort location column in ascending order in both sheets (rates and counts). Copy the counts column to the rates sheet. Select counts column head > edit menu > copy. In rates sheet: select empty column head > edit menu > paste.

Move columns to this order left to right: Location. Rate. Count. Year. Region. Subregion. Columns can be moved by clicking the column head. Then hold down the alt key and drag via any DATA cell in the selected column. You can't drag via the column head.

Add links and flags

Copy sheet to visual editor in Wikipedia sandbox. Then add links to regions and subregions. Add links and flags to locations. See:

Clarify and alphabetize some location names

  • In the wikitext change [[Southern Asia]] to [[Southern Asia (UN geoscheme)|Southern Asia]].
  • Change {{flagg|us*eft|pref=Crime in|Holy See}} to {{flagg|us*eft|pref=Crime in|Vatican City|name=Holy See}}
  • Some manual alphabetization and linking is needed. This is caused by the flag/link templates clarifying the country names. Alphabetize:
East Timor. Macau. Hong Kong. Palestine. Tanzania.
  • Clarify and alphabetize the UK and Iraq entries:
Location
 United Kingdom *
 Northern Ireland *
 Scotland * 
{| class="wikitable sortable"
!Location 
|-
| {{flagg|us*eft|pref=Crime in|United Kingdom (England and Wales)}}
|-
| {{flagg|us*eft|pref=Crime in|United Kingdom (Northern Ireland)}}
|-
| {{flagg|us*eft|pref=Crime in|United Kingdom (Scotland)}}
|}

"England and Wales" can be made into a link: England and Wales.

Location
 United Kingdom *. England and Wales 
{| class="wikitable sortable"
!Location 
|-
|{{flagg|us*eft|pref=Crime in|United Kingdom (England and Wales)}}. [[England and Wales]]
|}
  • Clarify the Iraq entries:
Location
 Iraq
 Iraq
 Iraq 
{| class="wikitable sortable"
!Location 
|-
| {{flagg|us*eft|pref=Crime in|Iraq}}
|-
| {{flagg|us*eft|pref=Crime in|Iraq (Central Iraq)}} 
|-
| {{flagg|us*eft|pref=Crime in|Iraq (Kurdistan Region)}}
|}


Location
 Iraq
 Iraq (Central Iraq)
 Iraq (Kurdistan Region) 
{| class="wikitable sortable"
!Location 
| {{flagg|us*eft|pref=Crime in|Iraq}} 
|-
| {{flagg|us*eft|pref=Crime in|Iraq (Central Iraq)}} (Central Iraq) 
|-
| {{flagg|us*eft|pref=Crime in|Iraq (Kurdistan Region)}} (Kurdistan Region)
|}
Retrieved from "https://mdwiki.org/wiki/Talk:List_of_countries_by_intentional_homicide_rate/LibreOffice_Calc_instructions"