<jupyter_start><jupyter_text>**Municipality Budget Planning**In this project we seek to analyze the surplus per capita in budgeting for the Danish municipalities. By use of data from Statistics Denmark we seek to find any pattern between the budgeted and actual net expenditure for the municipalities. We further examine if population size, unemployment rate and Employment rate has any influence on the municipalities estimates for expenditure. To analyze our problem, we make use of several python packages, which we import in the code below. If any of the packages aren't loading properly, please consult the read.md file for installation instructions.<jupyter_code>#needed for matplotlib to function in notebook
%matplotlib inline

#import packages
import numpy as np
import pandas as pd
import pydst
import matplotlib.pyplot as plt
import scipy.stats
from pandas_datareader import wb
import ipywidgets as  widgets


#import class from local file
from NokiaSnakeClient import graphwindow  
from dataproject.plotter import PlotterWindow

#setting default language for DST api
Dst = pydst.Dst(lang='en')<jupyter_output><empty_output><jupyter_text>**Data sorting**In order to examine our topic, we retrieve data from the DST APi. We get data from the tables REGK11, BUDK1 and INDAMP01.we use REGK11 to get accounting data for all municipalities in Denmark. The data is registered at the end of the year. REGK11 covers the period 2011-2018.BUDK1 is used to get budget data for all municipalities in Denmark. The data is registered at the beginning of the year and covers the period 2009-2019.We use INDAMP01 to get population data to examine our problem. the population is registered 1st of January and the table covers the period 2006-2017.AULP01 contains unemployment rate for all Municipalities.The csv file Employment.csv is data for employed workers in a municipality.   When fetching data from the DST API we restrict it to only include current prices per capita, total main account and current expenditure for BUDK1 and REGK11, while the data from INDAMP01 and AULP01 is restricted to include all people across gender, age, etc.In order to follow the data sorting and cleaning throughout the paper, we'll include account.head() to demonstrate the changes on the table<jupyter_code>#fetching data from DST api with pydst package
account = Dst.get_data(table_id = "REGK11", variables = {"OMRÅDE": ["*"], "FUNK1": ["X"], "DRANST": ["1"], "ART": ["TOT"], "PRISENHED": ["INDL"], "Tid": ["*"]})
budget = Dst.get_data(table_id = "BUDK1", variables = {"REGI07A": ["*"], "FUNK1": ["X"], "DRANST": ["1"], "ART": ["TOT"], "PRISENHED": ["INDL"], "Tid": ["*"]})
population = Dst.get_data(table_id = "INDAMP01", variables = {"OMRÅDE": ["*"], "KØN": ["TOT"], "ALDER": ["TOT"], "PERSG": ["IALT"], "Tid": ["*"], "BNØGLE": ["PER"]})
unemployment = Dst.get_data(table_id = "AULP01", variables = {"OMRÅDE": ["*"], "KØN": ["TOT"], "ALDER": ["TOT"],"Tid": ["*"],})
employment = pd.read_csv("./Employment.csv")
population.head()<jupyter_output><empty_output><jupyter_text>We have several columns in our data which have no use in our analysis. They are therefore dropped. Further, we rename the columns to get more appropriate names<jupyter_code>#drop unneeded columns
account.drop(["FUNK1", "DRANST", "ART", "PRISENHED"], axis=1, inplace=True)
budget.drop(["FUNK1", "DRANST", "ART", "PRISENHED"], axis=1, inplace=True)
population.drop(["KØN", "ALDER", "PERSG", "BNØGLE"], axis=1, inplace=True)
unemployment.drop(["KØN", "ALDER"], axis=1, inplace=True)
employment.drop(["Unnamed: 0"], axis=1, inplace=True)

