Row into column in Python

Question

I have a CSV file containing a time serie of daily precipitation. The problem arises of how the data is organized. Here a small sample to ilustrate:

date        p01 p02 p03 p04 p05 p06
01-01-1941  33.6 7.1 22.3 0 0 0
01-02-1941  0 0 1.1 11.3 0 0

So, there is a column to each day of the month (p01 is the precipitation of the day 1, p02 corresponds to the day 2, and so on ). I'd like to have this structure: one column to date and another to precipitation values.

date        p
01-01-1941  33.6
02-01-1941  7.1
03-01-1941  22.3
04-01-1941  0
05-01-1941  0
06-01-1941  0
01-02-1941  0
02-02-1941  0
03-02-1941  1.1
04-02-1941  11.3
05-02-1941  0
06-02-1941  0

I have found some code examples, but unsuccessfully for this specific problem. In general they suggest to try using pandas, numpy. Does anyone have a recommendation to solve this issue or a good advice to guide my studies? Thanks. (I'm sorry for my terrible English)

Answer 1

I think you can first use read_csv, then to_datetime with stack for reshaping DataFrame, then convert column days to_timedelta and add it to column date:

import pandas as pd
import io

temp=u"""date;p01;p02;p03;p04;p05;p06
01-01-1941;33.6;7.1;22.3;0;0;0
01-02-1941;0;0;1.1;11.3;0;0"""
#after testing replace io.StringIO(temp) to filename
df = pd.read_csv(io.StringIO(temp), sep=";")
print df
         date   p01  p02   p03   p04  p05  p06
0  01-01-1941  33.6  7.1  22.3   0.0    0    0
1  01-02-1941   0.0  0.0   1.1  11.3    0    0

#convert column date to datetime
df.date = pd.to_datetime(df.date, dayfirst=True)
print df
        date   p01  p02   p03   p04  p05  p06
0 1941-01-01  33.6  7.1  22.3   0.0    0    0
1 1941-02-01   0.0  0.0   1.1  11.3    0    0

#stack, rename columns
df1 = df.set_index('date').stack().reset_index(name='p').rename(columns={'level_1':'days'})
print df1
         date days     p
0  1941-01-01  p01  33.6
1  1941-01-01  p02   7.1
2  1941-01-01  p03  22.3
3  1941-01-01  p04   0.0
4  1941-01-01  p05   0.0
5  1941-01-01  p06   0.0
6  1941-02-01  p01   0.0
7  1941-02-01  p02   0.0
8  1941-02-01  p03   1.1
9  1941-02-01  p04  11.3
10 1941-02-01  p05   0.0
11 1941-02-01  p06   0.0

#convert column to timedelta in days
df1.days = pd.to_timedelta(df1.days.str[1:].astype(int) - 1, unit='D')
print df1.days
0    0 days
1    1 days
2    2 days
3    3 days
4    4 days
5    5 days
6    0 days
7    1 days
8    2 days
9    3 days
10   4 days
11   5 days
Name: days, dtype: timedelta64[ns]

#add timedelta
df1['date'] = df1['date'] + df1['days']
#remove unnecessary column
df1 = df1.drop('days', axis=1)
print df1
         date     p
0  1941-01-01  33.6
1  1941-01-02   7.1
2  1941-01-03  22.3
3  1941-01-04   0.0
4  1941-01-05   0.0
5  1941-01-06   0.0
6  1941-02-01   0.0
7  1941-02-02   0.0
8  1941-02-03   1.1
9  1941-02-04  11.3
10 1941-02-05   0.0
11 1941-02-06   0.0