Talk:List of countries by firearm-related death rate/LibreOffice Calc instructions for latest available year

From WikiProjectMed
Jump to navigation Jump to search

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

See also: User:Timeshifter/Sandbox251. Sandbox250. Sandbox249. Sandbox248.

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.

Open csv file with freeware LibreOffice Calc. Delete the columns for sources and country abbreviations. Delete the top rows for date and email. Copy to a new file. It is important sometimes for reducing the file size. Save as ods file.

Filter for

  • Victims of intentional homicide
  • Firearms or explosives - firearms
  • Rate per 100,000 population

Click column head with one of the above phrases. Then: Data menu > More Filters > Standard Filter > Filter Criteria. Enter 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.

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. You can now copy the remaining rows to another table.

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'.
Retrieved from "https://mdwiki.org/wiki/Talk:List_of_countries_by_firearm-related_death_rate/LibreOffice_Calc_instructions_for_latest_available_year"