Concatenating and sorting table does not work as expected-CodePudding

I have two tables: DF and DF2 with the same structure, like below:

df = [
    ['2021-10-02', 'abc', 'cash', '1.50'], ['2021-10-02', 'xyz', 'cash', '2.34'], ['2021-10-02', 'abc', 'ex', '4.55'], ['2021-10-02', 'xyz', 'cash', '10.55'],
    ['2021-10-05', 'jjh', 'cash', '1.20'], ['2021-10-05', 'xyz', 'cash', '0.34'], ['2021-10-05', 'abc', 'cash', '0.43'], ['2021-10-05', 'xyz', 'cash', '34.20'], ['2021-10-05', 'jdh', 'ex', '0.20'], ['2021-10-05', 'xyz', 'hyj', '12.00'],
    ['2021-10-09', 'jhf', 'ex', '12.30'],  ['2021-10-09', 'xyz', 'cash', '1.89'], ['2021-10-09', 'abc', 'ex', '4.05'], ['2021-10-09', 'kgh', 'ex', '3.45'],
    ]

DF = pd.DataFrame(df, columns = ['date', 'typ', 'method', 'price']) 
DF

df2 = [['2021-10-02', '', '', '100'], ['2021-10-05', '', '', '150'], ['2021-10-09', '', '', '234']]
DF2 = pd.DataFrame(df2, columns = ['date', 'typ', 'method', 'price'])  
DF2

Right now, I would like to concat these two tables (DF and DF2) into one, in such a way that the rows from table DF2 are always at the end of the selected date in table DF with respect to the date column.

The expected result:

I tried to use concat and later sort_values like this below:

pd.concat([DF, DF2]).sort_values(by = 'date').reset_index(drop = True)

Then, I get this:

Do you know where is the issue? Or maybe is easier way to achieve expected result?

Thanks for any ideas.

CodePudding user response：

Default sort in DataFrame.sort_values if sorting by one column is not stable quicksort, so for expected ouput need mergesort like:

df = pd.concat([DF, DF2]).sort_values(by = 'date', kind='mergesort', ignore_index=True)
print (df)
          date  typ method  price
0   2021-10-02  abc   cash   1.50
1   2021-10-02  xyz   cash   2.34
2   2021-10-02  abc     ex   4.55
3   2021-10-02  xyz   cash  10.55
4   2021-10-02                100
5   2021-10-05  jjh   cash   1.20
6   2021-10-05  xyz   cash   0.34
7   2021-10-05  abc   cash   0.43
8   2021-10-05  xyz   cash  34.20
9   2021-10-05  jdh     ex   0.20
10  2021-10-05  xyz    hyj  12.00
11  2021-10-05                150
12  2021-10-09  jhf     ex  12.30
13  2021-10-09  xyz   cash   1.89
14  2021-10-09  abc     ex   4.05
15  2021-10-09  kgh     ex   3.45
16  2021-10-09                234