Pandas combine two data frames append a subset of columns to the rows of output table

user_5170993 0
Viewed 366

I want to merge two data frames and make a subset of the columns rows in the output dataframe. The two tables are as follows

Table Stk

stk = pd.DataFrame({'code':['A121','H812','Z198'],'01-05-2021':[4,2,6],'02-05-2021':[1,3,2],'03-05-2021':[12,13,12]})

Table Sup

sup = pd.DataFrame({'code':['A121','H812','S222'],'01-05-2021':[2,2,2],'03-05-2021':[5,5,5],'06-05-2021':[1,4,7]})

Output Table

Brief Explanation on how the Output table needs to be created:

  1. The output table needs to have a union of all date columns in stk table and sup table
  2. The output table also needs to have 2 rows for union of each code in both the tables
  3. The corresponding value from each table with respect to the code and date needs to assigned in the appropriate cell in the output table
  4. If value does not exist mark it as np.nan in the output table

I hope this is clear. Any suggestion or help regarding this will be greatly appreciated. I have tried using merge and an outer join on both the tables but that creates extra date columns. I am not sure how to create rows out of that.

pandas dataframe python-3.x python
2 Answers

Add new column "code" to each dataframe and concatenate them. Obviously, rows for Z198, sup and S222, stk will be missing. The solution is to .reindex the final dataframe with product of code and mode values. The function .reindex will add NaNs to missing rows automatically.

idx = pd.MultiIndex.from_product(
    [set(stk["code"].tolist() + sup["code"].tolist()), ["stk", "sup"]],
    names=["code", "mode"],
)
x = (
    pd.concat([stk.assign(mode="stk"), sup.assign(mode="sup")])
    .set_index(["code", "mode"])
    .reindex(idx)
    .reset_index()
)
print(x)

Prints:

   code mode  01-05-2021  02-05-2021  03-05-2021  06-05-2021
0  Z198  stk         6.0         2.0        12.0         NaN
1  Z198  sup         NaN         NaN         NaN         NaN
2  H812  stk         2.0         3.0        13.0         NaN
3  H812  sup         2.0         NaN         5.0         4.0
4  A121  stk         4.0         1.0        12.0         NaN
5  A121  sup         2.0         NaN         5.0         1.0
6  S222  stk         NaN         NaN         NaN         NaN
7  S222  sup         2.0         NaN         5.0         7.0
user_100359850

TRY:

sup['mode'] = 'sup'
stk['mode'] = 'stk'

# this function is just to add the misisng rows with NAN values. If you don't want rows with NAN values skip the func.
def add_row(x):
    if (len(x)<2):
        x = x.append([{'code':  x['code'].iloc[0], 'mode':  x['mode'].iloc[0]}])
        return x
    return x
merged_df = stk.merge(sup, how='outer').groupby('code').apply(add_row).reset_index(drop=True)

OUTPUT

   code mode  01-05-2021  02-05-2021  03-05-2021  06-05-2021
0  A121  stk         4.0         1.0        12.0         NaN
1  A121  sup         2.0         NaN         5.0         1.0
2  H812  stk         2.0         3.0        13.0         NaN
3  H812  sup         2.0         NaN         5.0         4.0
4  S222  sup         2.0         NaN         5.0         7.0
5  S222  sup         NaN         NaN         NaN         NaN
6  Z198  stk         6.0         2.0        12.0         NaN
7  Z198  stk         NaN         NaN         NaN         NaN
user_154380330
Related