Q: How to make a pivot table from a dataframe that groups by year and count my data 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.

A: 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

