I have the dataframe below and I want to create a new column that has the Competitors sales growth based on the year.
import pandas as pd
d = {'date': [2000, 2000, 2001, 2001, 2002, 2002, 2003, 2003, 2004, 2004], 'Stock': ["GM", "F", "GM", "F", "GM", "F", "GM", "F", "GM", "F"],
'US Sales Growth': [.3, .3, .1, .1, .6, .6, .12, .12, .7, .7], 'Stock_Growth': [.1, .2, .3, .4, .14, .16, .2, .1,.15,.16],
'Competitor': ["F", "GM", "F", "GM", "F", "GM", "F", "GM", "F", "GM"] }
df = pd.DataFrame(data=d)
[![enter image description here][1]][1]
My goal is to have this new dataframe below
d = {'date': [2000, 2000, 2001, 2001, 2002, 2002, 2003, 2003, 2004, 2004], 'Stock': ["GM", "F", "GM", "F", "GM", "F", "GM", "F", "GM", "F"],
'US Sales Growth': [.3, .3, .1, .1, .6, .6, .12, .12, .7, .7], 'Stock_Growth': [.1, .2, .3, .4, .14, .16, .2, .1,.15,.16],
'Competitor': ["F", "GM", "F", "GM", "F", "GM", "F", "GM", "F", "GM"], 'Competitor_Stock_Growth': [.2, .1, .4, .3, .16, .14, .1, .2,.16,.15]}
df = pd.DataFrame(data=d)
[![enter image description here][1]][1]
CodePudding user response：
Find competitors' stocks and then merge based on date and Competitor:
competitor_stocks = df[['date', 'Stock', 'Stock_Growth']].rename(columns={'Stock_Growth': 'Competitor_Stock_Growth', 'Stock': 'Competitor'})
competitor_stocks
date Competitor Competitor_Stock_Growth
0 2000 GM 0.10
1 2000 F 0.20
2 2001 GM 0.30
3 2001 F 0.40
4 2002 GM 0.14
5 2002 F 0.16
6 2003 GM 0.20
7 2003 F 0.10
8 2004 GM 0.15
9 2004 F 0.16
df.merge(competitor_stocks)
date Stock US Sales Growth Stock_Growth Competitor Competitor_Stock_Growth
0 2000 GM 0.30 0.10 F 0.20
1 2000 F 0.30 0.20 GM 0.10
2 2001 GM 0.10 0.30 F 0.40
3 2001 F 0.10 0.40 GM 0.30
4 2002 GM 0.60 0.14 F 0.16
5 2002 F 0.60 0.16 GM 0.14
6 2003 GM 0.12 0.20 F 0.10
7 2003 F 0.12 0.10 GM 0.20
8 2004 GM 0.70 0.15 F 0.16
9 2004 F 0.70 0.16 GM 0.15