I try to update stock data in one csv file with new rows in another. Because of the way I retrieve this data, the rows do partly overlap. The basic stock file contains (simplified example):
Mar 08, 2016 9692.82 9688.47 9785.05 9617.69 95.75M -0.88% Mar 07, 2016 9778.93 9764.08 9803.73 9690.00 78.15M -0.46% Mar 04, 2016 9824.17 9800.86 9899.11 9742.76 93.45M 0.74% Mar 03, 2016 9751.92 9807.06 9808.52 9709.68 85.25M -0.25% Mar 02, 2016 9776.62 9780.84 9837.11 9695.98 106.45M 0.61% Mar 01, 2016 9717.16 9482.66 9719.02 9471.09 99.54M 2.34% Feb 29, 2016 9495.40 9424.93 9498.57 9332.42 93.79M -0.19%
This file should be updated with the data from a second file:
Mar 11, 2016 9831.13 9672.05 9833.90 9642.79 118.96M 3.51% Mar 10, 2016 9498.15 9697.64 9995.84 9498.15 177.50M -2.31% Mar 09, 2016 9723.09 9700.16 9838.95 9679.19 100.90M 0.31% Mar 08, 2016 9692.82 9688.47 9785.05 9617.69 95.75M -0.88% Mar 07, 2016 9778.93 9764.08 9803.73 9690.00 78.15M -0.46%
The code I use to try to achieve the update looks like that:
existingquotes = pd.read_csv(filenames_quotes[i], parse_dates=[0], infer_datetime_format=True, header=None, delimiter='\t')
newquotes = pd.read_csv(filenames_upd[i], parse_dates=[0], infer_datetime_format=True, header=None, delimiter='\t')
existingquotes.update(newquotes)
mergedquotes=existingquotes
print mergedquotes
The output looks like this:
0 1 2 3 4 5 6
0 2016-03-11 9831.13 9672.05 9833.90 9642.79 118.96M 3.51%
1 2016-03-10 9498.15 9697.64 9995.84 9498.15 177.50M -2.31%
2 2016-03-09 9723.09 9700.16 9838.95 9679.19 100.90M 0.31%
3 2016-03-08 9692.82 9688.47 9785.05 9617.69 95.75M -0.88%
4 2016-03-07 9778.93 9764.08 9803.73 9690.00 78.15M -0.46%
5 2016-03-01 9717.16 9482.66 9719.02 9471.09 99.54M 2.34%
6 2016-02-29 9495.40 9424.93 9498.57 9332.42 93.79M -0.19%
There is a gap between 2016-03-01 and 016-03-07. If I use
existingquotes.update(newquotes), overwrite=False)
the update looks like the original csv. Appreciate any help!