#datafriday
Every year the Canada Revenue Agency (CRA) gathers data from every charity in Canada. Charities complete a T3010 form. This form captures data like the total revenue, amounts and types of fundraising, and what the charity spends on a particular kind of compensation. In a previous post, I demonstrated how we could use KNIME to compare data from a couple of CRA Open Data sets. This week I’ll quickly show how we can put data into a database, and how we can extract a table of the highest gross revenue charity sectors.
Import Data To Database
For our exercise today, we’ll use CRA T3010 data from 2016, the most recent year that is posted. The first step that we want to take is to put the data into our database. To get the data into our database, we need to have a dynamic way to create tables. Fortunately, KNIME allows me to create tables based on the layout of the file. In the diagram below, we can see the workflow that operates as follows:
- A file of data from CRA is read
- I connect to the PostgreSQL database that I am putting the data into.
- Based on the row headings in the file, a table is created. KNIME creates a column for each row heading. (I do need to enter the table name each time manually, but that isn’t a problem. I’m just glad it is saving me the time of not having to name each of the rows.)
- Once my table and columns are created, the data is imported.
Extract Useful Information
Once the data is in the database, all I have to do is look at my CRA Data Dictionary, and Code Lists and create helpful queries. Below is the results of a query that shows the total revenue by the organization type.
| Category | Type | Group | count | Total Revenue |
|---|---|---|---|---|
| 20 | Teaching Institutions or Institutions of Learning | Education | 3,043 | 82,727,359,055 |
| 10 | Hospitals | Health | 430 | 56,723,090,977 |
| 19 | Health Organizations (not else classified) | Health | 762 | 39,036,920,650 |
| 11 | Services other than Hospitals | Health | 3,205 | 17,253,987,125 |
| 9 | Welfare Organizations (not else classified) | Health | 10,268 | 10,779,435,909 |
| 1 | Organizations providing care other than treatment | Welfare | 3,243 | 9,229,263,819 |
| 3 | (Welfare) Charitable Corporations | Health | 4,449 | 7,948,477,325 |
| 21 | Support of Schools and Education | Education | 3,912 | 5,481,277,495 |
| 59 | Community Organizations (not else classified) | Benefits to the Community & Other | 4,402 | 3,653,912,576 |
| 13 | (Health) Charitable Corporations | Health | 1,087 | 3,327,785,624 |
| 22 | Cultural Activities and Promotion of the Arts | Education | 4,351 | 3,178,470,900 |
| 29 | Education Organizations (not else classified) | Education | 1,055 | 2,551,936,121 |
| 39 | Other Denominations’ Congregations or Parishes (not else classified) | Religion – Churches and Other Places of Worship | 9,621 | 2,512,917,000 |
| 50 | Libraries, Museums and Other Repositories | Benefits to the Community & Other | 2,351 | 2,301,463,358 |
| 49 | Religious Organizations, (not else classified) | Religion – Churches and Other Places of Worship | 1,805 | 2,089,954,706 |
| 38 | Roman Catholic Parishes and Chapels | Religion – Churches and Other Places of Worship | 3,915 | 1,539,222,258 |
| 23 | (Education) Charitable Corporations | Education | 1,048 | 1,308,594,263 |
| 56 | Recreation, Playgrounds and Vacation Camps | Benefits to the Community & Other | 2,893 | 1,236,364,590 |
| 47 | Missionary Organizations and Propagation of Gospel | Religion – Churches and Other Places of Worship | 1,821 | 1,077,482,956 |
| 52 | Preservation of Sites, Beauty and Historical | Benefits to the Community & Other | 1,308 | 807,624,292 |
| 5 | (Welfare) Charitable Trusts | Health | 905 | 686,617,391 |
| 31 | Baptist Congregations | Religion – Churches and Other Places of Worship | 2,065 | 547,737,050 |
| 54 | Protection of Animals | Benefits to the Community & Other | 915 | 503,435,339 |
| 53 | (Community) Charitable Corporations | Benefits to the Community & Other | 561 | 494,243,309 |
| 44 | United Church Congregations | Religion – Churches and Other Places of Worship | 2,513 | 491,524,097 |
| 36 | Pentecostal Assemblies (Pentecostal Assemblies) of Canada only | Religion – Churches and Other Places of Worship | 1,351 | 472,462,095 |
| 30 | Anglican Parishes | Religion – Churches and Other Places of Worship | 1,827 | 409,914,180 |
| 40 | Salvation Army Temples | Religion – Churches and Other Places of Worship | 302 | 409,030,070 |
| 46 | Convents and Monasteries | Religion – Churches and Other Places of Worship | 471 | 366,016,539 |
| 57 | Temperance Associations | Benefits to the Community & Other | 403 | 331,781,572 |
| 34 | Mennonite Congregations | Religion – Churches and Other Places of Worship | 656 | 319,712,454 |
| 43 | (Religion) Charitable Organizations | Religion – Churches and Other Places of Worship | 396 | 289,385,219 |
| 41 | Seventh Day Adventist Congregations | Religion – Churches and Other Places of Worship | 422 | 288,044,143 |
| 37 | Presbyterian Congregations | Religion – Churches and Other Places of Worship | 1,040 | 239,944,616 |
| 99 | Miscellaneous Charitable Organizations (not else classified) | Benefits to the Community & Other | 88 | 232,226,197 |
| 42 | Synagogues | Religion – Churches and Other Places of Worship | 331 | 222,854,670 |
| 60 | Islamic Religious Groups | Religion – Churches and Other Places of Worship | 317 | 217,781,872 |
| 32 | Lutheran Congregations | Religion – Churches and Other Places of Worship | 946 | 156,240,241 |
| 15 | (Health) Charitable Trusts | Health | 204 | 151,468,001 |
| 25 | (Education) Charitable Trusts | Education | 351 | 136,406,914 |
| 61 | Jehovah’ Witnesses Congregations | Religion – Churches and Other Places of Worship | 1,604 | 126,836,995 |
| 35 | Buddhist Religious Groups | Religion – Churches and Other Places of Worship | 343 | 121,966,996 |
| 45 | (Religion) Charitable Trusts | Religion – Churches and Other Places of Worship | 212 | 76,698,825 |
| 2 | Disaster Funds | Health | 75 | 64,895,556 |
| 55 | (Community) Charitable Trusts (Other than Service Clubs and Fraternal | Benefits to the Community & Other | 174 | 60,472,760 |
| 62 | Sikh Religious Groups | Religion – Churches and Other Places of Worship | 157 | 47,366,825 |
| 48 | Hindu Religions Groups | Religion – Churches and Other Places of Worship | 186 | 45,027,520 |
| 65 | Service Clubs and Fraternal Societies’ Projects | Benefits to the Community & Other | 491 | 39,307,954 |
| 75 | Employees’ Charity Trusts | Benefits to the Community & Other | 151 | 29,300,383 |
| 63 | Service Clubs and Fraternal Societies’ Charitable Corporations | Benefits to the Community & Other | 187 | 27,594,604 |
| 33 | Baha’ is Religious Groups | Religion – Churches and Other Places of Worship | 236 | 23,028,622 |
| 81 | Registered National Arts Services Organization (RNASO) | Benefits to the Community & Other | 25 | 8,911,123 |
| 51 | Military Units | Benefits to the Community & Other | 63 | 4,245,851 |