获取每30分钟的行数值？

huangapple
go评论126阅读模式
英文:

How to get the row values for every 30th minute?

问题

我有一个数据集，它是一个数据框：

  1. 	item	id	_value	agentstatus	type	agenttimestamp	Unit	Calc1	Calc2
  2. 0	FCX.FCN2.PM01001_01_SUM.SUM	1	3	Good	Double	2022-01-01 09:00:00+00:00	kWh	Diff	30min
  3. 1	FCX.FCN2.PM01002_01_SUM.SUM	2	3	Good	Double	2022-01-01 09:00:00+00:00	kWh	Diff	30min
  4. 25	FCX.FCN2.PM01001_01_SUM.SUM	26	3	Good	Double	2022-01-01 09:10:00+00:00	kWh	Diff	30min
  5. 26	FCX.FCN2.PM01002_01_SUM.SUM	27	3	Good	Double	2022-01-01 09:10:00+00:00	kWh	Diff	30min
  6. 50	FCX.FCN2.PM01001_01_SUM.SUM	51	3	Good	Double	2022-01-01 09:20:00+00:00	kWh	Diff	30min
  7. 51	FCX.FCN2.PM01002_01_SUM.SUM	52	3	Good	Double	2022-01-01 09:20:00+00:00	kWh	Diff	30min
  8. 75	FCX.FCN2.PM01001_01_SUM.SUM	76	3	Good	Double	2022-01-01 09:30:00+00:00	kWh	Diff	30min
  9. 76	FCX.FCN2.PM01002_01_SUM.SUM	77	3	Good	Double	2022-01-01 09:30:00+00:00	kWh	Diff	30min
  10. 100	FCX.FCN2.PM01001_01_SUM.SUM	101	3	Good	Double	2022-01-01 09:40:00+00:00	kWh	Diff	30min
  11. 101	FCX.FCN2.PM01002_01_SUM.SUM	102	3	Good	Double	2022-01-01 09:40:00+00:00	kWh	Diff	30min
  12. 125	FCX.FCN2.PM01001_01_SUM.SUM	126	3	Good	Double	2022-01-01 09:50:00+00:00	kWh	Diff	30min
  13. 126	FCX.FCN2.PM01002_01_SUM.SUM	127	3	Good	Double	2022-01-01 09:50:00+00:00	kWh	Diff	30min
  14. 150	FCX.FCN2.PM01001_01_SUM.SUM	151	3	Good	Double	2022-01-01 10:00:00+00:00	kWh	Diff	30min
  15. 151	FCX.FCN2.PM01002_01_SUM.SUM	152	3	Good	Double	2022-01-01 10:00:00+00:00	kWh	Diff	30min
  16. 175	FCX.FCN2.PM01001_01_SUM.SUM	176	3	Good	Double	2022-01-01 10:10:00+00:00	kWh	Diff	30min
  17. 176	FCX.FCN2.PM01002_01_SUM.SUM	177	3	Good	Double	2022-01-01 10:10:00+00:00	kWh	Diff	30min
  18. 200	FCX.FCN2.PM01001_01_SUM.SUM	201	3	Good	Double	2022-01-01 10:20:00+00:00	kWh	Diff	30min
  19. 201	FCX.FCN2.PM01002_01_SUM.SUM	202	3	Good	Double	2022-01-01 10:20:00+00:00	kWh	Diff	30min
  20. 225	FCX.FCN2.PM01001_01_SUM.SUM	226	3	Good	Double	2022-01-01 10:30:00+00:00	kWh	Diff	30min
  21. 226	FCX.FCN2.PM01002_01_SUM.SUM	227	3	Good	Double	2022-01-01 10:30:00+00:00	kWh	Diff	30min
  22. 250	FCX.FCN2.PM01001_01_SUM.SUM	251	3	Good	Double	2022-01-01 10:40:00+00:00	kWh	Diff	30min
  23. 251	FCX.FCN2.PM01002_01_SUM.SUM	252	3	Good	Double	2022-01-01 10:40:00+00:00	kWh	Diff	30min
  24. 275	FCX.FCN2.PM01001_01_SUM.SUM	276	3	Good	Double	2022-01-01 10:50:00+00:00	kWh	Diff	30min
  25. 276	FCX.FCN2.PM01002_01_SUM.SUM	277	3	Good	Double	2022-01-01 10:50:00+00:00	kWh	Diff	30min
  26. 300	FCX.FCN2.PM01001_01_SUM.SUM	301	3	Good	Double	2022-01-01 11:00:00+00:00	kWh	Diff	30min
  27. 301	FCX.FCN2.PM01002_01_SUM.SUM	302	3	Good	Double	2022-01-01 11:00:00+00:00	kWh	Diff	30min
  28. 325	FCX.FCN2.PM01001_01_SUM.SUM	326	3	Good	Double	2022-01-01 11:10:00+00:00	kWh	Diff	30min
  29. 326	FCX.FCN2.PM01002_01_SUM.SUM	327	3	Good	Double	2022-01-01 11:10:00+00:00	kWh	Diff	30min
  30. 350	FCX.FCN2.PM01001_01_SUM.SUM	351	3	Good	Double	2022-01-01 11:20:00+00:00	kWh	Diff	30min
  31. 351	FCX.FCN2.PM01002_01_SUM.SUM	352	3	Good	Double	2022-01-01 11:20:00+00:00	kWh	Diff	30min
