Capital in the Netherlands, 2006-2013

I guess most people have heard of Piketty and his book capital in the twenty-First century. I don't have that book, but the media attention has made me wonder if I could see any trends in Dutch public data. As I progressed with this post, I have concluded that these data could not tell me much about longer term trends, however, one can see very well most people have been getting more poor as the crisis continued.

Data

Data are acquired from Statistics Netherlands. They have Statline (the electronic databank of Statistics Netherlands. It enables users to compile their own tables and graphs. The information can be accessed, printed and downloaded free of charge). There is both a Dutch and an English version of the website. Above I did link to the English part, but I could only find the vermogensklassen table in the Dutch language section. As far as I understand estate is a better word than capital for 'vermogen'. It is intended to reflect the net value of all a persons assets.
Since the contents of the tables are also in Dutch, I will be translating, however, it is not so easy, not everything has an English language equivalent.
It should also be noted that these tables are not the most ideal in the 'Piketty context'. For one thing the time range is to short, for another the detail in the upper capital range is too low.
Finally, there are three sets of variables within the tables; income categories (ten categories, low to high), income source and capital itself. There is one table which crosses income categories and income source, this table provides counts, means and quantiles. The other table has capital categories, but only by either income categories or income source. 
After suitable selection of data ranges I have chosen the .csv versions to export the data. 
As final notes, the data for 2011 has two versions, before and after a method change. When using years in a display, the former got year 2010.9 and the latter 2011.1. The data for 2013 is preliminary.
Regarding income source, I translated as follows:
c('1 Working','1.1 Employees',
        '1.2 Civil Servant','1.3 Other working',
        '2 Own Business','3 Welfare',
        '3.1 Income Insurance','3.1.1 Unemployment',
        '3.1.2 Illness','3.1.3 Retirement',
        '3.2 Social','3.2.1 Sustention','3.2.2 Other Social',
        '3.3 Other welfare')
There is a structure, Working has three sub categories, Welfare has two levels. It should be explained that 3.1 and its sub categories are paid for by employees while working. Unemployment has limited duration, illness is, as far as I understand, a combination of short and long term, the rules have changed a bit, I am not 100% sure. The category 3.2.1 is 'bijstand' which is the last resort for which one can only apply if there are no other sources of income.

Extremes in the data

The presence of both a mean and a 75% quantile gives possibility to finding subsections of data where the mean is larger than the 75% quantile. Below a selection of these data where the quantile is also larger than 100000 and from years 2007 and 2013, ordered by ration mean/75% quantile.
          income            Source  count year   Mean    p50
1         2e 10%    2 Own Business  35000 2013 152000  15000
2         3e 10% 1.3 Other working   4000 2013 203000  11000
3         4e 10% 1.3 Other working   5000 2013 239000  23000
4   1e 10% (low)    2 Own Business  92000 2013 281000  25000
5 10e 10% (high)    2 Own Business 221000 2013 816000 283000
6 10e 10% (high)    2 Own Business 205000 2007 891000 380000
7   1e 10% (low)  3.1.3 Retirement  83000 2013 207000  24000
8         2e 10% 1.3 Other working   4000 2013 257000   6000
9   1e 10% (low) 1.3 Other working  11000 2013 474000   6000
We can observe that these extremes mostly occur in 2013.
There is no data with mean lower than the 25% quantile, however, we can select all data with the first 25% quantile lower than 0. These are mostly working people from the 6th to 8th income category in 2013. I would guess these people have house which lost value while the mortgage did not. It should be noted that mid 2014 house prices did increase again, but since the data reflect information on 1st January, it will be some years ere that is reflected in these tables . On radio today I heard that currently one third of the houses is worth less than the mortgage on it.
   income            Source  count   year   Mean    p25    p50
1  3e 10% 1.2 Civil Servant  22000 2013.0  20000 -13000   2000
2  7e 10%     1.1 Employees 403000 2013.0  55000 -18000   9000
3  7e 10%         1 Working 484000 2013.0  63000 -18000  10000
4  7e 10% 1.2 Civil Servant  67000 2013.0  67000 -19000  18000
5  6e 10%     1.1 Employees 361000 2013.0  46000 -11000   5000
6  8e 10% 1.2 Civil Servant  88000 2013.0  82000 -16000  32000
7  6e 10%         1 Working 425000 2013.0  54000  -9000   6000
8  8e 10%     1.1 Employees 413000 2013.0  76000 -12000  24000
9  8e 10%         1 Working 520000 2013.0  85000 -13000  27000
10 7e 10% 1.2 Civil Servant  64000 2010.9  82000  -3000  30000
11 7e 10% 1.3 Other working  13000 2013.0 289000  -6000  89000
12 8e 10% 1.3 Other working  19000 2013.0 294000  -2000 114000
13 4e 10% 1.3 Other working   5000 2013.0 239000  -1000  23000
From these data at least it seems that 2013 has quite more extremes than 2007, both at the high and the low side.

