My Goodness. What a Fat Dataset!

inkhorn82 — Fri, 26 Oct 2012 01:41:38 +0000

Recently at work we got sent a data file containing information on donations to a specific charitable organization, ranging all the way back to the 80’s. Usually, when we receive a dataset with a donation history in it, each row represents a specific gift from a specific person at a specific time. Also, each column represents some kind of information about that gift. The result is usually a dataset which is fairly long (thousands or hundreds of thousands, in my recent experience) with maybe about 15 columns or more.

In this case, each row represented one person, but there were 1,551 columns!! As it turned out, after the first column, which was the ID of the person donating the money, there were supposed to be just 31 extra columns to describe the gift in each row. However, the person who put the data together decided that we should get 31*50 columns so that each row represented a person, and not a gift, and every subsequent gift from that person was represented by an extra 31 columns to the right of the previous 31. Ridiculous!!

Anyway, I knew that I could reshape this using R, by stacking all 50 copies of the same variable together, and making sure that each new resultant 31 vectors should just take the names of the first 31 vectors. Following is a gist that shows what eventually worked for me:

	# Here's where I extract the database IDs and repeat them 50 times to make the column long enough for
	# my new long-form dataset (596,100 rows)

	client.data.new = rep(client.data[,1],50)

	for (i in 2:32){
	# for each column in the first 31 after the ID column, find the 49 matching columns
	# to the right and stack them using melt

	stacked.data = melt(client.data, id.vars="CnBio_ID", measure.vars=seq(i,(i+(31*49)),31), value.name=names(client.data)[i])

	# Once we have one new stacked column, we add it on to the right of whatever has already been built in the
	# new long-form dataset.

	client.data.new = data.frame(client.data.new, stacked.data[,3])
	}

	# I don't know why the "value.name" argument didn't give me the right variable names for my
	# new long-form dataset, but it was easy enough to use this last line of code to fix that!
	names(client.data.new) = names(client.data)[1:32]

view raw

stack multiple copies of the same data type together.r

hosted with ❤ by GitHub

In conclusion, if you need your dataset to get in shape, you need only remember one letter: R!

Stack – Data Until I Die!

My Goodness. What a Fat Dataset!