Forum Discussion
Python extension running really slow
Hi, I'm creating a script that adds a new column to my data, and it seems to be running very slowly. I've narrowed down what is causing it, but I do not know how I can fix it. It appears to be caused by my last few lines of code, that actually edit my data. The column is created without any issue, but when I try to update the column with the new information, the scrip runs forever as far as I can tell (I have seen it run for upwards of a half hour without finishing).
Initially I thought it may be because of the volume of my data, so I tried testing it with only the first 50 rows of my data, and it still never completed running the script. Here is my code:
import pandas as pd
dataset["Duration In Status"] = "null"
for x in range(40):
ticketID = dataset["Ticket ID"][x]
initialStatus = dataset["Initial Status"]
ticketable = pd.DataFrame({"Time of Change":dataset["Date"][x],
"Initial Status":dataset["Initial Status"][x],
"New Status":dataset["New Status"][x],
"Last Change": None}, index=[0])
for y in range(40):
if dataset["Ticket ID"][y] == dataset["Ticket ID"][x] and dataset["Date"][y] != dataset["Date"][x]:
ticketable.loc[len(ticketable.index)] = [dataset["Date"][y],dataset["Initial Status"][y],dataset["New Status"][y],y]
if dataset["Initial Status"][x] == ticketable["New Status"][0]:
dataset["Duration In Status"][x] = ticketable["Time of Change"][z]
else:
dataset["Duration In Status"][x] = "New Ticket"
so far as I can tell, it is caused by the final if/else statement, and the changing of data that occurs within said statement. If I delete the if/else statement, the code completes in about 5 seconds with no issue, and the new column added to the table. Any help with a solution would be greatly appreciated.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVfbTuNIEP2VVp4Zpa/uy5uHsFpWAkYD4oXlwZs0jIWxo9gJ8Pdbbbvt+EZCZqQgktg5XVXn1Knyw8PsLl6+2AJdLmZns4u0sBu7QouosPDx2r6h2yIqtjl8uElW/sPj2cOMMBZQTeGCnlM+p5gSxA2nBv7/uIKvz5MsB6hv6DpD4bKIsxRd26XN82jzAZd/2jxLdpM39I8gojpCGyYMY9URBzDOZrd2s4uX1l23bzUmV4JJuEbIvMZURjjYAeZ59rpObAHvr+17/GzTDmKY5/FzalceFjMiy1AZ9qFSYYg6qhpteExJGlRVrcOjBmvD2R+s6uAIYkRgCP2Nqo5g0hK0Lmv3N+MYwHXA4ZqaE+WZwQKyryBuUvR3lriw/toW241FFzubFh2syxT92GTPGwjV00KxEI5t3EhIgX4M4716NmRPaMDHqCivFck9PSB6rvpp3i5/2dU2KUF+2tesbKhPIiU4AAW5SOkciohBOMSQwNAAhfus7IuFSriv/k2VHUbMcGKwPC677x/oKn6x/24xpjJHl3d1LIRw7KpG51S6WDQKANNQ2oPdC6p5O6i79HWiUHk83WUHmGSaclIpTDUKIxxeQ4XZVQ6x7WL7Bkf89zHav63uQHYYwzUxLymlSLrOxUEfdz+mA1R7T1AlsBNfFTLAKkPw0T3RMEHmrCJCgSIMId0yOuvOl1ESObO6y9B5EkNrNHwyKTnTcBNgkCrFwJWOB6c1QQtHajjX7C3eCexqHgSs5EDXiBjEMuZJ37fpKunh7XGpBOclDg0qIA3VMgKflqnmhKv9sAJwDjfiwg7aTfHLbo613xZTeUxcyjg82X0BUgf7YUrDpcG0DjNKlzbpp7rYrpN4GTVibTHUHgZhg7DaH44Gw7EW1DVqUE/CkktKwCZP5YDzrjQ6yX0+EEerJTGpAKtZ5Rjg0FcDI+k2fDt/wqKwr2vXa+dZWkTLAr4jefEZPDGM9sfYFMxR3gKnCF2eQmq+wF1B6PKoQeTdonHL5i4PrymVJTzzLQ5jhTjnCb9EYuO9Hhdenf6EjtLNwBpZr4ZfoiJD/ruFzV88tFBBFTLFvvWZW0JOk52iUrHKM1VjcrCJYXYMjzTtO9TUTKpPY1jz6jRZncbdpivq6X/M+qS4lg6Cz4UXhFCDUeHifYviIk6f0b3dxE+ulaFrDvizlKJ0B7D8Jj7oaXqs40/pmGitmCpXDVYtMNpF7Xe+w5tGA0Dqx5ByBRpOji5jvgL1oAzX6022i5J6rQJAXCXrxc/dEBns4IsMXKdwf/HTR7erOuM43IJi7yMIozoAY0m5aKvpdj3s6Ia0w6m0JwfKCJp7XnALTAl24ImjyZb6Tc3ZtWxG5lRS93H6kVxF727LmBAkVkJ0Okg4C+GiV0ZXnqf4fXzM96G8/LSzDS6/uB4McYTLFZPjn1KgWBpXWeFmHumx3Weqz0YrCgq5iqFJktcoRf8sLpqmxpp1pEhKbgcBf90vFaOSd6DlPu8NPeVDgo+sJHvU57+1d80eH/8H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Entered Date", type datetime}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Ticket ID", Order.Ascending}, {"Entered Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Duration", each if [Index]=0 then null else if [Ticket ID]=#"Sorted Rows"[Ticket ID]{[Index]-1} then [Entered Date]-#"Sorted Rows"[Entered Date]{[Index]-1} else null), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type duration}}) in #"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
7 Replies
For each of your data rows (how many do you have) you are running 1600 iterations. Is that really necessary?
Maybe this can all be done in Power Query directly. Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.Here is a sample dataset, The goal is to get the duration time between status changes for tickets. For example, in for row 22, the initial status is Resolved- do not notify, and the new status is closed - do not notify. The script would look for a row with with the same ticket number, and a New status that is the same as column 22s old status. This would be row 21, which has the resolved - do not notify new status. I am planning to then switch out to power Query to find the duration between the two dates, and return that as my final value, which in this case, would be 1 hour and 2 minutes.
If this can all be done through power query directly, please let me know! I tried for a while to figure out how to do it but couldn't figure it out, so I tried using the python integration as I know much more about python.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Here is a table with the information, I deleted the audit text column, as it has private information, but it does not have any bearing on the python formula. If this doesn't work I also have a link to an excel sheet with the data in my google drive
Ticket Id Entered Date New Status Old Status
1336292 9/24/2021 4:42:21 PM Closed - No Action Necessary Resolved - No Action Necessary 1336292 9/15/2021 9:35:33 PM Resolved - No Action Necessary Service - New 1348537 11/5/2021 8:59:35 PM Resolved - Completed Nexigen Service - Assigned 1340317 9/30/2021 9:25:18 PM Closed - No Action Necessary New 1338726 9/25/2021 2:09:43 PM Closed - No Action Necessary Resolved - No Action Necessary 1338726 9/25/2021 1:56:12 PM Resolved - No Action Necessary Service - New 1338726 9/25/2021 12:56:15 PM Service - New Service - New 1329264 8/18/2021 8:05:09 PM On Hold - Future Event Service - In Progress 1342055 10/15/2021 8:33:34 PM Closed - Completed Resolved - Completed 1328242 9/14/2021 2:42:48 PM Service - Scheduled - Remote Service - In Progress 1106031 12/9/2018 1:16:26 AM Resolved New 1272011 12/15/2020 3:41:07 PM Closed - Completed Resolved - By Mike's IT 1111405 2/27/2019 6:07:22 PM Closed - Resolved Resolved 1342055 10/7/2021 2:28:30 PM Resolved - Completed Service - In Progress 1339241 9/28/2021 12:14:14 PM Service - Needs Reviewed by Nexigen Service - New 1392600 5/4/2022 7:25:06 PM Service - In Progress Service - Scheduled - Remote 1340800 10/1/2021 5:08:10 PM Service - New Service - New 1111405 1/3/2019 8:26:11 PM Resolved Escalated To Client IT 1377439 3/21/2022 6:14:46 PM Closed - Completed Resolved - Completed 1377439 3/11/2022 12:54:46 PM Resolved - Completed Service - In Progress 1394663 5/9/2022 10:30:12 PM Resolved - Bundled Service - New 1398544 5/26/2022 9:11:50 PM Closed - Completed Resolved - Completed 1394148 5/9/2022 6:34:21 AM Closed - Other Resolved - No Action Necessary 1394148 5/8/2022 6:04:14 AM Resolved - No Action Necessary Service - New 1394196 5/9/2022 7:47:02 AM Canceled Resolved - Duplicate 1394196 5/8/2022 7:47:13 AM Resolved - Duplicate Service - New 1409521 6/30/2022 10:21:42 PM Closed - Completed Resolved - Completed 1394443 5/9/2022 1:47:02 AM Closed - No Action Necessary Service - New 1397013 5/25/2022 6:48:24 PM Service - In Progress On Hold - Attempted Contact - 1st 1397013 5/25/2022 6:41:32 PM On Hold - Attempted Contact - 1st Service - Scheduled - Remote 1397059 5/18/2022 8:31:57 PM Service - Scheduled - Remote Escalate - Needs Scheduled 1399227 5/31/2022 11:01:11 AM Closed - Completed Resolved - Completed Nexigen 1399399 5/26/2022 6:39:07 PM Service - Assigned Service - Assign to Service Desk 1395867 5/20/2022 9:32:56 PM Closed - Completed Resolved - Completed 1382783 3/28/2022 12:09:03 PM On Hold - Attempted Contact - 2nd Service - Needs Reviewed by Nexigen 1383094 3/27/2022 4:35:56 AM Service - New Service - New 1384974 4/5/2022 8:58:11 PM Resolved - Awaiting Verification Service - In Progress 1377596 3/17/2022 4:21:26 PM Resolved - Completed Service - Scheduled - Remote 1199838 2/3/2020 9:58:09 PM Closed - Resolved Resolved 1199838 1/24/2020 3:40:12 PM Resolved On Hold - Awaiting Client Approval 1283806 3/31/2022 4:11:43 PM Closed - Do Not Notify Escalate - Escalated To Auto Valve 1007245 3/17/2018 10:35:09 AM Closed - Resolved No Action Necessary 1007245 3/17/2018 9:43:06 AM No Action Necessary New 1283806 2/7/2021 10:27:50 PM Escalate - Escalated To VinylMax IT Service - New 1380855 3/28/2022 5:01:45 PM Closed - Autofix Resolved - Bundled 1380855 3/17/2022 9:39:47 PM Resolved - Bundled Service - New 1380855 3/17/2022 5:27:01 PM Service - New Service - New 1380690 3/20/2022 1:49:46 PM Resolved - Awaiting Verification Escalate - Escalated to Michelman JDE 1384093 3/31/2022 1:43:01 PM Service - Assigned Service - Assign to Service Desk 1383274 3/31/2022 7:27:50 PM Closed - By Michelman IT Resolved - Completed - Michelman