2

Context: I'm trying to get the count for weekly dates on a given dataframe that has a format with daily dates, a categorical variable called "X" that has values "A","B" and "C" as follows:

pd.DataFrame({'Date':['2020-01-01','2020-01-02','2020-01-03','2020-01-04','2021-01-01'],'X': ['A','B','C','B','C']})

Then, I'm grabbing the initial weeks from the dates and creating the pivot_table as such:

dataframe[week_commencing_col] = pd.to_datetime(
    dataframe[date_col]-pd.to_timedelta(dataframe[date_col].dt.weekday,unit='D')).apply(lambda x: x.strftime('%Y-%m-%d')
    )
   

 pivoted_dataframe = dataframe.pivot_table(
        values='X',
        index='Week',
        columns='X',
        aggfunc='count',
        margins=True,
        margins_name='Total')

Essentially, I want the columns of the pivot table to be the unique values of the column X and I want the counts to be aggregated by week commencing using the same column X (the weekly counts of X by it's unique value)

Is there a way to do this using pivot_table without using groupby or crosstab?

The desired output would be Something like this:

X                  A        B         C    Total
Week
2020-01-01         1        3        20     24
2020-01-08         5        6        44     55
2020-01-15         5        6        25     36
cottontail
  • 10,268
  • 18
  • 50
  • 51
Chronicles
  • 436
  • 1
  • 11

1 Answers1

1

Following @cottontail suggestion that worked:

dataframe.assign(vals=1).pivot_table(values='vals', index='Date', columns='X', aggfunc='count', fill_value=0)

Having as reference his answer on this SO topic: How can I pivot a dataframe?

Chronicles
  • 436
  • 1
  • 11