pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHairy question - transpose columns
4+ messages / 4 participants
[nested] [flat]
* Hairy question - transpose columns
@ 2002-10-23 17:56 andres javier garcia garcia <andresjavier.garcia@wanadoo.es>
0 siblings, 2 replies; 4+ messages in thread
From: andres javier garcia garcia @ 2002-10-23 17:56 UTC (permalink / raw)
To: pgsql-sql
Hello;
I've got pluviometric data in a bizarre format (spanish administration is rather original) and I need to "transpose" them, to be able to use them as time series data for a model.
The original data are in a format like
cod_var | Year | Month | Ten | RainDay1 | RainDay2 | RainDay3 | Rainday4 | Rainday5 | RainDay6 | RainDay7 | RainDay8 | Rainday9 | Rainday10 | Rainday11
--------------+-----------+------------+---------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+
452 | 1995 | 1 | 1 | 2 | 5 | 6 | -3 | 0 | 5 | 4 | 5 | 4 | 4 |
452 | 1995 | 3 | 2 | 4 | 5 | 0 | 5 | 3 | 23 | 4 | 34 | 4 | 2 |
452 | 1996 | 12 | 3 | 12 | 2 | 3 | 4 | 7 | 3 | 3 | 15 | 2 | 4 | 3
452 | 1998 | 9 | 2 | 2 | 8 | 6 | -3 | 5 | 0 | 2 | 6 | 0 | 1 |
452 | 1998 | 3 | 3 | 2 | -3 | 7 | 9 | 4 | 2 | 5 | 6 | 1 | 16 | 3
......................
As you may see, the date of a rain datum is defined by the value of the fields Year, Month,Ten (1=first ten days of month; 2=second ten days of month; 3=up to eleven last days of month).
and the field in which the datum is (RainDay1, RainDay2...)
This is no useful for me because I need something like:
cod_var | Year | Month | Day | Rain |
--------------+-----------+------------+---------+----------------+--
452 | 1995 | 1 | 1 | 2 |
452 | 1995 | 1 | 2 | 5 |
452 | 1995 | 1 | 3 | 6 |
452 | 1995 | 1 | 4 | -3 |
452 | 1995 | 1 | 5 | 0 |
452 | 1995 | 1 | 6 | 5 |
...................
Perhaps this is not possible to do with a database? Should I manage to make a program to fix this?
Thanks for your help. I really can't imagine how to do this with Postresql; though I'm a newbye.
Regards
-----------
Javier
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Hairy question - transpose columns
@ 2002-10-23 18:06 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: andres javier garcia garcia <andresjavier.garcia@wanadoo.es>
1 sibling, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2002-10-23 18:06 UTC (permalink / raw)
To: andres javier garcia garcia <andresjavier.garcia@wanadoo.es>; +Cc: pgsql-sql
On Wed, 23 Oct 2002, andres javier garcia garcia wrote:
> Hello; I've got pluviometric data in a bizarre format (spanish
> administration is rather original) and I need to "transpose" them, to
> be able to use them as time series data for a model.
> As you may see, the date of a rain datum is defined by the value of
> the fields Year, Month,Ten (1=first ten days of month; 2=second ten
> days of month; 3=up to eleven last days of month). and the field in
> which the datum is (RainDay1, RainDay2...)
>
> This is no useful for me because I need something like:
>
> cod_var | Year | Month | Day | Rain |
> --------------+-----------+------------+---------+----------------+--
> 452 | 1995 | 1 | 1 | 2 |
> 452 | 1995 | 1 | 2 | 5 |
> 452 | 1995 | 1 | 3 | 6 |
> 452 | 1995 | 1 | 4 | -3 |
> 452 | 1995 | 1 | 5 | 0 |
> 452 | 1995 | 1 | 6 | 5 |
> ...................
>
> Perhaps this is not possible to do with a database? Should I manage to
> make a program to fix this?
I'm sure there's a better way, but I think a series of union alls would
do it but be rather computationally expensive.
select cod_var, Year, Month, 1 as Day, RainDay1 as Rain
where Ten=1
union all
select cod_var, Year, Month, 2 as Day, RainDay2 as Rain
where Ten=1
...
union all
select cod_var, Year, Month, 11 as Day, Rain Day 1 as Rain
where Ten=2
...
union all
select cod_var, Year, Month, 29 as Day, RainDay9 as Rain
where Ten=3 and RainDay29 is not null;
...
I'm uncertain what you do for days at the end of a month that
don't exist, I'm guessing they have nulls.
In 7.3, I'd say you might be able to make a function to do this
that returns a result set which would probably be much better.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Hairy question - transpose columns
@ 2002-10-23 18:18 eric soroos <eric-psql@soroos.net>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: eric soroos @ 2002-10-23 18:18 UTC (permalink / raw)
To: pgsql-sql
> I'm sure there's a better way, but I think a series of union alls would
> do it but be rather computationally expensive.
>
> select cod_var, Year, Month, 1 as Day, RainDay1 as Rain
> where Ten=1
> union all
> select cod_var, Year, Month, 2 as Day, RainDay2 as Rain
> where Ten=1
You could do the following:
select cod_var, Year, Month, 1+((ten-1)*10) as Day, RainDay1 as Rain
where RainDay1 is not null
union all
select cod_var, Year, Month, 2+((ten-1)*10) as Day, RainDay2 as Rain
where RainDay2 is not null
..
I'm sure that there is a function that could do this too, but I'd tend to just convert the data and be done with it.
eric
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Hairy question - transpose columns
@ 2002-10-26 20:14 mark carew <markcarew@bigpond.com>
parent: andres javier garcia garcia <andresjavier.garcia@wanadoo.es>
1 sibling, 0 replies; 4+ messages in thread
From: mark carew @ 2002-10-26 20:14 UTC (permalink / raw)
To: pgsql-sql
Hi Andres,
For mine, I would read the data in using any langauage that I was
familiar with and parse it into
an array or some other form in the required format then create the "copy"
statement for postgresql,
connect and apply.
Regards Mark
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-10-26 20:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-23 17:56 Hairy question - transpose columns andres javier garcia garcia <andresjavier.garcia@wanadoo.es>
2002-10-23 18:06 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-10-23 18:18 ` eric soroos <eric-psql@soroos.net>
2002-10-26 20:14 ` mark carew <markcarew@bigpond.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox