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

These are instructions to create the latest-available-year table in this article: See also: User:Timeshifter/Sandbox253.
 * List of countries by intentional homicide rate.

See table reference: 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: 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.
 * "Victims of intentional homicide" in column D.
 * "Total" in column E.
 * "Total" in column G.

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:
 * Help:Creating tables.

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
 * 5. OK.

What exactly is happening with this?:
 * The first part  is the format with three decimal places for positive numbers.
 * The second part  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  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:
 * Help:Creating tables - Regions, subregions.
 * Help:Tables and locations - Locations.
 * Help:Tables and locations - "Crime in LOCATION" links.

Clarify and alphabetize some location names

 * In the wikitext change Southern Asia to Southern Asia.
 * Change to
 * 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:

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


 * Clarify the Iraq entries: