(no subject)

[miraje]

I have a question for you Excel whizzes. Let's say I have lots and lots (BIG understatement) of data in a spreadsheet that is formatted like this:

SITE_ID	DATE_TIME	WATER_LEVEL
1234	5/01/2007	31.00
1234	5/02/2007	30.00
1234	5/03/2007	35.00
5555	5/01/2007	25.00
5555	5/02/2007	24.00
5555	5/03/2007	23.00

Is there a way in Excel that I can transpose the data so that it reads like this?

	5/01/2007	5/02/2007	5/03/2007
1234	31.00	30.00	35.00
5555	25.00	24.00	23.00

Comments

[adrinna.livejournal.com]

I had a similar (but simpler) problem with some data a few years ago. I ended up writing all these VBA scripts to reformat the data to my preferred setup. However, even with the scripts it would take awhile to run (LOTS of data) so I finally sucked it up and realized that I needed to learn Matlab to deal with oodles of data.

I could send you my scripts, but my formatting issue was different... I had this type of thing:

01/01 45 01/02 67 01/03 68 ... to end of January
02/01 70 02/02 40 02/03 28 ... to end of February

and I needed to arrange the data all in one dimension like this:

01/01 45
01/02 67
.
.
.
02/28 68

If that makes sense.... my scripts worked, but they were pretty fragile and would take upwards of an hour to run.

Hope someone else has a better answer for you!

[miraje.livejournal.com]

Yeah, well, I requested both Matlab and a statistical software program called JMP (I KNOW this function exists in JMP because I've used it before) for my NSSL PC at least a month ago, and it's still not definite whether I'll even get them. So my only options are Excel and writing some kind of Python script, but I think even the scripting is not an option because the "SITE_ID"s have data for irregular lengths of time. It's not like every well has consistent data from 5/01/07 to 7/01/07. A few only have one record like back in 1995 or something, and others have daily data for five straight years. It's moderately frustrating, and if I had JMP this would be done in 30 seconds. :(

[adrinna.livejournal.com]

Blah, that sucks. I'm going to ask Ian (my partner) about this. He's a fantastic programmer, and has even been able to help me write shell scripts that reformat huge amounts of data lickety-split. He loves a good challenge!

[orewaanpanman.livejournal.com]

I think Excel's pivot tables (http://www.microsoft.com/dynamics/using/excel_pivot_tables_collins.mspx) will do what you want. I don't have Excel installed, but I used the equivalent OpenOffice feature (awkwardly named "Data Pilot") to make this:

[adrinna.livejournal.com]

why hello there Ian :)

[weirdtab.livejournal.com]

Well, the first thing I tried was just putting your sample input in a spreadsheet, highlighting it, copying, right-clicking and choosing "paste special" and in the bottom corner of the window that pops up hitting "transpose" and hitting OK. This probably isn't quite what you want because what is returned is

site_id 1234 1234 1234 5555 5555 5555
date_time 5/1 5/2 5/3 5/1 5/2 5/3
water_level 31 30 35 25 24 23

I didn't format it into a table here on LJ so it probably looks like crap ... so that's an idea if you had really big clumps of places with the same site ID, I would say just select all the stations with one ID and transpose them and then move on to the next. That's the best quick answer I have, though I could probably come up with something else if I think and try long enough. Good luck if this isn't what you need!