Recap

• Persist with getting data from NZ.Stat and Infoshare
• There is a lot of data for stories in policedata.nz

Inflation adjusted median household income

Write out the steps

• Get the median household income data
• Get the CPI data
• Combine the data
• Calculate the inflation adjusted value

Data

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

Income data

• Upload
• 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

CPI data

(We did this yesterday)

• Use a new tab
• Upload
• 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

Joining time

• 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

• Hooray

• Oh Index is text

• Calculation time

  • 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

• Tidy up

• Look charts

• Does that look right?

Stories in police data

Look at trends

• 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

Assessment

Today

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?

Final

Write the data-supported or data-driven article.

Suggestions

• Christmas assaults

• 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

• Office hours

• 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