Adding a column that counts the number of occurrences of L2 values in column 'L1'-CodePudding

How do you add a column to a dataframe that counts the number of occurrences of L2 values in column 'L1' as follows:

input_table = pd.DataFrame({'L1':['A', 'A', 'B', 'C', 'C'], 'L2':['B', 'C', 'C', 'D', 'E']})

output_table = pd.DataFrame({'L1':['A', 'A', 'B', 'C', 'C'], 'L2':['B', 'C', 'C', 'D', 'E'], 'count L2 in L1':[1, 2, 2, 0, 0]})

CodePudding user response：

You can use the groupby function from pandas module to count the occurrences of L2 in L1, and then merge the resulting data back to the original data frame based on L2.

    output_table = pd.merge(
        input_table,
        input_table.groupby(by='L1').count().reset_index().
            rename(columns={'L1':'L2',"L2":'count L2 in L1'}),
            how='left',on='L2').fillna(0)

CodePudding user response：

You could use value_counts and transform:

vc = input_table['L1'].value_counts()
input_table['count L2 in L1'] = input_table['L2'].transform(lambda x: vc.get(x, 0))

Output:

>>> input_table
  L1 L2  count L2 in L1
0  A  B               1
1  A  C               2
2  B  C               2
3  C  D               0
4  C  E               0

CodePudding user response：

One option is to use a combination of isin and groupby:

input_table['count L2 in L1'] = (input_table
                                 .L2
                                 .isin(input_table.L1)
                                 .groupby(input_table.L2)
                                 .transform('sum')
                               )

input_table

  L1 L2  count L2 in L1
0  A  B               1
1  A  C               2
2  B  C               2
3  C  D               0
4  C  E               0