Merge dataframe and filling to a matrix

user_12004881 0
Viewed 48

I want to merge 2 dataframes & return a matrix & ffilling at the same time. My two dataframes are

AR = {"Supplier": ["A","A","A","B",],
      "Currency": ["AUD","AUD","USD","AUD"],
      "Day": [1,3,1,1],
      "Amount": [510,530,610,710]}
AR = pd.DataFrame(AR)

IntTable = {"Currency": ["AUD","AUD","AUD","USD","USD","USD"],
            "Day": [1,2,3,1,2,3],
            "InterestRate": [11,12,13,21,22,23]}
IntTable = pd.DataFrame(IntTable)

I tried this

Table = pd.merge_ordered(IntTable, AR, on=['Currency','Day'], 
                         how= 'outer', fill_method='ffill', left_by=['Day'])

Result I get:

Result I get

Result I want:

Result should be

pandas merge python
1 Answers

Here's a way to achieve what you want. I'm starting by creating the skeleton for the result:

  • For each combination of supplier and currency in AR, you'd like to have all three days.
  • Then, I'm filling in the relevant data:

    df = pd.merge(AR[["Supplier", "Currency"]].drop_duplicates().assign(dummy=1), 
             IntTable[["Day"]].drop_duplicates().assign(dummy=1), on = "dummy").drop("dummy", axis=1)
    df = pd.merge_ordered(df, IntTable, on = ["Day", "Currency"])
    df = df.sort_values(["Supplier", "Currency"])
    df = pd.merge_ordered(df, AR, on = ["Supplier", "Currency", "Day"], fill_method='ffill')
    print(df)

The output is:

  Supplier Currency  Day  InterestRate  Amount
0        A      AUD    1            11     510
1        A      AUD    2            12     510
2        A      AUD    3            13     530
3        A      USD    1            21     610
4        A      USD    2            22     610
5        A      USD    3            23     610
6        B      AUD    1            11     710
7        B      AUD    2            12     710
8        B      AUD    3            13     710
user_11055600
Related