Python calculates office hours: CSV data processing and time difference calculation

This article aims to provide a Python script for reading data from a CSV file and calculating the office hours corresponding to each ID within a specific month (such as February). The script does not rely on the Pandas library, but uses the csv and datetime modules for data processing and time calculation. The article will explain the code logic in detail and provide considerations to help readers understand and apply the method.

Data preparation

First, we need to prepare a CSV file containing ID, type (in/out) and time information. Here is the contents of an example CSV file (data.csv):

 id, type, time
1,out,2023-01-01T08:01:28.000Z
1,in,2023-02-01T08:01:28.000Z
2,in,2023-02-01T09:04:16.000Z
2,out,2023-02-01T12:01:28.000Z
1,out,2023-02-01T13:34:15.000Z

Python code implementation

The following Python code shows how to read CSV files, filter data for February, and calculate the office hours for each ID.

 import datetime
import csv

date_format = '%Y-%m-%dT%H:%M:%S.%fZ'
total_time = {}
feb = datetime.datetime.strptime('2023-02', '%Y-%m').month

file_path = 'data.csv'
with open(file_path, 'r') as f:
    # Create a CSV reader
    csv_file = csv.DictReader(f)
    list_of_dict = list(csv_file)

for d in list_of_dict:
  w_id = d['id']
  dt = datetime.datetime.strptime(d['time'], date_format).date()
  d_time = datetime.datetime.strptime(d['time'], date_format)

  if d_time.month == feb:

    if not total_time.get(w_id):
      total_time[w_id] = {"date": None,"last_in": None, "last_out": None, "work_hour_s": 0. , 'work_hour_string': '' }

    update_time = total_time[w_id]
    update_time['date'] = dt
    if d['type'] == 'in':
      update_time['last_in'] = d_time
    if d['type'] == 'out':
      update_time['last_out'] = d_time

    if update_time['last_out'] and update_time['last_in']:
      if update_time['last_out'] > update_time['last_in']:
        work_hour_s = update_time['last_out'] - update_time['last_in']
        update_time['work_hour_s'] = work_hour_s.seconds

        up_time = int(update_time['work_hour_s'])
        hours, remainder = divmod(up_time, 3600)
        minutes, seconds = divmod(remainder, 60)

        formatted_duration = f"{hours:02d}:{minutes:02d}:{seconds:02d}"
        update_time['work_hour_string'] = formatted_duration

print(total_time)

Code explanation:

1. Import the necessary modules: Import datetime is used to process date and time, and csv is used to read CSV files.
2. Define date format: date_format = '%Y-%m-%dT%H:%M:%S.%fZ' Defines the format of timestamps in CSV files.
3. Initialize the data structure: total_time = {} is used to store the office duration information corresponding to each ID.
4. Specify the target month: feb = datetime.datetime.strptime('2023-02', '%Y-%m').month Gets the month value of February.
5. Read CSV file: Use csv.DictReader to read CSV file and store each line of data as a dictionary.
6. Traversal data: Iterate through every line of data in a CSV file.
7. Filter the target month data: Check whether the month of the current row data is February.
8. Initialize ID data: If the current ID does not exist in the total_time dictionary, initialize a dictionary containing keys such as date, last_in, last_out, work_hour_s, work_hour_string, etc.
9. Update the last entry/leave time: Update the last_in or last_out time according to the value of the type field.
10. Calculate office hours: If last_in and last_out are both present, calculate the time difference between the two and accumulate the results into work_hour_s.
11. Format output: Convert the total number of seconds to a string in HH:MM:SS format and store it in work_hour_string.
12. Output result: Print the total_time dictionary, which contains information on the office duration of each ID in February.

Output example:

 {'1': {'date': datetime.date(2023, 2, 1),
  'last_in': datetime.datetime(2023, 2, 1, 8, 1, 28),
  'last_out': datetime.datetime(2023, 2, 1, 13, 34, 15),
  'work_hour_s': 19967.0,
  'work_hour_string': '05:32:47'},
 '2': {'date': datetime.date(2023, 2, 1),
  'last_in': datetime.datetime(2023, 2, 1, 9, 4, 16),
  'last_out': datetime.datetime(2023, 2, 1, 12, 1, 28),
  'work_hour_s': 10632.0,
  'work_hour_string': '02:57:12'}}

Things to note

• Data order: The code assumes that the in and out records of the same ID appear in pairs, and the out records are later than the in records. If the data is not ordered, it needs to be sorted first.
• Data Integrity: The code does not handle data missing, such as only in records and no out records, or vice versa. In practical applications, it needs to be handled according to specific circumstances. For example, records with only in or out can be ignored, or missing in or out times can be filled with default values.
• Date format: Ensure that the date format in the CSV file is consistent with the format defined by the date_format variable. If it is inconsistent, the date_format variable needs to be modified.
• Time zone issue: The code does not consider the time zone issue. If the timestamp in the CSV file contains time zone information, the timestamp needs to be converted to a unified time zone before the calculation is performed.
• Error handling: In actual applications, an error handling mechanism should be added, such as using the try-except block to catch possible exceptions, such as file not exist, date format errors, etc.

Summarize

This article provides a sample code for calculating office hours in CSV files using Python, and explains the code logic and considerations in detail. This code does not rely on the Pandas library, but uses the csv and datetime modules for data processing, which can be used as the basis for processing CSV data. In actual applications, it is necessary to modify and improve according to specific circumstances, such as handling data missing, time zone problems and error handling.

The above is the detailed content of Python calculates office hours: CSV data processing and time difference calculation. For more information, please follow other related articles on the PHP Chinese website!