Pandas:在重复的时间戳上重新采样

2024-03-29 08:20:58 发布

您现在位置:Python中文网/ 问答频道 /正文

我正在寻找一种解决方案,对基于全球导航卫星系统的数据进行重新采样。有两个csv格式的文件。数据可以通过时间戳和接收器明确识别。为了在数据库中更有效地使用,必须减少数据

第一个csv文件的原始数据:

^{tb1}$

以及使用以下方法重新采样的版本:

df_gnss = df_gnssdata.resample("1S", label="right", closed="right").last()
^{tb2}$

每件事都是按方面进行的

关于这个问题: 来自第二个csv文件的原始数据

|datetime,svId,gnssId,prMes,cpMes,doMes,freqId,locktime,cno,elev,azim,prRes,receiver
|-
| 2020-06-15 10:41:51.800,18,0,18819690.864839826,98898148.01376344,9100.7783203125,0,64500,42,63,171,12,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,26,0,18898475.83643752,99312188.42621994,8898.150390625,0,64500,34,61,290,64,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,29,0,19535404.626421425,102659242.90557194,5326.11328125,0,64500,45,50,72,12,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,21,0,20119878.873314977,105730657.34985732,9970.078125,0,64500,46,41,204,-19,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,31,0,20837340.256752983,109500950.49708273,4756.482421875,0,64500,45,34,224,8,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,16,0,21098212.356653187,110871854.1766653,10704.1064453125,0,43260,28,26,304,-23,MOD=NEO-M8U
| 2020-06-15 10:41:51.800,5,0,21687470.41932556,113968331.7959013,6576.39306640625,0,41400,36,20,49,-106,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,25,0,22170117.17070227,116504846.7321731,3855.67626953125,0,38720,30,18,134,-68,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,30,2,20674361.08438748,108644483.35774152,6237.05419921875,0,64500,41,66,183,-5,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,27,2,22681250.109272823,119190760.19749376,9190.7265625,0,64500,35,48,302,1,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,15,2,22865522.048461888,120159151.53984466,9805.8271484375,0,64500,41,42,220,4,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,36,2,24114401.037752766,126722022.71452372,4943.53564453125,0,64500,32,28,53,-8,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,24,6,17461541.697876718,93374682.96203424,8347.490234375,9,64500,38,75,266,-8,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,14,6,17547922.134648934,93540242.64641856,7146.93212890625,0,64500,36,71,247,44,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,23,6,18013117.07446595,96358008.47407912,5382.39794921875,10,64500,40,57,39,9,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,15,6,19712758.72882908,105338957.24770068,11033.0087890625,7,64500,39,32,323,5,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,17,6,20421382.08252564,109278920.26213168,11190.1328125,11,64500,38,23,240,-38,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,2,2,25028167.310711637,131523905.368055,4462.0048828125,0,64500,31,19,149,-144,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,4,2,26826911.73509302,140976382.70016584,7763.6904296875,0,0,26,4,14,-16,MOD=NEO-M8U-0
| 2020-06-15 10:41:51.800,8,6,21872062.310719565,117123862.76723988,10840.2900390625,13,0,25,9,81,-261,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,18,0,18819344.42566704,98896327.76647854,9101.73828125,0,64500,41,63,171,13,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,26,0,18898137.26295038,99310408.75445652,8898.8408203125,0,64500,34,61,290,62,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,29,0,19535201.80690319,102658177.63323784,5326.7392578125,0,64500,44,50,72,14,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,21,0,20119499.457593217,105728663.27651404,9971.025390625,0,64500,45,41,204,-19,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,31,0,20837159.09731752,109499999.09936048,4757.36669921875,0,64500,44,34,224,9,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,16,0,21097805.334686965,110869713.21157265,10705.314453125,0,43460,28,26,304,-16,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,5,0,21687220.17884062,113967016.43073082,6577.5400390625,0,41600,35,20,49,-106,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,25,0,22169969.367358018,116504075.55719538,3855.35888671875,0,38920,30,18,134,-76,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,30,2,20674123.57819893,108643235.85124858,6237.8623046875,0,64500,41,66,182,-4,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,27,2,22680900.219319485,119188921.9840947,9191.369140625,0,64500,34,48,302,1,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,15,2,22865148.65306525,120157190.27693364,9807.05859375,0,64500,41,42,220,7,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,36,2,24114212.688827597,126721033.9762238,4944.07373046875,0,64500,32,28,53,-7,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,24,6,17461229.51784391,93373013.39998628,8347.9482421875,9,64500,38,75,266,-10,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,14,6,17547653.990240593,93538813.22032166,7147.5302734375,0,64500,36,71,247,43,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,23,6,18012915.8167422,96356931.90315914,5383.234375,10,64500,40,57,39,10,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,15,6,19712345.974466328,105336750.56864738,11034.166015625,7,64500,39,32,323,4,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,17,6,20420963.874969844,109276682.15112592,11191.171875,11,64500,39,23,240,-38,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,2,2,25027997.288877644,131523012.90491705,4462.98583984375,0,64500,31,19,149,-142,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,4,2,26826616.6562736,140974831.6567889,7766.03955078125,0,0,26,4,14,-29,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.200,8,6,21871657.016324896,117121691.86499886,10837.83984375,13,0,25,9,81,-256,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,18,0,18818651.4775206,98892686.73469923,9104.4228515625,0,64500,42,63,171,14,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,26,0,18897459.9152986,99306848.79543497,8901.7705078125,0,64500,34,61,290,61,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,29,0,19534796.242030147,102656046.46861172,5329.84033203125,0,64500,45,50,72,14,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,21,0,20118740.521448392,105724674.53730202,9973.7919921875,0,64500,46,41,204,-21,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,31,0,20836796.7391836,109498095.7347746,4760.3232421875,0,64500,45,34,224,10,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,16,0,21096990.25246656,110865430.7668829,10708.291015625,0,43860,28,26,304,-15,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,5,0,21686719.30960339,113964385.17380524,6580.07177734375,0,42000,35,20,49,-105,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,25,0,22169675.49260362,116502532.54727812,3858.94140625,0,39320,31,18,134,-74,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,30,2,20673648.66179804,108640740.26990286,6240.66943359375,0,64500,41,66,182,-4,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,27,2,22680200.574651983,119185245.0013721,9194.365234375,0,64500,35,48,302,0,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,15,2,22864402.263626978,120153267.18037312,9809.6630859375,0,64500,41,42,220,5,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,36,2,24113835.990977246,126719055.82112038,4947.427734375,0,64500,32,28,53,-5,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,24,6,17460605.218854055,93369673.67619991,8351.201171875,9,64500,38,75,266,-13,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,14,6,17547117.640348148,93535953.83637904,7150.560546875,0,64500,36,71,247,42,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,23,6,18012513.161692966,96354778.16429044,5386.228515625,10,64500,40,57,39,10,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,15,6,19711520.090561144,105332336.64011955,11036.716796875,7,64500,38,32,323,2,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,17,6,20420127.276630968,109272205.3008604,11193.90234375,11,64500,38,23,240,-39,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,2,2,25027657.703277875,131521227.32395276,4465.5087890625,0,64500,31,19,149,-145,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,4,2,26826026.267419364,140971729.02822265,7768.0859375,0,0,26,4,14,-38,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.600,8,6,21870847.073193617,117117354.55721082,10842.9228515625,13,0,25,9,81,-255,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,18,0,18817958.472660948,98889044.76738548,9105.833984375,0,64500,42,63,171,13,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,26,0,18896782.36260676,99303287.9032383,8903.076171875,0,64500,34,61,290,60,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,29,0,19534390.60299368,102653914.33838654,5331.2939453125,0,64500,45,50,72,13,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,21,0,20117981.074884698,105720684.8536644,9975.1748046875,0,64500,46,41,204,-19,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,31,0,20836434.48575099,109496191.4196596,4761.53369140625,0,64500,45,34,224,9,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,16,0,21096173.521200582,110861147.39777756,10709.490234375,0,44260,28,26,304,1,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,5,0,21686218.3792904,113961753.01743698,6581.00537109375,0,42400,35,20,49,-105,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,25,0,22169380.383246318,116500988.55318424,3860.97998046875,0,39720,31,18,134,-61,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,30,2,20673173.57525752,108638243.70751746,6242.27001953125,0,64500,41,66,182,-5,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,27,2,22679500.655143555,119181567.01837043,9195.8564453125,0,64500,35,48,302,0,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,15,2,22863655.337594517,120149343.08017528,9811.2509765625,0,64500,41,42,220,7,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,36,2,24113459.162250265,126717076.72477472,4948.55712890625,0,64500,32,28,53,-3,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,24,6,17459980.54904707,93366333.04387663,8352.525390625,9,64500,38,75,266,-13,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,14,6,17546581.129041184,93533093.58329582,7151.37353515625,0,64500,36,71,247,41,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,23,6,18012110.319053892,96352623.47640038,5387.41259765625,10,64500,40,57,39,10,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,15,6,19710693.79221329,105327921.74807169,11038.056640625,7,64500,38,32,323,3,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,17,6,20419290.49942736,109267727.47779465,11195.384765625,11,64500,39,23,240,-39,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,2,2,25027317.56363369,131519440.66095872,4468.2216796875,0,64500,31,19,149,-143,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,4,2,26825434.565436244,140968620.353811,7769.9208984375,0,0,27,4,14,-34,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.000,8,6,21870036.567292817,117113015.09344968,10845.9931640625,13,0,25,9,81,-250,MOD=NEO-M8U-0

