I have multiple files that contains 12 weeks worth of sales data and i receive them every month. I will loop through the folder and append them into one df. Because the monthly file contains past 12 weeks of data, there are some overlapping.
Example:
File 1 (sales_mar2022.csv) contains Jan 2022 to Mar 2022
File 2 (sales_apr2022.csv) contains Feb 2022 to Apr 2022
File 3 (sales_jun2022.csv) contains Mar 2022 to Jun 2022 and continue.
The file contains columns like
Date | Product ID | Brand | Sales | Volume
01/01/2022 | 1232 | Brand X | 22332.234 | 23424
The latest file always contains the most updated values due to back dating of data. I want to perform something similar like SQL upsert function using pandas.
The index of each file is not the same so pandas update function dont really work well.
I tried appending all files into one combined df and used drop_duplicates on dates and product ID but unable to know which row to keep from the most updated file.
The combined df contains about 10 millions rows so appending all and dropping duplicates might not be efficient as well.
Appreciate for any suggestions. Thanks!