Plots of trends 

Using these same data it is also possible to make plots. I have chosen only the lowest and highest  income categories, otherwise the sub plots became too small.
In the plot the dots are the means, the lines the medians and the bars the 25% and 75% quantile.

In case anybody wonders what why this welfare category is so rich, that is mostly the retired. I suppose these people have worked hard in their life, got a good retirement fund bought a house of which the mortgage is paid. I have no explanation for the low income business owners who still seem worth on average 250000 Euros, their mean estate is more than the low income business owners of the next income segment. They did not lose value that much either, in contrast to the 9th income group of working people who have lower mean value and lose value.

Plots of estate categories

There are some difficulties in making these displays. The categories have wildly different widths. The lowest and highest categories are unbounded. Finally, the 0 to 5000 Euro category is by far most populous. The net effect is then that we get a figure with high peak, long tails where density is unclear and all detailed cropped up together. Hence in the end I dropped histograms.
The figure below shows trends for income categories while selecting the lowest estate categories. It is clear that especially the number of people with a clear negative estate is growing. People are not entering the small negative category either, debtors in general owe more that 10000 Euro. 

In the highest estate categories again most categories are getting empty. The one exception is the 1 million Euro plus, the number of Euro millionaires increased in 2013. In this latter category somehow the lowest income group relatively often present.

Categories by income source

Again in the lowest category we see a big increase in people with debt. The trend seems to be that the number of working people with debt increases quickly.

For the highest categories we see the corresponding effect. All categories go down, except the richest people. I have chosen to add the retirement category in this plot, hence it is visible that in these categories it is mostly the retired who make up the welfare category

Status in 2013

The final plot shows all estate categories for the three income sources. In this plot we see that for business owners the largest category is the debtors. For the working people the most frequent categories are 0 to 5000 Euro and more than 10 000 Euro debt. 
We can also see a split. Beside the debtors there are the haves, with more than 100 000 Euros and the have a little bit, with less than 30 000 Euro.

Code

Part 1

This is the code used for the mean data based results.
library(ggplot2)
library(dplyr)
r1 <- readLines('Gemiddeld_vermogen___241214145457.csv')
r1[6303]
r1 <- r1[-6303]
c1 <- read.csv(text=r1,skip=1,sep=';',na.strings='.')
uu <- as.character(unique(c1[,1]))
c1$income <- factor(c1[,1],levels=uu)
c1$value <- as.numeric(as.character(c1$Waarde))*1000
c1$period <- factor(c1$Period,levels=levels(c1$Period)[c(1:5,7,6,8,9)])
names(c1)[3] <- 'Source'
levels(c1$Source) <- c('1 Working','1.1 Employees',
        '1.2 Civil Servant','1.3 Other working',
        '2 Own Business','3 Welfare',
        '3.1 Income Insurance','3.1.1 Unemployment',
        '3.1.2 Illness','3.1.3 Retirement',
        '3.2 Social','3.2.1 Sustention','3.2.2 Other Social','3.3 Other welfare')

number <- c1[c1$Onderwerpen_1=='Huishoudens met vermogen',]
number$count <- number$value
number <- subset(number,,c(income,period,Source,count))
amount <- subset(c1,c1$Onderwerpen_1!='Huishoudens met vermogen')
amountw <- reshape(amount,
        v.names=c('value'),
        timevar='Onderwerpen_2',
        varying=list(c('Mean','p25','p50','p75')),
        idvar=c('income','period','Source'),
        direction='wide',
        drop=c('Onderwerpen_1','Inkomensgroepen',
                'Perioden','Waarde.eenheid','Waarde'))

r2 <- merge(number,amountw)
r2$year <- as.numeric(substr(r2$period,1,4))
r2$year[r2$period=='2011 na methodewijziging'] <- 2011.1
r2$year[r2$period=='2011 voor methodewijziging'] <- 2010.9
levels(r2$income) <- gsub('inkomen','',levels(r2$income)) %>%
        gsub('-groep','',.,fixed=TRUE) %>%
        gsub('laag ','low',.) %>%
        gsub('hoog ','high',.) %>%
        gsub(' ()','',.,fixed=TRUE)


