I have a dataset looking like this for simplication:
lst = [
["2015", "A"],
["2015", "B"],
["2015", "C"],
["2016", "A"],
["2016", "A"],
["2016", "B"],
["2016", "D"]
]
df = pd.DataFrame(lst, columns =["Year", "Item"])
I want to generate some stats like the following. How can I do this using Pandas? My actual dataset has tens of thousands of observations, and many different items.
Thank you!
Consider running inline aggregates with transform and merge on a data frame of all possible values. Finally, clean up with fillna procedures:
from itertools import product
...
years_items_df = pd.DataFrame(product(["2015", "2016"], list("ABCD")),
columns = ["Year", "Item"])
df = (df.assign(Count = lambda x: x.groupby(["Year", "Item"])["Year"].transform("count"),
AnnualCount = lambda x: x.groupby(["Year"])["Year"].transform("count"))
.drop_duplicates()
.merge(years_items_df, on=["Year", "Item"], how="right")
.sort_values(["Year", "Item"])
.assign(Count = lambda x: x['Count'].fillna(0),
AnnualCount = lambda x: x['AnnualCount'].ffill(),
Percent = lambda x: x["Count"].div(x["AnnualCount"]))
.reset_index(drop=True)
)
df
# Year Item Count AnnualCount Percent
# 0 2015 A 1.0 3.0 0.333333
# 1 2015 B 1.0 3.0 0.333333
# 2 2015 C 1.0 3.0 0.333333
# 3 2015 D 0.0 3.0 0.000000
# 4 2016 A 2.0 4.0 0.500000
# 5 2016 B 1.0 4.0 0.250000
# 6 2016 C 0.0 4.0 0.000000
# 7 2016 D 1.0 4.0 0.250000
