Predictions Trading Visualisations

Financial situation of municipalities in North Rhine-Westphalia, Germany

0

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]

gadm_n
gadm_n
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")
You may also like
Quantifying attention to terrorism: Google searches vs. NYT
A quick look at the composition of terrorist events

Leave Your Comment

Your Comment*

Your Name*
Your Webpage