filter(r2,!is.na(Mean) , Mean>p75,year %in% c(2007,2013),p75>100000) %>%
        arrange(.,Mean/p75) %>%
        select(.,income, Source,count,year,Mean,p50)


filter(r2,!is.na(Mean) , Mean<p25) %>%
        arrange(.,-Mean/p25) %>%
        select(.,income, Source,count,year,Mean,p50)
filter(r2,!is.na(Mean) , p25<0) %>%
        arrange(.,-Mean/p25) %>%
        select(.,income, Source,count,year,Mean,p25,p50)
       
# first figure
g1 <- ggplot(r2[as.numeric(r2$Source) %in%
                        c(1,5,6) &
                        as.numeric(r2$income) %in% c(1,2,9,10),],
        aes(x=year,
                y=p50,
                ymin=p25,
                ymax=p75,
                col=Source))
g1 + geom_errorbar(position = "dodge") +
        geom_line()+
        facet_wrap(~ income,nrow=2) +
        guides(col=guide_legend(ncol=3)) +
        theme(legend.position='bottom',
                legend.direction ='vertical',
                text=element_text(size=10,
                )
        ) +
        geom_point(aes(x=year,y=Mean,Col=Source)) +ylab('Euro')

Part 2

This is the code using the estate categories. It starts with a load of data reorganisation. This is because the data sits in columns for income categories, income sources and both of these having amounts and counts. It starts with a section for income categories (low to high) after which a similar section for income sources is used.
library(ggplot2)
library(dplyr)
cvk <- readLines('Vermogensklassen__hu_241214141933.csv')
cvk.head <- as.matrix(read.csv2(text=cvk[2:5],header=FALSE))
cvk.body <- read.csv2(text=cvk[5:(length(cvk)-1)])
keepcol <- c(colnames(cvk.head)[cvk.head[3,]=='Aantal' &
                        grepl('groep',cvk.head[1,])])

colnames(cvk.body)[c(1,2)] <- c(cvk.head[4,1:2])
mytimes <- cvk.head[1,keepcol]
cvk.body <- cvk.body[,c(1,2,which(cvk.head[3,]=='Aantal' &
                                grepl('groep',cvk.head[1,])))]
#head(cvk.body)
cvk <- reshape(cvk.body,
        varying=list(names(cvk.body)[-2:-1]),
        v.names='Count',
        timevar='income',
        idvar=c('Vermogensklassen','Perioden'),
        direction='long',
        times=mytimes
)
rownames(cvk) <- 1:nrow(cvk)
cvk$income <- factor(cvk$income,levels=unique(cvk$income))
levels(cvk$income) <- gsub('inkomen','',levels(cvk$income)) %>%
        gsub('-groep','',.,fixed=TRUE) %>%
        gsub('laag ','low',.) %>%
        gsub('hoog ','high',.) %>%
        gsub(' ()','',.,fixed=TRUE)       
head(cvk)

cvk$Period <- factor(cvk$Perioden,levels=levels(cvk$Perioden)[c(1:5,7,6,8,9)])
cvk$year <- as.numeric(substr(cvk$Period,1,4))
cvk$year[cvk$Period=='2011 na methodewijziging'] <- 2011.1
cvk$year[cvk$Period=='2011 voor methodewijziging'] <- 2010.9

#linux
cvk$EstateCat <- factor(cvk$Vermogensklassen,
        levels(cvk$Vermogensklassen)[c(1,14,4,2,3,5:13,16:24,15)])
#win
#cvk$EstateCat <- factor(cvk$Vermogensklassen,
#    levels(cvk$Vermogensklassen)[c(3,1,2,4:14,16:24,15)])

levels(cvk$EstateCat) <- gsub('Vermogen','',levels(cvk$EstateCat)) %>%
#gsub('000','',.) %>%
gsub('tot','to',.) %>%
gsub('miljoen','million',.) %>%
gsub('en meer','plus',.) %>%
gsub('[[:space:]]+',' ',.)

#xtabs(~ EstateCat + Vermogensklassen,data=cvk)
############
# plotting phase of income categories
############
# low
p1 <- ggplot(cvk[as.numeric(cvk$EstateCat)<7,],aes(y=Count,x=year,col=income))
p1+geom_line()+
        facet_wrap(~ EstateCat) +
        guides(col=guide_legend(ncol=5)) +
        theme(legend.position='bottom',
                legend.direction ='vertical',
                text=element_text(size=10,
                )) +
        ylab('Count (*1000) ')
