Search code examples
pythonpandasmergeworksheet-function

Pandas Merge cell when output to Excel

I have two tables. Table 1 is by week/day and Table 2 is by week. When I merge the two tables in pandas and output to Excel, how can I get a result in Table 3 rather than Table 4? The point is to show only 1 value for col2 for the same week in Excel, not to have Col2 values duplicated for each day. The table is actually large and has more dimensions, so I'm looking for an extendable solution, not for this small table specifically. I'm thinking about both pandas merge functions and worksheet functions but having trouble. Thanks!

Table 1 enter image description here

Table 2 enter image description here

Table 3 enter image description here

Table 4 enter image description here

Solution 
  • d1 = {'week': [0, 0, 0, 1, 1, 1],
      'day': [1, 2, 3, 1, 2, 3], 'col1': [30, 40, 20, 10, 20, 50]}
df1 = pd.DataFrame(data=d1)

d2 = {'week': [0, 1], "col2": [100, 200]}
df2 = pd.DataFrame(data=d2)

# Get table 4
dm = df1.merge(df2, how="right", on="week")

dm = dm.drop_duplicates(subset=['col2'], keep='last')

# Get table 3
desired = df1.merge(dm, how="left")