Home > Enterprise >  How to make a pivot table from a dataframe that groups by year and count my data
How to make a pivot table from a dataframe that groups by year and count my data

Time:10-20

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
  • Related