dev.off()
# high
p1 <- ggplot(cvk[as.numeric(cvk$EstateCat)>18,],aes(y=Count,x=year,col=income))
p1+geom_line()+
        facet_wrap(~ EstateCat) +
        guides(col=guide_legend(ncol=5)) +
        theme(legend.position='bottom',
                legend.direction ='vertical',
                text=element_text(size=10,
                )) +
        ylab('Count (*1000) ')

################
# income sources
################
#cvk.head <- as.matrix(read.csv2(text=cvk[2:5],header=FALSE))
cvk <- readLines('Vermogensklassen__hu_241214141933.csv')
cvk.body <- read.csv2(text=cvk[5:(length(cvk)-1)])
keepcol <- c(colnames(cvk.head)[cvk.head[3,]=='Aantal' &
                        !grepl('groep',cvk.head[1,])])
#keepcol
colnames(cvk.body)[c(1,2)] <- c(cvk.head[4,1:2])
mytimes <- cvk.head[1,keepcol]
cvk.body <- cvk.body[,c(1,2,which(cvk.head[3,]=='Aantal' &
                                !grepl('groep',cvk.head[1,])))]
#head(cvk.body)
cvit <- reshape(cvk.body,
        varying=list(names(cvk.body)[-2:-1]),
        v.names='Count',
        timevar='Source',
        idvar=c('Vermogensklassen','Perioden'),
        direction='long',
        times=mytimes
)
rownames(cvit) <- 1:nrow(cvit)
#head(cvit)
#unique(cvit$Source)
cvit$Source <- factor(cvit$Source)
levels(cvit$Source) <- c('1 Working','1.1 Employees',
        '1.2 Civil Servant','1.3 Other working',
        '2 Own Business','3 Welfare',
        '3.1 Income Insurance','3.1.1 Unemployment',
        '3.1.2 Illness','3.1.3 Retirement',
        '3.2 Social','3.2.1 Sustention','3.2.2 Other Social','3.3 Other welfare')
#levels(cvit$Perioden)
cvit$Period <- factor(cvit$Perioden,levels=levels(cvit$Perioden)[c(1:5,7,6,8,9)])
cvit$year <- as.numeric(substr(cvit$Period,1,4))
cvit$year[cvit$Period=='2011 na methodewijziging'] <- 2011.1
cvit$year[cvit$Period=='2011 voor methodewijziging'] <- 2010.9

levels(cvit$Vermogensklassen)
cvit$EstateCat <- factor(cvit$Vermogensklassen,
        levels(cvit$Vermogensklassen)[c(1,14,4,2,3,5:13,16:24,15)])
#levels(cvit$EstateCat)
#win
#cvit$EstateCat <- factor(cvit$Vermogensklassen,
#        levels(cvit$Vermogensklassen)[c(3,1,2,4:14,16:24,15)])

levels(cvit$EstateCat) <- gsub('Vermogen','',levels(cvit$EstateCat)) %>%
#gsub('000','',.) %>%
        gsub('tot','to',.) %>%
        gsub('miljoen','million',.) %>%
        gsub('en meer','plus',.) %>%
        gsub('[[:space:]]+',' ',.)

# figure 5
p1 <- ggplot(cvit[as.numeric(cvit$Source) %in% c(1,5,6) &
                        as.numeric(cvit$EstateCat) <7 ,],
        aes(y=Count,x=year,col=Source))
p1+geom_line()+
        facet_wrap(~ EstateCat) +
        guides(col=guide_legend(ncol=5)) +
        theme(legend.position='bottom',
                legend.direction ='vertical',
                text=element_text(size=10,
                )) +
        ylab('Count (*1000) ')

# figure 6
p1 <- ggplot(cvit[as.numeric(cvit$Source) %in% c(1,5,6,10) &
                        as.numeric(cvit$EstateCat) >18 ,],
        aes(y=Count,x=year,col=Source))
p1+geom_line()+
        facet_wrap(~ EstateCat) +
        guides(col=guide_legend(ncol=5)) +
        theme(legend.position='bottom',
                legend.direction ='vertical',
                text=element_text(size=10,
                )) +
        ylab('Count (*1000) ')

# figure 2013
p1 <- ggplot(cvit[as.numeric(cvit$Source) %in% c(1,5, 6) & cvit$year==2013 ,],
        aes(y=Count,x=EstateCat))
p1+geom_point()+
        facet_wrap(~ Source) +
        guides(col=guide_legend(ncol=4)) +
        theme(legend.position='bottom',
                legend.direction ='vertical',
                text=element_text(size=10,
                ))+
        ylab('Count (*1000) ') +
        coord_flip()