#renaming columns
budget.rename(columns = {"REGI07A":"Municipality", "TID": "Year", "INDHOLD":"Budget"}, inplace = True)
account.rename(columns = {"OMRÅDE":"Municipality", "TID": "Year", "INDHOLD":"Account"}, inplace = True)
population.rename(columns = {"OMRÅDE":"Municipality", "TID": "Year", "INDHOLD":"POP"}, inplace = True)
unemployment.rename(columns = {"OMRÅDE":"Municipality", "TID": "Year", "INDHOLD":"Unemployment rate"}, inplace = True)
employment.rename(columns = {"Område":"Municipality","Tid": "Year" ,"Indhold":"Employment"}, inplace = True) 
population.head()<jupyter_output><empty_output><jupyter_text>Our three data frames do not share the same dimensions and cannot be merged. We have to further clean the data frames before we can make our analysis.<jupyter_code>account.info()
population.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 832 entries, 0 to 831
Data columns (total 3 columns):
Municipality    832 non-null object
Year            832 non-null int64
Account         832 non-null int64
dtypes: int64(2), object(1)
memory usage: 19.6+ KB
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1380 entries, 0 to 1379
Data columns (total 3 columns):
Municipality    1380 non-null object
Year            1380 non-null int64
POP             1380 non-null int64
dtypes: int64(2), object(1)
memory usage: 32.4+ KB<jupyter_text>In order to make our data frames comparable, we remove all entries for non-municipalities. We further restrict the time periods for our data to the period 2011-2017.<jupyter_code>#dropping rows for region, province and all Denmark
for val in ["Region", "Province", "All Denmark","Landsdel","Hele landet"]:
    for dataset in [population, budget, account,unemployment,employment]:
        rowstodrop = dataset.loc[dataset["Municipality"].str.contains(val)].index
        dataset.drop(rowstodrop, axis=0, inplace=True)

#create list with time period
a = []
for i in range(2011,2018):
    a.append(str(i))

#limit data to time period a
budget = budget.loc[budget["Year"].isin(a)]
population = population.loc[population["Year"].isin(a)]
account = account.loc[account["Year"].isin(a)]
unemployment = unemployment.loc[unemployment["Year"].isin(a)]
employment = employment.loc[employment["Year"].isin(a)]
employment.info()
population.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 686 entries, 33 to 1149
Data columns (total 3 columns):
Municipality    686 non-null object
Year            686 non-null int64
Employment      686 non-null int64
dtypes: int64(2), object(1)
memory usage: 21.4+ KB
<class 'pandas.core.frame.DataFrame'>
Int64Index: 686 entries, 0 to 1378
Data columns (total 3 columns):
Municipality    686 non-null object
Year            686 non-null int64
POP             686 non-null int64
dtypes: int64(2), object(1)
memory usage: 21.4+ KB<jupyter_text>As seen from the .info() our data frames have the same dimensions and we are able to merge them. We do an outer merge in four stages. First, we merge people and account as the partialmerge data frame. We then merge firstmerge with unemployment, and so forth until supermerged which contains all data in one data frame.<jupyter_code>#merge datasets
firstmerge = pd.merge(population, account, how="outer", on=["Municipality", "Year"])
partialmerge = pd.merge(firstmerge, unemployment, how="outer", on=["Municipality", "Year"])
fullymerged = pd.merge(partialmerge, budget, how="outer", on=["Municipality", "Year"])
supermerged = pd.merge(fullymerged, employment, how="outer", on=["Municipality","Year"])
supermerged.head()<jupyter_output><empty_output><jupyter_text>We want to compute the surplus, which we define as the difference between the budgeted and actual net expenses. We further add a column with an employment ratio which is defined as employment/population.We add the column named surplus and Emp/POP to our data frame supermerged.in the .head() we chose to look at Aalborg municipality to verify the accuracy of the computation.<jupyter_code>#creating new columns
supermerged["Surplus"] = supermerged["Budget"]-supermerged["Account"]
supermerged["Emp/POP" ]=supermerged["Employment"]/supermerged["POP"]*100
supermerged[supermerged["Municipality"]=="Copenhagen"].head()<jupyter_output><empty_output><jupyter_text>**Data Analysis** To start our analysis, we look at the distribution of the surplus per capita. We compare the surplus distribution with the gaussian distribution to get further insights into the data.<jupyter_code>#define function to draw graph type
def surplusdistribution(data, title, printname):
    #calculating stats for the data set
    mean = np.mean(data["Surplus"])
    variance = np.var(data["Surplus"])
    sigma = np.sqrt(variance)
    normal = np.linspace(min(data["Surplus"]), max(data["Surplus"]), 100)
    
    #creating a figure and adding subplots
    fig = plt.figure(dpi=100)
    ax = fig.add_subplot(1,1,1)
    ax.hist(data["Surplus"], density=True, bins = 100, alpha = 0.5, label = "Surplus")
    ax.plot(normal, scipy.stats.norm.pdf(normal, mean, sigma), label = "Normal")
    ax.set_xlabel("Surplus")
    ax.set_title(title)
    ax.legend()
    
    print("Mean for " + printname + ": " + str(mean))
    print("Std. dev. for " + printname + ": " + str(sigma))

