Considering two tables: one which contains all the indices, and another which contains data for some indices. The purpose is to have an entry for all index, regardless of it having data, and if it does, then add it. Similar to a LEFT OUTER JOIN
in SQL.
The indices table, named dates
, could be something like:
name week
0 name_0 0
1 name_0 1
2 name_0 2
3 name_0 3
4 name_0 4
5 name_0 5
6 name_1 0
7 name_1 1
8 name_1 2
so the table would contain 6 weeks for name_0
, and 3 weeks for name_1
.
Whereas the data table, named data_holes
, could be:
name week otherdata
0 name_0 1 2
1 name_0 2 5
2 name_0 5 20
3 name_1 1 5
The desired merge result would be:
name week other_data
0 name_0 0 N/A
1 name_0 1 2
2 name_0 2 5
3 name_0 3 N/A
4 name_0 4 N/A
5 name_0 5 20
6 name_1 0 N/A
7 name_1 1 N/A
8 name_1 2 5
However, when using dates.merge(data_holes, how='left', on=['name','week'])
I am faced with an error:
ValueError: The column label 'week' is not unique.