英文:

I have a dataset, it's a dataframe:

  1. 	item	id	_value	agentstatus	type	agenttimestamp	Unit	Calc1	Calc2
  2. 0	FCX.FCN2.PM01001_01_SUM.SUM	1	3	Good	Double	2022-01-01 09:00:00+00:00	kWh	Diff	30min
  3. 1	FCX.FCN2.PM01002_01_SUM.SUM	2	3	Good	Double	2022-01-01 09:00:00+00:00	kWh	Diff	30min
  4. 25	FCX.FCN2.PM01001_01_SUM.SUM	26	3	Good	Double	2022-01-01 09:10:00+00:00	kWh	Diff	30min
  5. 26	FCX.FCN2.PM01002_01_SUM.SUM	27	3	Good	Double	2022-01-01 09:10:00+00:00	kWh	Diff	30min
  6. 50	FCX.FCN2.PM01001_01_SUM.SUM	51	3	Good	Double	2022-01-01 09:20:00+00:00	kWh	Diff	30min
  7. 51	FCX.FCN2.PM01002_01_SUM.SUM	52	3	Good	Double	2022-01-01 09:20:00+00:00	kWh	Diff	30min
  8. 75	FCX.FCN2.PM01001_01_SUM.SUM	76	3	Good	Double	2022-01-01 09:30:00+00:00	kWh	Diff	30min
  9. 76	FCX.FCN2.PM01002_01_SUM.SUM	77	3	Good	Double	2022-01-01 09:30:00+00:00	kWh	Diff	30min
  10. 100	FCX.FCN2.PM01001_01_SUM.SUM	101	3	Good	Double	2022-01-01 09:40:00+00:00	kWh	Diff	30min
  11. 101	FCX.FCN2.PM01002_01_SUM.SUM	102	3	Good	Double	2022-01-01 09:40:00+00:00	kWh	Diff	30min
  12. 125	FCX.FCN2.PM01001_01_SUM.SUM	126	3	Good	Double	2022-01-01 09:50:00+00:00	kWh	Diff	30min
  13. 126	FCX.FCN2.PM01002_01_SUM.SUM	127	3	Good	Double	2022-01-01 09:50:00+00:00	kWh	Diff	30min
  14. 150	FCX.FCN2.PM01001_01_SUM.SUM	151	3	Good	Double	2022-01-01 10:00:00+00:00	kWh	Diff	30min
  15. 151	FCX.FCN2.PM01002_01_SUM.SUM	152	3	Good	Double	2022-01-01 10:00:00+00:00	kWh	Diff	30min
  16. 175	FCX.FCN2.PM01001_01_SUM.SUM	176	3	Good	Double	2022-01-01 10:10:00+00:00	kWh	Diff	30min
  17. 176	FCX.FCN2.PM01002_01_SUM.SUM	177	3	Good	Double	2022-01-01 10:10:00+00:00	kWh	Diff	30min
  18. 200	FCX.FCN2.PM01001_01_SUM.SUM	201	3	Good	Double	2022-01-01 10:20:00+00:00	kWh	Diff	30min
  19. 201	FCX.FCN2.PM01002_01_SUM.SUM	202	3	Good	Double	2022-01-01 10:20:00+00:00	kWh	Diff	30min
  20. 225	FCX.FCN2.PM01001_01_SUM.SUM	226	3	Good	Double	2022-01-01 10:30:00+00:00	kWh	Diff	30min
  21. 226	FCX.FCN2.PM01002_01_SUM.SUM	227	3	Good	Double	2022-01-01 10:30:00+00:00	kWh	Diff	30min
  22. 250	FCX.FCN2.PM01001_01_SUM.SUM	251	3	Good	Double	2022-01-01 10:40:00+00:00	kWh	Diff	30min
  23. 251	FCX.FCN2.PM01002_01_SUM.SUM	252	3	Good	Double	2022-01-01 10:40:00+00:00	kWh	Diff	30min
  24. 275	FCX.FCN2.PM01001_01_SUM.SUM	276	3	Good	Double	2022-01-01 10:50:00+00:00	kWh	Diff	30min
  25. 276	FCX.FCN2.PM01002_01_SUM.SUM	277	3	Good	Double	2022-01-01 10:50:00+00:00	kWh	Diff	30min
  26. 300	FCX.FCN2.PM01001_01_SUM.SUM	301	3	Good	Double	2022-01-01 11:00:00+00:00	kWh	Diff	30min
  27. 301	FCX.FCN2.PM01002_01_SUM.SUM	302	3	Good	Double	2022-01-01 11:00:00+00:00	kWh	Diff	30min
  28. 325	FCX.FCN2.PM01001_01_SUM.SUM	326	3	Good	Double	2022-01-01 11:10:00+00:00	kWh	Diff	30min
  29. 326	FCX.FCN2.PM01002_01_SUM.SUM	327	3	Good	Double	2022-01-01 11:10:00+00:00	kWh	Diff	30min
  30. 350	FCX.FCN2.PM01001_01_SUM.SUM	351	3	Good	Double	2022-01-01 11:20:00+00:00	kWh	Diff	30min
  31. 351	FCX.FCN2.PM01002_01_SUM.SUM	352	3	Good	Double	2022-01-01 11:20:00+00:00	kWh	Diff	30min
  32. 375	FCX.FCN2.PM01001_01_SUM.SUM	376	3	Good	Double	2022-01-01 11:30:00+00:00	kWh	Diff	30min
  33. 376	FCX.FCN2.PM01002_01_SUM.SUM	377	3	Good	Double	2022-01-01 11:30:00+00:00	kWh	Diff	30min
  34. 400	FCX.FCN2.PM01001_01_SUM.SUM	401	3	Good	Double	2022-01-01 11:40:00+00:00	kWh	Diff	30min
  35. 401	FCX.FCN2.PM01002_01_SUM.SUM	402	3	Good	Double	2022-01-01 11:40:00+00:00	kWh	Diff	30min
  36. 425	FCX.FCN2.PM01001_01_SUM.SUM	426	3	Good	Double	2022-01-01 11:50:00+00:00	kWh	Diff	30min
  37. 426	FCX.FCN2.PM01002_01_SUM.SUM	427	3	Good	Double	2022-01-01 11:50:00+00:00	kWh	Diff	30min
  38. 450	FCX.FCN2.PM01001_01_SUM.SUM	451	3	Good	Double	2022-01-01 12:00:00+00:00	kWh	Diff	30min
  39. 451	FCX.FCN2.PM01002_01_SUM.SUM	452	3	Good	Double	2022-01-01 12:00:00+00:00	kWh	Diff	30min
  40. 475	FCX.FCN2.PM01001_01_SUM.SUM	476	3	Good	Double	2022-01-01 12:10:00+00:00	kWh	Diff	30min
  41. 476	FCX.FCN2.PM01002_01_SUM.SUM	477	3	Good	Double	2022-01-01 12:10:00+00:00	kWh	Diff	30min
  42. 500	FCX.FCN2.PM01001_01_SUM.SUM	501	3	Good	Double	2022-01-01 12:20:00+00:00	kWh	Diff	30min
  43. 501	FCX.FCN2.PM01002_01_SUM.SUM	502	3	Good	Double	2022-01-01 12:20:00+00:00	kWh	Diff	30min
  44. 525	FCX.FCN2.PM01001_01_SUM.SUM	526	3	Good	Double	2022-01-01 12:30:00+00:00	kWh	Diff	30min
  45. 526	FCX.FCN2.PM01002_01_SUM.SUM	527	3	Good	Double	2022-01-01 12:30:00+00:00	kWh	Diff	30min
  46. 550	FCX.FCN2.PM01001_01_SUM.SUM	551	3	Good	Double	2022-01-01 12:40:00+00:00	kWh	Diff	30min
  47. 551	FCX.FCN2.PM01002_01_SUM.SUM	552	3	Good	Double	2022-01-01 12:40:00+00:00	kWh	Diff	30min
  48. 575	FCX.FCN2.PM01001_01_SUM.SUM	576	3	Good	Double	2022-01-01 12:50:00+00:00	kWh	Diff	30min
  49. 576	FCX.FCN2.PM01002_01_SUM.SUM	577	3	Good	Double	2022-01-01 12:50:00+00:00	kWh	Diff	30min
  50. 600	FCX.FCN2.PM01001_01_SUM.SUM	601	3	Good	Double	2022-01-01 13:00:00+00:00	kWh	Diff	30min
  51. 601	FCX.FCN2.PM01002_01_SUM.SUM	602	3	Good	Double	2022-01-01 13:00:00+00:00	kWh	Diff	30min

