Hello people i have a question, i have a dataframe with different products and dates, something like this:
| product | date |
|---|---|
| A | 01-01-2016 |
| A | 01-02-2016 |
| B | 23-04-2016 |
| B | 22-02-2016 |
| A | 02-12-2017 |
| A | 13-11-2017 |
| B | 12-12-2017 |
What i want to do is create a pivot table that counts how many times product A and B are in each year.
Expected output is something like this:
| product | 2016 | 2017 |
|---|---|---|
| A | 2 | 2 |
| B | 2 | 1 |
Thank you for your support and time.
CodePudding user response：
As long as the date is a datetime object you can use df['date'].dt.year as your columns in the pivot.
import pandas as pd
df = pd.DataFrame({'product': ['A', 'A', 'B', 'B', 'A', 'A', 'B'],
'date': ['01-01-2016',
'01-02-2016',
'23-04-2016',
'22-02-2016',
'02-12-2017',
'13-11-2017',
'12-12-2017']})
df['date'] = pd.to_datetime(df['date'])
df = df.pivot_table(index='product', columns=df['date'].dt.year, aggfunc='count').droplevel(0, axis=1).rename_axis(None)
print(df)
Output
date 2016 2017
A 2 2
B 2 1