- Persist with getting data from NZ.Stat and Infoshare
- There is a lot of data for stories in policedata.nz
Write out the steps
- Get the median household income data
- Get the CPI data
- Combine the data
- Calculate the inflation adjusted value
We have the income (NZ.Stat) and the CPI (Infoshare) data
(I forgot how much of a pain it is to open NZ.Stat files - data is here)
- Load them both into separate workflow tabs
- Grrr commas in year
- Convert year column to date
- Actually not the right thing - we will come back to this
- Results of conversion
- Select Year and Value
- Convert weekly income to annual
- Drop value column
(We did this yesterday)
- Use a new tab
- Remove extra row
- And rename while we are cleaning up
- Filter out Q1,Q3,Q4
- It doesn't matter which quarter - just be consistent
- Filter using text contains
- Let's shorten the CPI column name
- Another rename
- Ohh lost filter
- Set column again
Start a new tab for joining
- Leave data loading tabs for data loading
Start a new tab using an existing tab
Join to the other tab
Ohoh not ready yet
Columns need to match
- Type, name, and content
Turn the year into a number without formatting
- Could do this back at beginning
Remove Q2 from years
Convert to number without formatting
Rename to Year
Oh Index is text
- Index is designed so it provides a ratio
- Think about whether number should get bigger or smaller
Divide through by year's index
Multiple by index now
Does that look right?
- Use the little plus to expand
- See more levels
- Select a level
- Select a sublevel
- What is happening?
- How can we download?
- Don't download the summary
- Download the full data
- What if we want to look at cities?
- Police stations report
- Use the variance to look for big changes
- Sort by variance
- Ignore big changes in small numbers
- Select the cities you are interested in
- Lower Hutt and Wellington too
Write a short pitch for a data-supported or data-driven article based on police data.
- No more than 150 words
- What is the (draft - more summary than headline) headline?
- Why is the data interesting/newsworthy?
- What other data will you use?
- What data processing do you expect to have to do?
Write the data-supported or data-driven article.
Rise in serious assaults
Burglarly town vs town
You don't need to interview people
But do say who you would interview and why
Final article should be 400 to 600 words
- Actual article could be less to leave room for interviews
Reach out to me if you have questions like:
- I think I should be able to download XX but I can't
- I want to calculate XX but I'm stuck