Expected output

  1. 	item	id	_value	agentstatus	type	agenttimestamp	Unit	Calc1	Calc2
  2. 75	FCX.FCN2.PM01001_01_SUM.SUM	76	3	Good	Double	2022-01-01 09:30:00+00:00	kWh	Diff	30min
  3. 76	FCX.FCN2.PM01002_01_SUM.SUM	77	3	Good	Double	2022-01-01 09:30:00+00:00	kWh	Diff	30min
  4. 150	FCX.FCN2.PM01001_01_SUM.SUM	151	3	Good	Double	2022-01-01 10:00:00+00:00	kWh	Diff	30min
  5. 151	FCX.FCN2.PM01002_01_SUM.SUM	152	3	Good	Double	2022-01-01 10:00:00+00:00	kWh	Diff	30min
  6. 225	FCX.FCN2.PM01001_01_SUM.SUM	226	3	Good	Double	2022-01-01 10:30:00+00:00	kWh	Diff	30min
  7. 226	FCX.FCN2.PM01002_01_SUM.SUM	227	3	Good	Double	2022-01-01 10:30:00+00:00	kWh	Diff	30min
  8. 300	FCX.FCN2.PM01001_01_SUM.SUM	301	3	Good	Double	2022-01-01 11:00:00+00:00	kWh	Diff	30min
  9. 301	FCX.FCN2.PM01002_01_SUM.SUM	302	3	Good	Double	2022-01-01 11:00:00+00:00	kWh	Diff	30min
  10. 375	FCX.FCN2.PM01001_01_SUM.SUM	376	3	Good	Double	2022-01-01 11:30:00+00:00	kWh	Diff	30min
  11. 376	FCX.FCN2.PM01002_01_SUM.SUM	377	3	Good	Double	2022-01-01 11:30:00+00:00	kWh	Diff	30min
  12. 450	FCX.FCN2.PM01001_01_SUM.SUM	451	3	Good	Double	2022-01-01 12:00:00+00:00	kWh	Diff	30min
  13. 451	FCX.FCN2.PM01002_01_SUM.SUM	452	3	Good	Double	2022-01-01 12:00:00+00:00	kWh	Diff	30min
  14. 525	FCX.FCN2.PM01001_01_SUM.SUM	526	3	Good	Double	2022-01-01 12:30:00+00:00	kWh	Diff	30min
  15. 526	FCX.FCN2.PM01002_01_SUM.SUM	527	3	Good	Double	2022-01-01 12:30:00+00:00	kWh	Diff	30min
  16. 600	FCX.FCN2.PM01001_01_SUM.SUM	601	3	Good	Double	2022-01-01 13:00:00+00:00	kWh	Diff	30min
  17. 601	FCX.FCN2.PM01002_01_SUM.SUM	602	3	Good	Double	2022-01-01 13:00:00+00:00	kWh	Diff	30min