使用:

df_satellite = df_gnsssatellitedata.resample("1S", label="right", closed="right").last()

将仅输出最后一行时间戳:

^{tb3}$

我需要的是所有具有与第一个csv文件中的时间戳匹配的相同最后时间戳的行:

|datetime,svId,gnssId,prMes,cpMes,doMes,freqId,locktime,cno,elev,azim,prRes,receiver
|-
| 2020-06-15 10:41:52.0,18,0,18819690.864839826,98898148.01376344,9100.7783203125,0,64500,42,63,171,12,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,26,0,18898475.83643752,99312188.42621994,8898.150390625,0,64500,34,61,290,64,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,29,0,19535404.626421425,102659242.90557194,5326.11328125,0,64500,45,50,72,12,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,21,0,20119878.873314977,105730657.34985732,9970.078125,0,64500,46,41,204,-19,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,31,0,20837340.256752983,109500950.49708273,4756.482421875,0,64500,45,34,224,8,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,16,0,21098212.356653187,110871854.1766653,10704.1064453125,0,43260,28,26,304,-23,MOD=NEO-M8U
| 2020-06-15 10:41:52.0,5,0,21687470.41932556,113968331.7959013,6576.39306640625,0,41400,36,20,49,-106,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,25,0,22170117.17070227,116504846.7321731,3855.67626953125,0,38720,30,18,134,-68,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,30,2,20674361.08438748,108644483.35774152,6237.05419921875,0,64500,41,66,183,-5,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,27,2,22681250.109272823,119190760.19749376,9190.7265625,0,64500,35,48,302,1,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,15,2,22865522.048461888,120159151.53984466,9805.8271484375,0,64500,41,42,220,4,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,36,2,24114401.037752766,126722022.71452372,4943.53564453125,0,64500,32,28,53,-8,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,24,6,17461541.697876718,93374682.96203424,8347.490234375,9,64500,38,75,266,-8,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,14,6,17547922.134648934,93540242.64641856,7146.93212890625,0,64500,36,71,247,44,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,23,6,18013117.07446595,96358008.47407912,5382.39794921875,10,64500,40,57,39,9,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,15,6,19712758.72882908,105338957.24770068,11033.0087890625,7,64500,39,32,323,5,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,17,6,20421382.08252564,109278920.26213168,11190.1328125,11,64500,38,23,240,-38,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,2,2,25028167.310711637,131523905.368055,4462.0048828125,0,64500,31,19,149,-144,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,4,2,26826911.73509302,140976382.70016584,7763.6904296875,0,0,26,4,14,-16,MOD=NEO-M8U-0
| 2020-06-15 10:41:52.0,8,6,21872062.310719565,117123862.76723988,10840.2900390625,13,0,25,9,81,-261,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,18,0,18817958.472660948,98889044.76738548,9105.833984375,0,64500,42,63,171,13,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,26,0,18896782.36260676,99303287.9032383,8903.076171875,0,64500,34,61,290,60,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,29,0,19534390.60299368,102653914.33838654,5331.2939453125,0,64500,45,50,72,13,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,21,0,20117981.074884698,105720684.8536644,9975.1748046875,0,64500,46,41,204,-19,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,31,0,20836434.48575099,109496191.4196596,4761.53369140625,0,64500,45,34,224,9,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,16,0,21096173.521200582,110861147.39777756,10709.490234375,0,44260,28,26,304,1,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,5,0,21686218.3792904,113961753.01743698,6581.00537109375,0,42400,35,20,49,-105,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,25,0,22169380.383246318,116500988.55318424,3860.97998046875,0,39720,31,18,134,-61,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,30,2,20673173.57525752,108638243.70751746,6242.27001953125,0,64500,41,66,182,-5,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,27,2,22679500.655143555,119181567.01837043,9195.8564453125,0,64500,35,48,302,0,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,15,2,22863655.337594517,120149343.08017528,9811.2509765625,0,64500,41,42,220,7,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,36,2,24113459.162250265,126717076.72477472,4948.55712890625,0,64500,32,28,53,-3,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,24,6,17459980.54904707,93366333.04387663,8352.525390625,9,64500,38,75,266,-13,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,14,6,17546581.129041184,93533093.58329582,7151.37353515625,0,64500,36,71,247,41,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,23,6,18012110.319053892,96352623.47640038,5387.41259765625,10,64500,40,57,39,10,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,15,6,19710693.79221329,105327921.74807169,11038.056640625,7,64500,38,32,323,3,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,17,6,20419290.49942736,109267727.47779465,11195.384765625,11,64500,39,23,240,-39,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,2,2,25027317.56363369,131519440.66095872,4468.2216796875,0,64500,31,19,149,-143,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,4,2,26825434.565436244,140968620.353811,7769.9208984375,0,0,27,4,14,-34,MOD=NEO-M8U-0
| 2020-06-15 10:41:53.0,8,6,21870036.567292817,117113015.09344968,10845.9931640625,13,0,25,9,81,-250,MOD=NEO-M8U-0

我期待着收到一些提示来解决这个问题


1条回答
网友
1楼 · 发布于 2024-03-29 08:20:58

基本上,我可以通过resample()和merge()实现我所需要的

  1. 我在第一个csv文件/dataframe df_gnssdata中复制了列datetime(名为datetimecopy的新列),然后在datetime上重新采样以获得df_gnss
  2. 我用copy()将df_gnss复制到一个新的df_关系中,并将除了datetime(=重采样)和datetimecopy(=原始时间戳)之外的所有列都放在那里
  3. 获取第二个csv文件/数据帧df_gnsssatellitedata,并将df_关系(在datetimecopy上)与df_gnsssatellitedata(在datetime上)how='inner'合并
  4. 删除不必要的列datetimecopy和datetime_y,重命名列datetime_x(=重采样时间),以获得所需的结果

相关问题 更多 >