#calling function 
surplusdistribution(supermerged,"Surplus distribution", "Surplus")<jupyter_output>Mean for Surplus: 1416.631195335277
Std. dev. for Surplus: 1626.4433776729281<jupyter_text>From the stats and the graph, we see a clear tendency for municipalities to overestimate their expenditure for the year. The distribution has a mean of 1416 dkk per capita and seems to be skewed to the right, which further indicates the tendency to overestimate expenditure. We further want to examine a possible correlation in municipalities' surplus and the population of the municipality.<jupyter_code>#create scatter plot function
def scatterplot(data, x, y,title=""):
    plt.scatter(data[x], data[y])
    plt.xlabel(x)
    plt.ylabel(y)
    plt.title(title)
    plt.show()

#calling scatterplot function for our data
scatterplot(supermerged, "Surplus", "POP")<jupyter_output><empty_output><jupyter_text>The scatter plot shows no clear correlation between population and surplus per capita in Danish municipalities. The mean for municipalities with a population above 150000 seems to be higher, while the standard deviation seems to be lower. To further examine this, we will split the data in a high population and low population dataset.  High vs. low populationThe dataset supermerged is split into a high and low population dataset. We define any municipality with a population over 150000 people as a high population municipality. We create a surplus distribution for both datasets.<jupyter_code>#splitting dataset in high and low POP
above150k = supermerged.loc[supermerged["POP"]>150000]
below150k = supermerged.loc[supermerged["POP"]<=150000]
above150k.name = "POP > 150k"
below150k.name = "POP < 150k"

#creating graphs with graph function 
for data in [above150k, below150k]:
    surplusdistribution(data,data.name,data.name)<jupyter_output>Mean for POP > 150k: 1838.8214285714287
Std. dev. for POP > 150k: 1515.0229855298146
Mean for POP < 150k: 1398.6656534954407
Std. dev. for POP < 150k: 1628.589892252984<jupyter_text>As suspected from the scatter plot, high population municipalities have a higher mean and lower standard deviation compared to low population municipalities. The sample for POP>150k is rather low, and no apparent distribution takes form in the figure. The distribution for low population municipalities is nearly identical to the combined surplus per capita. The mean is a bit lower while the standard deviation is a bit higher.It seems municipalities with a low population on average is better at predicting their net expenditure per capita than their high population counterpart. The higher mean for high population municipalities indicates a higher inefficiency in adjusting their expectations to expenditure.we further create to scatterplots to examine for any further correlation between population and surplus per capita.<jupyter_code>#creating graphs with graph function 
for data in [above150k, below150k]:
    scatterplot(data, "Surplus", "POP")<jupyter_output><empty_output><jupyter_text>We find no correlation between population and surplus per capita in the two datasets. The municipalities are randomly spread with a tendency for surplus as explained in the distribution figures.  Development in municipalitiesBy use of the PlotterWindow class from Plotter the application we are able to examine the development of population and surplus in the respective municipalities over the period 2011-2017. The class opens a separate graph window when run.<jupyter_code>app = PlotterWindow(data = supermerged, slicename = "Municipality", graphtype = "Standard", 
                    xvariable = "Year", yvariablelist=["Surplus", "POP"])
app.start()<jupyter_output><empty_output><jupyter_text>No consistent pattern can be found between population growth and surplus per capita across municipalities. municipality of Copenhagen have a somewhat consistent surplus while having population growth, whereas Aalborg have larger deviations in surplus while also having a population growth.<jupyter_code>app = PlotterWindow(data = supermerged, slicename = "Municipality", graphtype = "Standard", 
                    xvariable = "Year", yvariablelist=["Surplus", "Emp/POP"])
app.start()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10177903336641979, 'fraction_numerical': 0.031152172253187754, 'mean_word_length': 5.490584737363727, 'pattern_counts': {'":': 39, '<': 57, '<?xml version=': 0, '>': 58, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '593520', 'license': 'MIT', 'n_tokens_mistral': 4169, 'n_tokens_neox': 3725, 'n_words': 1461, 'path': 'dataproject/dataProject.ipynb', 'repo_name': 'NumEconCopenhagen/projects-2019-nokiasnake', 'size': '13097'}