Home > Enterprise >  Count occurrences of column value, then input that count into a column, for every row matching aggre
Count occurrences of column value, then input that count into a column, for every row matching aggre

Time:12-02

I am trying to input an aggregated occurrence of a columns' values, into a distinct column in the same Dataframe. I have tried to count the dates column, and input those occurrences into a dict value, and the date as a dict key. Then when I try to (boolean) match df["count"] = df[df[column]==my_dict.keys] the logic fails on me. I am missing the part where if true, then input my_dict.values().

The Dataframe:

    Date        Place_code 
14  2020-04-20  282
16  2020-04-23  301
18  2020-04-22  291
20  2020-04-22  422
21  2020-04-22  422

Desired Dataframe output:

    Date        Occurrence    Place_code 
14  2020-04-20  1             282
16  2020-04-23  1             301
18  2020-04-22  3             291
20  2020-04-22  3             422
21  2020-04-22  3             422

Also forgive the title, english is not my native language.

CodePudding user response:

You could try this one liner:

df['Occurrence'] = df['Date'].replace(df.groupby('Date').apply(lambda x: len(x))

CodePudding user response:

One option is to use transform to return the same length as the original dataframe:

df.assign(Occurence = df.groupby('Date').transform('count'))
# df.assign(Occurence = df.groupby('Date').transform(len))

          Date  Place_code  Occurence
14  2020-04-20         282          1
16  2020-04-23         301          1
18  2020-04-22         291          3
20  2020-04-22         422          3
21  2020-04-22         422          3
  • Related