Python: indexing pandas series by datetime

Question

I am new to python and have the following problem:

I have daily measurements that I imported into a pd.series object. The problem is that the data is such that there are always 31 measurements per month, irrespective of whether it is January or February. In case the respective month has less than 31 days, the measurements are set to zero for those days that exceed the last day of that month. However, missing data within a month is also set to zero. The data for the months of April and May looks like this.

1990-04-01    25.870
1990-04-01    26.205
1990-04-01    12.283
1990-04-01    19.630
1990-04-01    19.239
1990-04-01    23.614
1990-04-01    40.891
1990-04-01    41.152
1990-04-01    35.935
1990-04-01    25.682
1990-04-01    21.674
1990-04-01    15.818
1990-04-01    11.413
1990-04-01    16.522
1990-04-01    33.543
1990-04-01    28.727
1990-04-01    18.043
1990-04-01    10.326
1990-04-01    19.159
1990-04-01    21.848
1990-04-01    35.250
1990-04-01    39.152
1990-04-01    31.522
1990-04-01    23.152
1990-04-01    13.250
1990-04-01    20.705
1990-04-01    27.304
1990-04-01    24.478
1990-04-01    33.674
1990-04-01    32.591
1990-04-01     0.000
1990-05-01    40.370
1990-05-01    41.609
1990-05-01    47.478
1990-05-01    40.682
1990-05-01    42.587
1990-05-01    38.826
1990-05-01    35.543
1990-05-01    30.955
1990-05-01    23.543
1990-05-01     7.857
1990-05-01     0.000
1990-05-01     0.000
1990-05-01     0.000
1990-05-01     0.000
1990-05-01     0.000
1990-05-01     0.000
1990-05-01    54.133
1990-05-01    41.114
1990-05-01    44.739
1990-05-01    43.848
1990-05-01    26.739
1990-05-01    21.318
1990-05-01    26.750
1990-05-01    54.864
1990-05-01    33.000
1990-05-01    33.304
1990-05-01    34.304
1990-05-01    20.886
1990-05-01    20.250
1990-05-01    24.804
1990-05-01    28.091
Length: 62

Is there a way to drop those entries that do not belong to the respective months and provide a new time-index for each day? I need to keep the zeros within a month.

I'm taking a bit of a punt here, because I don't have pandas installed, but what happens if you try s[['1990-04-01', '1990-04-02']] ? (Assuming your series is called s, of course.) If it gives you something sensible then the problem is now a much more tractable one of generating a list of all the valid indices. — Dave
– Dave, Commented Nov 22, 2013 at 17:19

Andy Hayden · Accepted Answer · 2013-11-22 18:12:17Z

First I would replace the 0s with NaN (to symbolise missing data):

s.replace(0, np.nan, inplace=True)

One way is to create a function which acts on each group (month):

def f(s_month):
    date = s_month.index[0]
    # create the month long range
    rng = pd.date_range(date, date + pd.offsets.MonthEnd(1), freq='D')
    # throw away results longer than month length
    s_month = s_month.iloc[0:len(rng)]
    # reindex those remaining
    s_month.index = rng
    return s_month

Note: this requires that you have a DatetimeIndex, i.e. s.index = pd.to_datetime(s.index).

In [11]: s_month = s.loc['1990-04-01']

In [12]: f(s_month)
Out[12]: 
1990-04-01    25.870
1990-04-02    26.205
1990-04-03    12.283
1990-04-04    19.630
...
1990-04-28    24.478
1990-04-29    33.674
1990-04-30    32.591
Freq: D, Name: Value, dtype: float64

Use this with a groupby apply:

In [13]: res = s.groupby(s.index).apply(f)

In [14]: res
Out[14]: 
1990-04-01  1990-04-01    25.870
            1990-04-02    26.205
            1990-04-03    12.283
            1990-04-04    19.630
...

Correct the MultiIndex:

In [15]: res.index = res.index.droplevel(0)

In [16]: res
Out[16]: 
1990-04-01    25.870
1990-04-02    26.205
1990-04-03    12.283
...

Collectives™ on Stack Overflow

Python: indexing pandas series by datetime

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related