In Pandas you can use groupby()
to create an aggregate for every ID and counting the year where the ID occurs in your dataset:
import pandas as pd
data = {
"id": [1234, 1234, 5678, 5678],
"year": [2018, 2019, 2017, 2020]
}
df = pd.DataFrame(data)
df_grouped = df.groupby(["id", "year"]).size().unstack(fill_value=0)
print(df_grouped)
Where size()
counts the occurrences and unstack()
creates a new level of column labels whose inner-most level consists of the pivoted index labels.
Outputs:
year 2017 2018 2019 2020
id
1234 0 1 1 0
5678 1 0 0 1
As for the visualization, I don't know what your preferences are, but I found a bar diagram to be a poor choice. So I opted for a confusion matrix style of visualization:
import seaborn as sn
import matplotlib.pyplot as plt
sn.set(font_scale=1.4
sn.heatmap(df_grouped, annot=True, square=True, cbar=False)
plt.show()

The benefit of the confusion matrix is, for example, if ID=1234 occurs twice in the same year, the count would be 2 instead of 1 in the visualization.