How to merge pandas dataframe to a dataframe with more columns, while filling in the empty columns with corresponding values?

Question

I have two dataframes, df1 with (for example) 5 rows and 3 columns, and df2 with 5 rows but only 2 columns, as such:

df1 = pd.DataFrame({"First_Name":['Mark','John','Adam','Mark','Adam'], "Purchased":[20,12,13,40,23], "Last_Name":['S.','M.','C.','S.','C.'])

    First_Name  Purchased   Last_Name
0   Mark    20  S.
1   John    12  M.
2   Adam    13  C.
3   Mark    40  S.
4   Adam    23  C.

df2 = pd.DataFrame({"First_Name":['Jane','Mark','Mark','Adam','Jane'], "Purchased":[3,16,17,10,23]})

    First_Name  Purchased
0   Jane    3
1   Mark    16
2   Mark    17
3   Adam    10
4   Jane    23

I want to append the rows from df2 to df1, while also creating values for the third column (in this example, "Last Name") based on the values from df1.

For example, I want the output to be:

    First_Name  Purchased   Last_Name
0   Mark    20  S.
1   John    12  M.
2   Adam    13  C.
3   Mark    40  S.
4   Adam    23  C.
5   Jane    3   nan
6   Mark    16  S.
7   Mark    17  S.
8   Adam    10  C.
9   Jane    23  nan

Is there any way to do all these functions simply? Thanks!

Answer 1

This should do the trick:

final = pd.concat([df1, df2]).reset_index()
final.sort_values('First_Name')
final.fillna(method='ffill')

which gives

  index First_Name  Purchased Last_Name
0      0       Mark         20        S.
3      3       Mark         40        S.
6      1       Mark         16        S.
7      2       Mark         17        S.
1      1       John         12        M.
5      0       Jane          3        M.
9      4       Jane         23        M.
2      2       Adam         13        C.
4      4       Adam         23        C.
8      3       Adam         10        C.