What I tried and the actual output:

I tried with df.resample('30T', on='agenttimestamp') but it isn't working or the output isn't giving the results as expected, the output is same as the input when I apply resample.

Please let me know how to do this ?

答案1

得分: 1

关于 df[df['agenttimestamp'].dt.minute % 30 == 0] 的部分：

  1. df[df['agenttimestamp'].dt.minute % 30 == 0]

更新：要删除第一个时间戳，假设第一个时间戳出现两次，您可以使用 iloc：

  1. df[df['agenttimestamp'].dt.minute % 30 == 0].iloc[2:]
英文:

What about df[df['agenttimestamp'].dt.minute % 30 == 0]?

  1. from io import StringIO
  2. import pandas as pd
  4. df = pd.read_csv(StringIO(
  5. """agenttimestamp
  6. '2022-01-01 09:00:00+00:00'
  7. '2022-01-01 09:00:00+00:00'
  8. '2022-01-01 09:10:00+00:00'
  9. '2022-01-01 09:10:00+00:00'
  10. '2022-01-01 09:20:00+00:00'
  11. '2022-01-01 09:20:00+00:00'
  12. '2022-01-01 09:30:00+00:00'
  13. '2022-01-01 09:30:00+00:00'
  14. '2022-01-01 09:40:00+00:00'
  15. '2022-01-01 09:40:00+00:00'
  16. '2022-01-01 09:50:00+00:00'
  17. '2022-01-01 09:50:00+00:00'
  18. '2022-01-01 10:00:00+00:00'
  19. '2022-01-01 10:00:00+00:00'
  20. '2022-01-01 10:10:00+00:00'
  21. '2022-01-01 10:10:00+00:00'
  22. '2022-01-01 10:20:00+00:00'
  23. '2022-01-01 10:20:00+00:00'
  24. '2022-01-01 10:30:00+00:00'
  25. '2022-01-01 10:30:00+00:00'
  26. '2022-01-01 10:40:00+00:00'
  27. '2022-01-01 10:40:00+00:00'
  28. '2022-01-01 10:50:00+00:00'
  29. '2022-01-01 10:50:00+00:00'
  30. '2022-01-01 11:00:00+00:00'
  31. '2022-01-01 11:00:00+00:00'
  32. '2022-01-01 11:10:00+00:00'
  33. '2022-01-01 11:10:00+00:00'
  34. '2022-01-01 11:20:00+00:00'
  35. '2022-01-01 11:20:00+00:00'
  36. '2022-01-01 11:30:00+00:00'
  37. '2022-01-01 11:30:00+00:00'
  38. '2022-01-01 11:40:00+00:00'
  39. '2022-01-01 11:40:00+00:00'
  40. '2022-01-01 11:50:00+00:00'
  41. '2022-01-01 11:50:00+00:00'
  42. '2022-01-01 12:00:00+00:00'
  43. '2022-01-01 12:00:00+00:00'
  44. '2022-01-01 12:10:00+00:00'
  45. '2022-01-01 12:10:00+00:00'
  46. '2022-01-01 12:20:00+00:00'
  47. '2022-01-01 12:20:00+00:00'
  48. '2022-01-01 12:30:00+00:00'
  49. '2022-01-01 12:30:00+00:00'
  50. '2022-01-01 12:40:00+00:00'
  51. '2022-01-01 12:40:00+00:00'
  52. '2022-01-01 12:50:00+00:00'
  53. '2022-01-01 12:50:00+00:00'
  54. '2022-01-01 13:00:00+00:00'
  55. '2022-01-01 13:00:00+00:00'"""), parse_dates=['agenttimestamp'])
  57. df[df['agenttimestamp'].dt.minute % 30 == 0]
  59. # Output:
  60. 0  2022-01-01 09:00:00+00:00
  61. 1  2022-01-01 09:00:00+00:00
  62. 6  2022-01-01 09:30:00+00:00
  63. 7  2022-01-01 09:30:00+00:00
  64. 12 2022-01-01 10:00:00+00:00
  65. 13 2022-01-01 10:00:00+00:00
  66. 18 2022-01-01 10:30:00+00:00
  67. 19 2022-01-01 10:30:00+00:00
  68. 24 2022-01-01 11:00:00+00:00
  69. 25 2022-01-01 11:00:00+00:00
  70. 30 2022-01-01 11:30:00+00:00
  71. 31 2022-01-01 11:30:00+00:00
  72. 36 2022-01-01 12:00:00+00:00
  73. 37 2022-01-01 12:00:00+00:00
  74. 42 2022-01-01 12:30:00+00:00
  75. 43 2022-01-01 12:30:00+00:00
  76. 48 2022-01-01 13:00:00+00:00
  77. 49 2022-01-01 13:00:00+00:00

Update: to remove the first timestamp, assuming the first timestamp appears twice, you can use iloc:

  1. df[df['agenttimestamp'].dt.minute % 30 == 0].iloc[2:]

huangapple
  • 本文由 发表于 2023年8月10日 20:53:35
  • 转载请务必保留本文链接：https://go.coder-hub.com/76875913.html
  • pandas
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定