遇到某些数据时出现错误，但某些数据是正确的。

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

error when encoutering some data, but some are correct

问题

您的程序在处理不同数据集时出现错误，原因是数据集中的日期和时间格式与程序的预期格式不匹配。具体来说，错误信息中提到的问题是“unsupported operand type(s) for -: 'datetime.time' and 'datetime.time'”错误，这意味着程序试图在两个时间对象之间执行减法操作，但时间对象应该是datetime.datetime而不是datetime.time。

要解决此问题，您可以采取以下步骤：

  1. 检查数据集格式：首先，确保数据集中的日期和时间列是以正确的格式存在的。日期时间格式应该是datetime.datetime，而不是datetime.time。

  2. 适应数据集：如果您的第二个数据集中的日期和时间是datetime.time格式，您需要相应地调整代码，以便能够正确处理这种情况。

  3. 处理时间差异：如果数据集中的日期和时间仅包含时间信息（datetime.time），而不包含日期信息，您需要修改代码以处理这种情况。您可能需要根据程序逻辑提供日期信息。

  4. 调试程序：通过添加打印语句或使用调试器来跟踪程序的执行流程，以查找在处理第二个数据集时出现错误的具体位置。

请注意，程序的错误可能与数据集的结构有关，因此需要适应程序以处理不同格式的数据。如果您可以提供第二个数据集的具体格式，我可以为您提供更具体的建议。

英文:

so i make a waging program using python, with that waging program I have an excel data that I need to processed.

