md`# NetPolitiek Masterclass 2019 ## Financing political parties in The Netherlands What kind of data is available? https://kennisopenbaarbestuur.nl/thema/financiering-politieke-partijen/ bron? download? https://www.rijksoverheid.nl/onderwerpen/democratie/documenten/publicaties/2017/10/03/financiele-verslagen-over-2016-van-politieke-partijen PDF? => for governement publications starting in 2016, we can use Who (Wet hergebruik overheidsinformatie) to get the source data format data.overheid.nl? https://data.overheid.nl/dataset/giften-en-schulden-politieke-partijen Click some more. https://www.rijksoverheid.nl/onderwerpen/democratie/documenten/publicaties/2017/10/03/overzichten-van-bijdragen-aan-politieke-partijen-en-hun-neveninstellingen-2016 Spreadsheet-ish! Click even more. https://www.rijksoverheid.nl/onderwerpen/democratie/documenten?trefwoord=overzichten-van-bijdragen-aan-politieke-partijen-en-hun-neveninstellingen&periode-van=&periode-tot=&onderdeel=Alle+ministeries&type=Alle+documenten. Data is available since 2013 in PDF. ## Can we visualize the data? ![NYT screenshot](https://i.imgur.com/jKd9t3L.png) Source New York Times ![screenshot 538](https://i.imgur.com/2EmSRkh.png "") Source fivethirtyeight ## Process the data See my starting point spreadsheet. Choose File->Make a copy to your own Google drive or claim one of the public editable sheets (no guarantees here!) and fill in your name in the sheet name to claim it (please respect filled in sheets!). Claim: sheet 1, sheet 2, sheet 3, sheet 4, sheet 5, sheet 6, sheet 7, sheet 8. Now you need to complete a few basic steps: 1. Convert the city names to municipalities (e.g. 'Hoofddorp => Haarlemmermeer') 2. Cleanup the data for unmapped cities 3. Sum up the amounts per city and party combination 4. Paste the data in the proper CSV format below 5. Optional: change the code! Tips: 1. VLOOKUP See Google documentation VLOOKUP 2. For some common cases, you can use replace or add a record in the mapping 3. See pivot tables Google documentation Pivot tables 4. Remove both € and , from the pivot table calculated output (Format->Number->More Formats->Custom Number Format), File->Download->Comma Seperated values (.csv current sheet), open in text editor (e.g. notepad) 5. Can you use different color scales per party? Can you combine party data like in the New York Times example? `