CODEWITHSUNDEEP
pythonpandas
user5721764
user5721764

Reputation:

Pandas, Grouping by date range

I have a dataframe with a column called 'complete' with the date format as: 2017-01-16

I have many rows that span over the last three months, and I want to split up my dataframe into weeklong increments so I can later make a chart showing the amount of specific items from week-to-week, and visualize either it's ascent or descent.

Typically when I don't know An answer, I manually go the long way. in This case I would say for example, 

  weeknumber = 1
  for day in range(earliest, latest, 8):
      df[df.complete == day].week = weeknumber
      weeknumber += 1

As you can see, I'm very confused about how exactly to do this because I can't be sure of exactly what I want until I can play with some output first and see what working options are even available. I searched for other codes like date_range() but I only got all 0's in my weeknumber and had no idea if my code was wrong or my date format, or if I was using the method wrong etc. others were saying that groupby wouldn't work, and so on and so forth.

any help for a noob?

Upvotes: 0

Views: 412

Answers (1)

piRSquared
piRSquared

Reputation: 294218

Consider the dataframe df as our example. You'll want to make sure your complete column is datetime by doing df.complete = pd.to_datetime(df.complete).

tidx = pd.date_range('2016-03-31', periods=95)
df = pd.DataFrame(dict(complete=tidx, A=np.arange(len(tidx))))

df.head()

   A   complete
0  0 2016-03-31
1  1 2016-04-01
2  2 2016-04-02
3  3 2016-04-03
4  4 2016-04-04

These are two common techniques for time grouping

  • resample

    • we pass a string parameter, in this case 'W'

      df.resample('W', on='complete').mean()

  • pd.TimeGrouper

    • similar idea

      df.set_index('complete').groupby(pd.TimeGrouper('W')).sum()

Upvotes: 2

Related Questions