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: I think you can first use [`read_csv`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.read_csv.html), then
[`to_datetime`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.to_datetime.html) with
[`stack`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.DataFrame.stack.html) for reshaping `DataFrame`,
then convert column `days` [`to_timedelta`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.to_timedelta.html) 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 coolumn 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
    

