Thread: Hairy question - transpose columns
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
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 Rainwhere Ten=1 union all select cod_var, Year, Month, 2 as Day, RainDay2 as Rainwhere Ten=1 ... union all select cod_var, Year, Month, 11 as Day, Rain Day 1 as Rainwhere Ten=2 ... union all select cod_var, Year, Month, 29 as Day, RainDay9 as Rainwhere 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.
> 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 nullunion allselect 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
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