Pandas and MySQL With Explanation
sql []\nSELECT a.machine_id, ROUND(AVG(b.timestamp - a.timestamp), 3) AS processing_time\nFROM Activity a, Activity b\nWHERE a.machine_id = b.machine_id AND a.p
```sql []\nSELECT a.machine_id, ROUND(AVG(b.timestamp - a.timestamp), 3) AS processing_time\nFROM Activity a, Activity b\nWHERE a.machine_id = b.machine_id AND a.process_id = b.process_id AND a.activity_type = \'start\' AND b.activity_type = \'end\'\nGROUP BY a.machine_id, b.machine_id;\n```\n\n## MySQL Explanation\n\n![acti.png](https://assets.leetcode.com/users/images/717c9c1a-ea37-406b-b83a-a12e20920f0f_1695919608.416645.png)\n\nFor the red color part: (since we grouped by `GROUP BY a.machine_id, b.machine_id`)\n- `a.machine_id = b.machine_id` matches the red group in a and b.\n- For red group `a.process_id = b.process_id` matches green and blue part in both a and b.\n- - `For the green part` : by `a.activity_type = \'start\' AND b.activity_type = \'end\'` we\'re telling who will be the start and end. We\'ve no more condition, so let\'s go to the `SELECT` part. We see `AVG(b.timestamp - a.timestamp)`, inside AVG, `b.timestamp - a.timestamp` taking places by 1.520 - 0.712 = `0.808`.\n\n#### Wait, is the condition `WHERE a.machine_id = b.machine_id AND a.process_id = b.process_id AND a.activity_type = \'start\' AND b.activity_type = \'end\'` really finished? NO! For red group we\'ve green and blue. Green is finished, blue will will take place now.\n\n- - `For the blue part` : Similarly we will get here 4.120 - 3.140 = `0.98`. But where the previous result `0.808` gone? It was stored in `AVG()` FUNCTION. \n```py\nIn Python: sum(v for v in [1,2,3]), what happens in sum() here? A value(v) is returned after every iteration in the loop and the value\n(v) is being added by sum().\n\nThe same thing happens in the MySQL code. The whole where condition is the for loop and the return value is \'b.timestamp - a.timestamp\'\nwhich is being stored in \'AVG() FUNCTION\' to be operated.\n```\n- - Now `0.98` will be added with `0.808` and then 0.98 + 0.808 = `1.788` will divided by 2 by the `AVG()` FUNCTION.\n- In this way for red group the whole where condition is executed. Now similar operation will happen for the group of 1 and 2 in `machine_id`.\n- \n## Pandas\n```py []\nimport pandas as pd\n\ndef get_average_time(activity: pd.DataFrame) -> pd.DataFrame:\n    startDf, endDf = activity.query(\'activity_type == "start"\'), activity.query(\'activity_type == "end"\')\n    merged_df = pd.merge(startDf, endDf, on=[\'machine_id\', \'process_id\'], suffixes=[\'_start\', \'_end\'])\n    merged_df[\'processing_time\'] = merged_df[\'timestamp_end\'] - merged_df[\'timestamp_start\']\n    return merged_df.groupby([\'machine_id\'])[\'processing_time\'].mean().round(3).reset_index()\n```\n\n## Pandas Explanation\nWe separated the the activity dataframe based on the `activity_type` column where first df `startDf` has values related with value `start` and second df `endDf` has values related with value `end`, then we merged them and added new column `processing_time`.. The rest is given in the below 2 pics :\n![sivity1.png](https://assets.leetcode.com/users/images/ee72936b-e9a0-457a-99a0-5335dffa06bf_1695925401.88148.png)\n\n![sivity2.png](https://assets.leetcode.com/users/images/343319d1-44e9-4336-a64d-f02c609f9aaf_1695925411.748391.png)\n\n## If the solution was helpful, an upvote  will be appreciated. Thank youu\n\n
['MySQL', 'Pandas']