my program as such

  1.  # Install required library
  2. !pip install xlrd
  3. import pandas as pd
  4. from datetime import time, timedelta
  5. import openpyxl
  6. import math
  7. import numpy as np
  8. # Mount google drive
  9. from google.colab import drive
  10. drive.mount('/content/drive')
  11. # Read the Excel file
  12. path = '/content/drive/MyDrive/Colab Notebooks/Book1.xlsx'
  13. df = pd.read_excel(path)
  14. # Convert the 'Tgl/Waktu' column to datetime format
  15. df['Tgl/Waktu'] = pd.to_datetime(df['Tgl/Waktu'])
  16. # Extract the date and time from the 'Tgl/Waktu' column
  17. df['Date'] = df['Tgl/Waktu'].dt.date
  18. df['Time'] = df['Tgl/Waktu'].dt.time
  19. # Group the data by employee name and date
  20. grouped_df = df.groupby(['Nama', 'Date'])
  21. # Set the overtime threshold to 16:30:00
  22. overtime_threshold = time(hour=16, minute=30)
  23. # Set the late limit
  24. late_limit = time(hour=8, minute=15)
  25. # Set holidays date
  26. holidays_date = ['2023-1-1', '2023-1-22', '2023-2-18', '2023-3-22', '2023-4-7', '2023-4-22', '2023-4-23', '2023-5-1', '2023-5-18', '2023-6-1', '2023-6-4','2023-6-29',
  27. '2023-7-19', '2023-8-17', '2023-9-28', '2023-12-25', '2023-1-23', '2023-3-23', '2023-4-21', '2023-4-24', '2023-4-25', '2023-4-26', '2023-6-2', '2023-12-26',
  28. '2023-1-8', '2023-1-15', '2023-1-29', '2023-2-5', '2023-2-12', '2023-2-19', '2023-2-26', '2023-3-5', '2023-3-12', '2023-3-19', '2023-3-26', '2023-4-2',
  29. '2023-4-9', '2023-4-16', '2023-4-23', '2023-4-30', '2023-5-7', '2023-5-14', '2023-5-21', '2023-5-28', '2023-6-11', '2023-6-18', '2023-6-25', 
  30. '2023-7-2', '2023-7-9', '2023-7-16', '2023-7-23', '2023-7-30', '2023-8-6', '2023-8-13', '2023-8-20', '2023-8-27', '2023-9-3', '2023-9-10', '2023-9-17', 
  31. '2023-9-24', '2023-10-1', '2023-10-8', '2023-10-15', '2023-10-22', '2023-10-29', '2023-11-5', '2023-11-12', '2023-11-19', '2023-11-26', '2023-12-3', '2023-12-10', 
  32. '2023-12-17', '2023-12-24', '2023-12-31','2022-12-20']
  33. # Iterate over the grouped data
  34. for (name, date), group in grouped_df:
  35. # Calculate the total work hours and overtime hours for each employee on each day
  36. start_time = group['Time'].min()
  37. end_time = group['Time'].max()
  38. total_hours = (timedelta(hours=end_time.hour, minutes=end_time.minute, seconds=end_time.second) - 
  39. timedelta(hours=start_time.hour, minutes=start_time.minute, seconds=start_time.second)).total_seconds() / 3600
  40. if total_hours > 8:
  41. hours_worked = 8
  42. if end_time > overtime_threshold:
  43. overtime_hours += (end_time - overtime_threshold).total_seconds() / 3600
  44. elif total_hours < 8:
  45. if start_time > late_limit:
  46. hours_worked = 5
  47. else:
  48. hours_worked = total_hours
  49. hours_worked = math.floor(total_hours)  # Round down the hours_worked value
  50. overtime_hours = 0
  51. if end_time > overtime_threshold:
  52. overtime_hours += (end_time - overtime_threshold).total_seconds() / 3600
  53. # Calculate the payment for each employee on each day
  54. if hours_worked == 8:
  55. if overtime_hours > 0:
  56. if name == 'Alif':
  57. payment_each_date = 60000 + overtime_hours * 10000
  58. elif name == 'budi':
  59. payment_each_date = 70000 + overtime_hours * 10000
  60. elif name == 'adi':
  61. payment_each_date = 60000 + overtime_hours * 10000
  62. elif name == 'supriyanto':
  63. payment_each_date = 70000 + overtime_hours * 10000
  64. elif name == 'Edi':
  65. payment_each_date = 60000 + overtime_hours * 10000
  66. elif name == 'Tri Gunawan':
  67. payment_each_date = 60000 + overtime_hours * 10000
  68. elif name == 'Bayu Aji N':
  69. payment_each_date = 60000 + overtime_hours * 10000
  70. elif name == 'dani':
  71. payment_each_date = 70000 + overtime_hours * 10000
  72. else :
  73. payment_each_date = "Name Not Listed"
  74. else:
  75. if name == 'Alif':
  76. payment_each_date = 60000
  77. elif name == 'budi':
  78. payment_each_date = 70000
  79. elif name == 'adi':
  80. payment_each_date = 60000
  81. elif name == 'supriyanto':
  82. payment_each_date = 70000
  83. elif name == 'Edi':
  84. payment_each_date = 60000
  85. elif name == 'Tri Gunawan':
  86. payment_each_date = 60000
  87. elif name == 'Bayu Aji N':
  88. payment_each_date = 60000
  89. elif name == 'dani':
  90. payment_each_date = 70000
  91. else :
  92. payment_each_date = "Name Not Listed"
  93. else:
  94. if start_time > late_limit:
  95. if name == 'Alif':
  96. payment_each_date = 60000/2
  97. elif name == 'budi':
  98. payment_each_date = 70000/2
  99. elif name == 'adi':
  100. payment_each_date = 60000/2
  101. elif name == 'supriyanto':
  102. payment_each_date = 70000/2
  103. elif name == 'Edi':
  104. payment_each_date = 60000/2
  105. elif name == 'Tri Gunawan':
  106. payment_each_date = 60000/2
  107. elif name == 'Bayu Aji N':
  108. payment_each_date = 60000/2
  109. elif name == 'dani':
  110. payment_each_date = 70000/2
  111. else :
  112. payment_each_date = "Name Not Listed"  
  113. else:
  114. if name == 'Alif':
  115. payment_each_date = 60000
  116. elif name == 'budi':
  117. payment_each_date = 70000
  118. elif name == 'adi':
  119. payment_each_date = 60000
  120. elif name == 'supriyanto':
  121. payment_each_date = 70000
  122. elif name == 'Edi':
  123. payment_each_date = 60000
  124. elif name == 'Tri Gunawan':
  125. payment_each_date = 60000
  126. elif name == 'Bayu Aji N':
  127. payment_each_date = 60000
  128. elif name == 'dani':
  129. payment_each_date = 70000
  130. else :
  131. payment_each_date = "Name Not Listed"
  132. # Add the total work hours, overtime hours, and payment as new columns to the dataframe
  133. df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Hours Worked'] = hours_worked
  134. df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Overtime Hours'] = overtime_hours
  135. df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Payment Each Date'] = payment_each_date
  136. holiday_status = df['Tgl/Waktu'].dt.normalize().isin(pd.DatetimeIndex(holidays_date))
  137. # make new column for holiday with boolean value then merge with the original dataframe and place it in between date and time using merge
  138. df = pd.merge(df, holiday_status.to_frame('Holiday'), left_index=True, right_index=True)
  139. # make the payment each date column to 1.5 times and add 5000 if the holiday column is true
  140. df.loc[df['Holiday'] == True, 'Payment Each Date'] = df['Payment Each Date'] * 1.5 + 5000
  141. # Calculate the total payment from payment each date and insert it into new column named Total Payment
  142. df_total = df.groupby(['Nama', 'Date'])['Payment Each Date'].max().groupby('Nama').sum().rename('Total Payment')
  143. df = df.merge(df_total, how='left', on='Nama')
  144. # Print the resulting dataframe
  145. print(df)
  146. # write DataFrame to excel
  147. df.to_excel(excel_writer=r'/content/drive/MyDrive/Colab Notebooks/test.xlsx')

with this data when I run the program it can be processed and got correct result

but when I use this data I get error unsupported operand type(s) for -: 'datetime.time' and 'datetime.time'

like below
遇到某些数据时出现错误，但某些数据是正确的。

so how do I fix it? is it the error on the data? or the program?

答案1

得分: 0

将程序能够运行的答案是将end_time和overtime_threshold转换为时间间隔（timedelta）格式，如下所示：

  1. overtime_hours += (timedelta(hours=end_time.hour, minutes=end_time.minute, seconds=end_time.second) - timedelta(hours=overtime_threshold.hour, minutes=overtime_threshold.minute)).total_seconds() / 3600
英文:

The Answer to make the program can run is to convert the end_time and overtime_treshold into timedelta format, such as

  1. overtime_hours += (timedelta(hours=end_time.hour, minutes=end_time.minute, seconds=end_time.second) - timedelta(hours=overtime_threshold.hour, minutes=overtime_threshold.minute)).total_seconds() / 3600

huangapple
  • 本文由 发表于 2023年1月9日 15:55:45
  • 转载请务必保留本文链接：https://go.coder-hub.com/75054437.html
  • data-processing
  • dataframe
  • excel
  • pandas
  • python
匿名

发表评论

匿名网友

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

确定