The financial situation of municipalities is often dire. North Rhine-Westphalia, Germany’s biggest state, is generally thought to be in the worst financial situation. High per capita debt is often accompanied by a high share of short-term debt which strongly exposes the municipalitiy to interest rate risk.
Using data from 382 municipalities, I will start with a graphical explanatory analysis.
The data can be obtained from here. For creating the map of per capita debt, I borrowed heavily form Mark Heckmann.
The R code can be seen after the plots. It still needs some cleaning up. Let me know if you have any questions or if you have suggestions for further analysis.
require(ggplot2)
require(ggmap)
require(mapproj)
library(sp)
library(RColorBrewer)
library(scales)
setwd("/Users/christophpfeiffer/Downloads/Kommunalfinanzen")
Ergebnis09<-read.table("Ergebnis2009.txt",sep=";",encoding='latin1')
Ergebnis10<-read.table("Ergebnis2010.txt",sep=";",encoding='latin1')
Bilanz<-read.table("Bilanz.txt",sep=";",encoding='latin1')
names(Ergebnis09)<-c("Gemeindeschl","Verwaltungsform","Name","Produktgruppe","Text.Produktgruppe","Konto","Konto.Text","Wert")
names(Ergebnis10)<-c("Gemeindeschl","Verwaltungsform","Name","Produktgruppe","Text.Produktgruppe","Konto","Konto.Text","Wert")
names(Bilanz)<-c("Amtlicher.GemS","Verwaltungsform","Name","Signatur","Signatur.text","Wert")
Ergebnis09[,9]<-NULL
Ergebnis10[,9]<-NULL
Bilanz[,7]<-NULL
names(Ergebnis09)==names(Ergebnis10)
#Join
Ergebnis09$Jahr<-2009
Ergebnis10$Jahr<-2010
Ergebnis<-rbind(Ergebnis09,Ergebnis10)
#Name as character / numeric
Ergebnis$Name<-as.character(Ergebnis$Name)
Ergebnis$Text.Produktgruppe<-as.character(Ergebnis$Text.Produktgruppe)
Ergebnis$Wert<-as.numeric(Ergebnis$Wert)
Ergebnis$Konto.Text<-as.character(Ergebnis$Konto.Text)
Bilanz$Name<-as.character(Bilanz$Name)
Bilanz$Signatur.text<-as.character(Bilanz$Signatur.text)
#Cut last empty space in character name
Ergebnis$Name<-substr(Ergebnis$Name,start=1,stop=nchar(Ergebnis$Name)-1)
Ergebnis$Text.Produktgruppe<-substr(Ergebnis$Text.Produktgruppe,start=1,stop=nchar(Ergebnis$Text.Produktgruppe)-1)
Ergebnis$Konto.Text<-substr(Ergebnis$Konto.Text,start=1,stop=nchar(Ergebnis$Konto.Text)-1)
Bilanz$Name<-substr(Bilanz$Name,start=1,stop=nchar(Bilanz$Name)-1)
Bilanz$Signatur.text<-substr(Bilanz$Signatur.text,start=1,stop=nchar(Bilanz$Signatur.text)-1)
#Look at variables
names(Ergebnis)
unique(Ergebnis$Name)
unique(Ergebnis$Verwaltungsform)
unique(Ergebnis$Text.Produktgruppe)
unique(Ergebnis$Konto.Text)
names(Bilanz)
unique(Bilanz$Verwaltungsform)
unique(Bilanz$Name)->name
unique(Bilanz$Signatur.text)
a<-list()
b<-which(Bilanz$Signatur.text=="Verbindlichkeiten mit einer Restlaufzeit von bis zu 1 Jahr")
c<-which(Bilanz$Signatur.text=="Gesamtbetrag der Verbindlichkeiten am 31.12. des Haushaltsjahres")
for(i in 1:length(name)){
a[[i]]<-which(Bilanz$Name==name[i])
}
Schulden.r<-matrix(nrow=length(name),ncol=2)
for(i in 1:length(name)){
if(
length(which(is.element(b,a[[i]])))>0 &
length(which(is.element(c,a[[i]])))>0)
{
Schulden.r[i,1]<-Bilanz[which(Bilanz$Name==name[i] & Bilanz$Signatur.text=="Verbindlichkeiten mit einer Restlaufzeit von bis zu 1 Jahr"),]$Wert
Schulden.r[i,2]<- Bilanz[which(Bilanz$Name==name[i] & Bilanz$Signatur.text=="Gesamtbetrag der Verbindlichkeiten am 31.12. des Haushaltsjahres"),]$Wert
}
else
Schulden.r[i,]<-NA
#Schulden.r[i]<-0
}
as.data.frame(Schulden.r)->Schulden.r
Schulden.r$Name<-name
names(Schulden.r)<-c("Verbindlichk.k","Verbindlichk.i","Name")
Schulden.r$Schulden.r<-Schulden.r$Verbindlichk.k/Schulden.r$Verbindlichk.i
Einwohnerzahl hinzufügen
Einw<-read.csv("Einwohner_Deutschland_Kreise.csv",header=TRUE,sep=";",encoding='latin1',colClasses=c("character","character","numeric","character","numeric","numeric","numeric"))
Einw$X<-NULL
names(Einw)[1]<-"Bundesland"
Einw.NRW<-Einw[Einw$Bundesland=="Nordrhein-Westfalen",]
Einw.NRW$Name <- gsub("Städte", "", Einw.NRW$Name)
order1<-c()
for(i in 1:nrow(Schulden.r)){
order1[i]<-pmatch(Schulden.r$Name[i],Einw.NRW$Name)
}
for(i in which(is.na(order1))){
order1[i]<-agrep(Schulden.r$Name[i],Einw.NRW$Name,max.distance=0.7)[1]
}
which(is.na(order1))
Schulden.r$Einwohner<-Einw.NRW$Insgesamt[order1]
names(Schulden.r)
Schulden.r$Schulden.pro.Einw<-Schulden.r$Verbindlichk.i/Schulden.r$Einwohner
Plot worst Schulden pro Einw
qplot(
x=reorder(Name,Schulden.pro.Einw),
y=Schulden.pro.Einw,
data=top.x, col=I("blue")
)+coord_flip()+theme_minimal()
P1<-qplot(x=reorder(Name,Schulden.r),y=Schulden.r, data=top.x, col=Einwohner,size=Schulden.pro.Einw,alpha=I(0.7))
P1<-P1+theme_minimal()+ theme(legend.position="none")+opts(axis.text.x=theme_text(angle=0))+coord_flip()
P1+ylab("Stadt")+xlab("Verhältnis kurzfristige Schulden zu Schulden insg.")
##4 Feld Graphik
head(Schulden.r[order(Schulden.r$Schulden.pro.Einw,decreasing=T),],22)->top.x
which(is.na(top.x))
P2<-qplot(x=Schulden.r,y=Schulden.pro.Einw, data=top.x,
label=Name, col=Schulden.pro.Einw,size=Einwohner,
alpha=I(0.5),xlim=c(0.2,0.9),ylim=c(3000,8000))
P2<-P2+theme_minimal()+ theme(legend.position="none")
P2<-P2+scale_area(range=c(5,30),guide="none")+geom_text(size=5,hjust=0.3,vjust=0.3,color="black")
P2+ylab("Verbindlichenkeiten pro Einwohner")+xlab("Fremdkapitalstruktur")+geom_smooth(method="lm",se=F,col=I("black"))+
labs(title="Finanzsituation der 22 Kommunen in NRW mit der höchsten pro Kopf Verschuldung")+
scale_colour_gradient2(low="blue", mid="blue",high="red",guide="none")
qplot(y=Schulden.pro.Einw,x=Schulden.r,data=Schulden.r[-which(Schulden.r$Schulden.r<0),],
geom="point",alpha=I(0.5),size=Einwohner)+theme_minimal()+geom_smooth()+ theme(legend.position="none")
Visualisieurng
p1<-qplot(Schulden.r,
data=Schulden.r[-Schulden.r$Schulden.r<0|is.na(Schulden.r$Schulden.r),],
geom="density",fill=I("blue"),alpha=I(0.7),color=I("white"))
p1+theme_minimal()+labs(title="Verhältnis kurzfristige zu langfriste Schulden in NRW")+xlab("Schuldenratio")
con <- url("http://gadm.org/data/rda/DEU_adm3.RData")
print(load(con))
close(con)
# plot Germany with random colors
col = rainbow(length(levels(gadm$NAME_3)))
spplot(gadm, "NAME_3", col.regions=col, main="German Regions",
colorkey = FALSE, lwd=.4, col="white")
#Only NRW
gadm.nrw<-gadm[gadm$NAME_1=="Nordrhein-Westfalen",]
spplot(gadm.nrw, "NAME_3", col.regions=col, main="German Regions NRW",
colorkey = FALSE, lwd=.4, col="white")
gadm_names <- iconv(gadm.nrw$NAME_3, "ISO_8859-2", "UTF-8")
#Match kurzfristiger Verschuldungsratio
Schulden.r[-c(376:382),]->Schulden.r
gadm_names_n <- gsub("Städte", "", gadm_names)
Schulden.r$Name <- gsub("Kreis", "", Schulden.r$Name)
Schulden.r$Name <- gsub("Kreisverwaltung", "", Schulden.r$Name)
gadm_names_n[1]<-"Köln"
order<-c()
for(i in 1:length(gadm_names_n)){
order[i]<-pmatch(gadm_names_n[i], Schulden.r$Name)
}
for(i in which(is.na(order))){
order[i]<-agrep(gadm_names_n[i], Schulden.r$Name,max.distance=0.3)[1]
}
which(is.na(order))
as.data.frame(gadm_names_n)->gadm_n
gadm_n$Schulden.pro.Einwohner<-Schulden.r$Schulden.pro.Einw[order]
col_no <- as.factor(as.numeric(cut(gadm_n$Schulden.pro.Einwohner,c(0,500,1000,2000,3000,4000,5000,6000,8000))))
levels(col_no) <- c("<500EUR","500-1000EUR","1000-2000EUR","2000-3000EUR","3000-4000EUR","4000-5000EUR","5000-6000EUR",">6000EUR")
gadm_n[which(is.na(col_no)),]
gadm.nrw$col_no <- col_no
myPalette<-brewer.pal(length(levels(col_no)),"Reds")
spplot(gadm.nrw, "col_no", col=grey(.9), col.regions=myPalette,main="Pro Kopf Verschuldung NRW")