Pandas Merge Two Dataframes

I am new to pandas module. I have a small question regarding pandas merge method. Suppose I have two separate tables, as follows: Original_DataFrame machine weekNum Percent M1

Solution 1:

I think you need combine_first, but first set_index by common columns:

df11 = df1.set_index(['machine','weekNum'])
df22 = df2.set_index(['machine','weekNum'])

df = df11.combine_first(df22).astype(int).reset_index()
print (df)
  machine  weekNum  Percent
0      M1        1      100
1      M1        2       75
2      M1        3      100
3      M1        4      100
4      M1        5       80
5      M1        6      100
6      M1        7      100
7      M1        8       95
8      M1        9      100
9      M1       10       90


df.plot.bar('weekNum', 'Percent')

EDIT:

For labels:

plt.figure(figsize=(12, 8))
ax = df.plot.bar('weekNum', 'Percent')
rects = ax.patches

for rect, label in zip(rects, df['Percent']):
    height = rect.get_height()
    ax.text(rect.get_x() + rect.get_width()/2, height + 1, label, ha='center', va='bottom')

plt.ylim(ymax=120)

Solution 2:

Not as elegant as the other solution, but works anyway:

# join
merged = pd.merge(data1, data2, on=['machine','weekNum'], how='outer')
# combine percent columns
merged['Percent'] = merged['Percent_x'].fillna(merged['Percent_y'])
# remove extra columns
result = merged[['machine','weekNum', 'Percent']]

result:

machine weekNum Percent
M1  275
M1  580
M1  895
M1  1090
M1  1100
M1  3100
M1  4100
M1  6100
M1  7100
M1  9100

Solution 3:

You could try this. Depending on your overall goal this may not be "programatic" enough.

import pandas as pd    
df1 = pd.DataFrame({"machine":["M1"]*4, "WeekNum": [2,5,8,10], "Percent":[75,80,95,90]})
df2 = pd.DataFrame({"machine":["M1"]*10,"WeekNum":np.arange(1,11,1),"Percent":[100]*10})
newcol = df2.merge(df1, on = "WeekNum", how = "outer")["Percent_y"].fillna(100)
df2["Percent"] = newcol