Hello,
I have a dataset. It records the products that companies produce each year and the components for each product ("Categories" column is only for convenience and it is not in my real dataset). All the components of each firm in five years consist of a component base. It is updated as firms introduce more products. The file has been sorted by FirmID, Beginyear and components.
If all the components of a new product do not overlap with the component base in five years, it will be considered as radical products. For example, Firm 10002 introduced KJ128F in 1996 and its components were Z14 and T32, which do not exist in the component base between 1991 and 1995 within Firm 10002. Thus KJ128F is a radical product.
If one of the components of a new product overlaps with the component base in five years, it will be considered as incremental products. For example, Firm 10002 introduced OP284L in 1995 and its component was X48. X48 exists in the component base between 1990 and 1994. So OP284L is an incremental product.
I would like to know how many incremental products and radical products that each firm has for each year. The output that I want is below. In case you need the file, I also attached the excel file.
Have data:
FirmID | ProductID | BeginYear | Components | Categories |
10001 | AA386G | 1992 | B30 | radical |
10001 | AA386G | 1992 | V55 |
10001 | AA386G | 1992 | T92 |
10001 | AB259T | 1993 | R26 | Incremental |
10001 | AB259T | 1993 | V55 |
10001 | AB259T | 1993 | U38 |
10001 | AC449P | 1993 | Y38 | radical |
10001 | AC449P | 1993 | I92 |
10002 | XV429S | 1990 | E33 | radical |
10002 | CT746B | 1991 | X48 | radical |
10002 | CT746B | 1991 | K93 |
10002 | CT746B | 1991 | O28 |
10002 | RU239F | 1992 | X48 | Incremental |
10002 | RU239F | 1992 | Q15 |
10002 | DH469E | 1993 | K93 | Incremental |
10002 | DH469E | 1993 | Q15 |
10002 | MH387T | 1994 | O28 | Incremental |
10002 | OP284L | 1995 | X48 | Incremental |
10002 | KJ128F | 1996 | Z14 | Radical |
10002 | KJ128F | 1996 | T32 |
10003 | CN726Q | 1991 | E55 | radical |
10003 | SZ116R | 1992 | E55 | Incremental |
10003 | SZ116R | 1992 | W87 |
10003 | TU692E | 1992 | W87 | Incremental |
10003 | TU692E | 1992 | I92 |
want data
FirmID | BeginYear | nb_Incremental | nb_Radical |
10001 | 1992 | 0 | 1 |
10001 | 1993 | 1 | 1 |
10002 | 1990 | 0 | 1 |
10002 | 1991 | 0 | 1 |
10002 | 1992 | 1 | 0 |
10002 | 1993 | 1 | 0 |
10002 | 1994 | 1 | 0 |
10002 | 1995 | 1 | 0 |
10002 | 1996 | 0 | 1 |
10003 | 1991 | 0 | 1 |
10003 | 1992 | 2 | 0 |