{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "5dbfa7da-a2cc-4220-a0ef-ba521bd84905", "metadata": {}, "outputs": [], "source": [ "import os\n", "import numpy as np\n", "import pandas as pd\n", "import datetime" ] }, { "cell_type": "code", "execution_count": 2, "id": "0aa63889-45cb-4145-bd6c-0077b1af008a", "metadata": {}, "outputs": [], "source": [ "from openpyxl import load_workbook" ] }, { "cell_type": "code", "execution_count": 3, "id": "439140e3-828b-4ddb-a9a4-66ee78f993ef", "metadata": {}, "outputs": [], "source": [ "path_to_sheet = r\"C:\\Users\\a.jurcenko\\Desktop\\Ralf Fischer\\Stempelzeiten Erding Oktober 2023.xlsx\"" ] }, { "cell_type": "code", "execution_count": 4, "id": "1787f5f5-ee30-4e41-b8c0-8c6209ae34ad", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "816\n" ] } ], "source": [ "wb = load_workbook(filename=path_to_sheet)\n", "ws = wb.active\n", "xl = pd.ExcelFile(path_to_sheet)\n", "nrows = xl.book.active.max_row\n", "print(nrows)" ] }, { "cell_type": "code", "execution_count": 5, "id": "d0f69c9c-e647-4d6f-abd4-e75d021014db", "metadata": {}, "outputs": [], "source": [ "def parse_time_string(time_string):\n", " come = time_string.endswith('K')\n", " time_string = time_string.replace('*', '').strip()[:5]\n", " return time_string, come" ] }, { "cell_type": "code", "execution_count": 16, "id": "7bb2e3ea-8a0a-4c68-92f3-151bdee96db9", "metadata": {}, "outputs": [], "source": [ "def parse_info(first_row, last_row, info):\n", " df_times = pd.read_excel(path_to_sheet, skiprows=first_row, skipfooter=nrows-last_row-1)\n", " user = info.split(':')\n", " user_id = user[1].strip()[:4]\n", " user_surname, user_name = user[0].split(',')\n", " user_name = user_name.split('-')[0].strip()\n", " user = user_surname[:8]\n", " # print(user_name)\n", " times = list()\n", " for index, row in df_times.iterrows():\n", " date = row['Datum']\n", " direction = 'Hinaus'\n", " for r in (row['Buchungen'], row['Unnamed: 5']):\n", " if str(r) != 'nan':\n", " if r.find('\\n') > -1:\n", " for s in r.split('\\n'):\n", " tm, come = parse_time_string(s)\n", " if come:\n", " direction = 'Herein'\n", " else:\n", " direction = 'Hinaus'\n", " times.append({'Benutzername': user, 'Vorname': user_name, 'Nachname': user_surname, 'Abteilung': 'Erding', \\\n", " 'Person': user_id, 'Wann': date + ' ' + tm + ':00', 'Gerät': 'Erding', 'Hinein/Hinaus': direction, 'Überprüfung': 'RFID'})\n", " \n", " else:\n", " tm, come = parse_time_string(r)\n", " if come:\n", " direction = 'Herein'\n", " else:\n", " direction = 'Hinaus'\n", " \n", " times.append({'Benutzername': user, 'Vorname': user_name, 'Nachname': user_surname, 'Abteilung': 'Erding', \\\n", " 'Person': user_id, 'Wann': date + ' ' + tm + ':00' , 'Gerät': 'Erding', 'Hinein/Hinaus': direction, 'Überprüfung': 'RFID'})\n", " # print(times)\n", " return times\n" ] }, { "cell_type": "code", "execution_count": 17, "id": "721ebf17-de2b-419f-a16d-997e8614a04d", "metadata": {}, "outputs": [], "source": [ "# info = \"Fischer, Achraf - P1: 1508 - Ausweis: 159 - VWG: Erding\"\n", "# first_cell = 273\n", "# last_cell = 305\n", "# time_table = parse_info(first_cell, last_cell, info)\n", "# print(time_table)" ] }, { "cell_type": "code", "execution_count": 18, "id": "8f675a0d-603f-4786-a89d-7e9d72213568", "metadata": {}, "outputs": [], "source": [ "rows = list(ws.rows)\n", "\n", "all_df = list()\n", "for row in rows:\n", " for cell in row: # Zellen\n", " if cell.value is None or type(cell.value) == float:\n", " continue\n", " elif cell.value.find('Ausweis') > -1:\n", " first_cell = cell.row\n", " info = cell.value\n", " elif cell.value.find('gesamt') > -1:\n", " last_cell = cell.row\n", " time_table = parse_info(first_cell, last_cell, info)\n", " if len(time_table) > 0:\n", " all_df.append(pd.DataFrame(time_table))" ] }, { "cell_type": "code", "execution_count": 19, "id": "c66a946a-0f75-479f-b27a-88de3a17757f", "metadata": {}, "outputs": [], "source": [ "df_cum = pd.concat(all_df)" ] }, { "cell_type": "code", "execution_count": 20, "id": "4eb98f8c-5630-4db7-93fa-6d1da3d7d081", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BenutzernameVornameNachnameAbteilungPersonWannGerätHinein/HinausÜberprüfung
BraunTobiasBraunErding151502.10.2023 05:45:00ErdingHereinRFID
02.10.2023 07:17:00ErdingHinausRFID
04.10.2023 09:42:00ErdingHereinRFID
04.10.2023 09:43:00ErdingHinausRFID
10.10.2023 06:00:00ErdingHereinRFID
...........................
ZinglerMarkusZinglerErding144327.10.2023 15:16:00ErdingHinausRFID
30.10.2023 05:57:00ErdingHereinRFID
30.10.2023 10:49:00ErdingHinausRFID
ZweckstäCarinaZweckstätterErding152717.10.2023 07:15:00ErdingHereinRFID
17.10.2023 15:00:00ErdingHinausRFID
\n", "

865 rows × 0 columns

\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: [(Braun, Tobias, Braun, Erding, 1515, 02.10.2023 05:45:00, Erding, Herein, RFID), (Braun, Tobias, Braun, Erding, 1515, 02.10.2023 07:17:00, Erding, Hinaus, RFID), (Braun, Tobias, Braun, Erding, 1515, 04.10.2023 09:42:00, Erding, Herein, RFID), (Braun, Tobias, Braun, Erding, 1515, 04.10.2023 09:43:00, Erding, Hinaus, RFID), (Braun, Tobias, Braun, Erding, 1515, 10.10.2023 06:00:00, Erding, Herein, RFID), (Braun, Tobias, Braun, Erding, 1515, 10.10.2023 09:00:00, Erding, Hinaus, RFID), (Braun, Tobias, Braun, Erding, 1515, 26.10.2023 05:39:00, Erding, Herein, RFID), (Braun, Tobias, Braun, Erding, 1515, 26.10.2023 08:37:00, Erding, Hinaus, RFID), (Broushak, Farnaz, Broushaky, Erding, 1478, 04.10.2023 09:41:00, Erding, Herein, RFID), (Broushak, Farnaz, Broushaky, Erding, 1478, 04.10.2023 09:41:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 09.10.2023 06:00:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 09.10.2023 10:00:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 09.10.2023 10:30:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 09.10.2023 15:00:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 10.10.2023 05:58:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 10.10.2023 10:10:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 10.10.2023 10:39:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 10.10.2023 15:07:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 11.10.2023 07:56:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 11.10.2023 11:45:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 11.10.2023 12:14:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 11.10.2023 16:06:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 12.10.2023 07:51:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 12.10.2023 16:22:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 13.10.2023 07:59:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 13.10.2023 12:20:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 13.10.2023 12:48:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 13.10.2023 15:50:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 16.10.2023 06:01:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 16.10.2023 10:23:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 16.10.2023 10:52:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 16.10.2023 15:15:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 17.10.2023 06:54:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 17.10.2023 11:09:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 17.10.2023 11:37:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 17.10.2023 17:39:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 18.10.2023 06:56:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 18.10.2023 10:45:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 18.10.2023 11:11:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 18.10.2023 16:54:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 19.10.2023 06:57:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 19.10.2023 10:40:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 19.10.2023 11:07:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 19.10.2023 15:58:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 20.10.2023 06:56:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 20.10.2023 10:40:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 20.10.2023 11:04:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 20.10.2023 15:27:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 23.10.2023 07:53:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 23.10.2023 13:10:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 23.10.2023 13:37:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 23.10.2023 16:21:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 24.10.2023 07:49:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 24.10.2023 11:57:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 24.10.2023 12:15:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 24.10.2023 16:26:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 25.10.2023 05:54:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 25.10.2023 11:48:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 25.10.2023 12:15:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 25.10.2023 15:02:00, Erding, Hinaus, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 30.10.2023 07:03:00, Erding, Herein, RFID), (Czehmeis, Zoltan, Czehmeiszter, Erding, 1453, 30.10.2023 11:00:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 04.10.2023 07:30:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 04.10.2023 17:45:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 05.10.2023 07:37:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 05.10.2023 17:22:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 06.10.2023 07:29:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 06.10.2023 09:45:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 06.10.2023 11:30:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 06.10.2023 17:21:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 09.10.2023 07:32:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 09.10.2023 17:54:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 10.10.2023 07:30:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 10.10.2023 17:27:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 11.10.2023 07:36:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 11.10.2023 16:53:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 12.10.2023 07:32:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 12.10.2023 17:26:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 13.10.2023 07:27:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 13.10.2023 17:18:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 16.10.2023 07:33:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 16.10.2023 16:59:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 17.10.2023 07:32:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 17.10.2023 17:37:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 18.10.2023 07:25:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 18.10.2023 17:10:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 19.10.2023 07:36:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 19.10.2023 17:21:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 20.10.2023 07:35:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 20.10.2023 16:49:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 23.10.2023 07:37:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 23.10.2023 17:25:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 25.10.2023 07:31:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 25.10.2023 17:26:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 26.10.2023 07:26:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 26.10.2023 17:37:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 27.10.2023 07:38:00, Erding, Herein, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 27.10.2023 16:56:00, Erding, Hinaus, RFID), (Fischer, Ralf, Fischer, Erding, 1394, 30.10.2023 07:26:00, Erding, Herein, RFID), (Grimm, Sascha, Grimm, Erding, 1506, 03.10.2023 20:10:00, Erding, Herein, RFID), ...]\n", "\n", "[865 rows x 0 columns]" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#df_cum = pd.DataFrame(time_table)\n", "df_cum.groupby(['Benutzername', 'Vorname', 'Nachname', 'Abteilung', 'Person', 'Wann', 'Gerät', 'Hinein/Hinaus', 'Überprüfung']).all()" ] }, { "cell_type": "code", "execution_count": 21, "id": "36271c42-1a98-4a30-bea5-d478b333f81b", "metadata": {}, "outputs": [], "source": [ "writer = pd.ExcelWriter(\n", " r\"C:\\Users\\a.jurcenko\\Desktop\\Ralf Fischer\\Stempelzeiten Erding Oktober 2023 erweitert.xlsx\",\n", " engine=\"xlsxwriter\",\n", " datetime_format=\"hh:mm:ss\",\n", ")" ] }, { "cell_type": "code", "execution_count": 22, "id": "a8a237f9-6768-4ebb-9e19-adc7b1aacdea", "metadata": {}, "outputs": [], "source": [ "df_cum.to_excel(writer, index=False)" ] }, { "cell_type": "code", "execution_count": 23, "id": "df468f1e-d91d-4a46-a4d6-29aa9ea1d342", "metadata": {}, "outputs": [], "source": [ "writer.close()" ] }, { "cell_type": "code", "execution_count": null, "id": "23b8f318-1c43-4a44-89ad-0c1eed4c6258", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.11.4" } }, "nbformat": 4, "nbformat_minor": 5 }