CODEWITHSUNDEEP
pythonpandas
aagaard
aagaard

Reputation: 1666

Pandas Dataframe splitted into weeks

I have problems reshaping a dataframe into weeks, such that I'm able to look at one particular week easy, but also aggregated week-days together, i.e. Monday + Monday, Tuesday + Tuesday, etc.

I have looked in the documentation for an approach, but I have not been able to find a solution that works for me. My data has a resolution of 1 min and a duration of 4 months, and the series has missing data at some locations.

Currently I have come up with something like:

def week_reshaping(df):
    # Define constant for offsetting the loop
    offset = pd.DateOffset(days=7)

    # Number of weeks within the df
    weeks = (df.index[-1] - df.index[0]).days // 7

    d_datetime = df.index[0]
    df_week = pd.DataFrame()
    for week in range(1, weeks + 1):
        start = df.index.searchsorted(d_datetime)
        end = df.index.searchsorted(offset + d_datetime)

        # Assign this somehow
        df.ix[start:end]

        d_datetime += offset

    return df_week

Upvotes: 2

Views: 3055

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375445

I'm not entirely sure what your objective is here, but you should definitely consider using groupby rather than for loops (which will be much faster).

You can groupby the week (from the DatetimeIndex):

In [1]: rng = pd.date_range('2013', freq='D', periods=10)

In [2]: df = pd.DataFrame(np.random.randn(10), rng)

In [3]: df.index.week
Out[3]: array([32, 32, 32, 33, 33, 33, 33, 33, 33, 33], dtype=int32)

In [4]: df.groupby(df.index.week).sum()
Out[4]:
           0
32  3.600673
33  0.791545

Similarly, you can groupby day (of the week):

In [5]: df.groupby(df.index.dayofweek).sum()
Out[5]:
          0
0  1.268307
1  0.387322
2  1.416948
3 -0.380844
4  1.464068
5  0.030965
6  0.205453

or more complicated arrays derived from these...

I think you'll be able to apply a different function here (rather than sum) to achieve the desired result.

Upvotes: 5

Related Questions