NICAR-L Summary

In the NICAR-L forum, Jessica Glazer added a post on January 14 regarding formatting dates before 1900 in Excel.

Glazer wanted to find the best way to uniformly format the dates she had in her data set.

There were a few suggestions that were given and further explained throughout this thread.

The most common excel tip that was given to Glazer was to use a text field and the yyyy-mm-dd format. This format is said to have the benefit of sorting correctly as text and it ends up being chronological.

I learned that because it’s delimited, it would be easy to do date arithmetic. The downfall from doing this is the loss of automatic validation of a date field. This means that the one who is looking through the data set must find mistakes such as “1799-02-31” on their own.

In this thread, Thomas Wilburn said this format is harder to filter.

To solve that problem, other columns could be used for filtering. Multiple columns can be sorted or filtered on a single column with predictable delimiters.

A delimiter is a character or a series of characters that indicate the beginning or end of a statement or body set. Examples of delimiters are round brackets: ( )

Here is the formula that Wilburn left for sorting multiple columns in Excel:
"=CONCATENATE(A1, "-", TEXT(B1, "00"), "-", TEXT(C1,"00"))"

I learned that separate columns could be sorted and filtered. With single text columns sorting is no problem, but filtering isn’t fun.

Even though filtering isn’t the most “fun” of all things, it should always be done and will be useful.

Wilburn said, “I feel like I’ve rarely regretted breaking something out into separate fields, but I have regretted not doing it.”

Edward Borasky added an insightful tip about Excel’s date algorithm. An Excel spreadsheet will display data for February 29, 1900. This is because Excel thinks 1900 was a leap year and will continuously think this for 1800 and 1700.

After reading through this thread, I learned that data journalists are willing to help and answer a variety of questions. They could reply within a couple days, a few hours, or seconds after the question was posted.

In this thread that Glazer posted, Tim Henderson and Thomas Wilburn were having a mini-conversation between themselves. They were commenting on each other’s comments and questioned each other’s suggestions.

With that said, Glazer was not the only one who learned tips on excel formatting, so did Henderson, Wilburn and everyone else who posted in the thread.

The data journalism community is a group of very knowledgeable people who are experts in their own specific fields and are more than willing to share that knowledge to help others from around the world. 

Data Access Updates

Update #1

My final data project will be looking at the amount of foreign students in Canada.

I want to analyze the data set and compare the number of foreign student entries from 2012 and 2013 and see if there was an increase or decrease of foreign students coming to Canada.

The data set that I found includes information such as:

• The country where the students are originally from
• Amount of foreign student entries during 2012 (which is broken down into quarter 1, 2, 3, 4, year-to-date, and total throughout the year)
• Amount of foreign student entries during 2013 (which is broken down into quarter 1, 2, and year to date)
• Total amount of foreign student entries during each quarter and year-to-date

 The following are questions I want to ask the data:

• In 2012, Which country had the most foreign students in Canada? The lowest?
• Do the top and lowest ranked countries for amount of foreign students in Canada have to do with their economy?
• What field of study are these students applying for?
• What consists of "other countries"? (the lowest ranked country could be in this category)

Since this data set doesn't have quarter 3 and 4 for 2013, I would like to somehow find an updated data set or find the missing data to allow myself to give a full and complete analysis.


Update #2

The question that I chose to identify that is not answered by the data set I have is: 

What consists of "other countries" ?

This is an important question to have answers to because the country with the least amount of foreign students in Canada could be found in the "other countries" record. 

The "other countries" record was most likely created because there were many countries that had a very low count of foreign students in Canada and summing them up was probably a way to have a cleaner-looking data spreadsheet. Maybe if the all the countries were included in the data set, then there would be many records with a count of zero.

The data set states that Cuba has the least amount of foreign student entries in Canada, but this analysis could easily change once I find out what the "other countries" are, along with the number of foreign students in relation to those countries. 

This data would probably be on an Excel spreadsheet that includes all the countries found on the data set I currently have, plus the "other countries".

The agency that I think would answer my question and have additional information would be Citizenship and Immigration Canada (CIC).


Update #3

I wanted to get in contact with Citizenship and Immigration Canada (CIC) to get them to voluntarily provide the data I need.

The data I needed was: 
What consists of "other countries" ?

There was a phone number and email address provided on the CIC website, but it clearly stated that it was only to be used by the media. It said, " If you are seeking information about your case or general immigration information, please see our 'contact us' page."

On their "contact us" page, there is a search bar to easily search and access answers to questions, and phone numbers to specific CIC offices such as visa application centres, processing centres, and offices for appointments. 

I felt as though the data I needed couldn't be provided by these offices.

I decided to give that "for media only" phone number a call anyway. I said I am a journalism students looking for data regarding the total number of  foreign student entries in Canada by source of country in an excel spreadsheet format. 

Before I could go into further detail with what I was looking for, I was cut off and apologized to because they couldn't provided the data to a student. 

This made me wish I retracted my statement of being a journalism student. 

With that being a complete bust, I thought I would use the search bar on the "contact us" page and see what I could find. I typed in "Canada total foreign student entries."

On the third page, I found a link that surprisingly provided me with the data I was looking for. I now have a more detailed data set that I can work with, with more country records included.

There is still an "other countries" record with very small totals, but this is most likey due to privacy concerns.

These privacy concerns can be seen in cases such as this example:
If there is only one foreign student in Canada from Nauru, CIC may feel that listing that single student on a spreadsheet like that violates their privacy.

In my case, speaking directly with the agency didn't provide me with the data I needed, but curiously browsing through their website did.


Update #4

Stacy Ticne
Kwantlen Polytechnic University
12666 72nd Avenue
Surrey, B.C. V3W 2M8

March 22, 2014

Sarah Michaud
Access to Information and Privacy Coordinator
Access to Information and Privacy Division
Citizenship and Immigration Canada
Ottawa, Ontario  K1A 1L1

Dear Ms. Michaud

Under the Access to Information Act, please provide me with:

• A complete list of number of foreign students who entered Canada, by country of origin from 2003-2013.

If possible, please provide this list in electronic database format (ie. Excel, Access or CSV). I would like each country on a separate row with the relevant information on each country in adjoining columns, and the year as data fields.

If for some reason providing the actual student counts for each country could violate individual students' privacy, you can provide me with the percentages for each year instead.

Please do not provide me with PDF files.

I have enclosed a bulk cheque for $5 to cover the fee associated with my request.  

If you have any concerns, please email me at stacyticne@gmail.com

Please advise me when the material is available for release.

Sincerely,

Stacy Ticne