I am trying to mark the frequency of identical value (year) in a dataframe and to show it as a column in the original dataframe. Prior to that I need to group all the rows based on another column. This is dummy data, in principle, I have many more columns and rows. This is what I have done but I do not know how to map it back to the original dataframe (into a separate column)
data1 = [[1, 'Tom', 1987], [1, 'Bill', 1987], [1, 'Andrew', 1988],[2, 'Ann', 1994], [2, 'Olya', 1984], [2, 'Andrew', 1984], [2, 'Tony', 1989]]
df1 = pd.DataFrame(data1, columns = ['group', 'name', 'year'])
unique_year = df1.groupby(['group'])['year'].apply(lambda s: s.value_counts())
freq = unique_year.to_frame()
freq
group year
1 1987 2
1988 1
2 1984 2
1994 1
1989 1
This is the desired result:
group name year year_frequency
1 Tom 1987 2
1 Bill 1987 2
1 Andrew 1988 1
2 Ann 1994 1
2 Olya 1984 2
2 Andrew 1984 2
2 Tony 1989 1
I am sorry for the lack of clarity, I am very new to pandas.
