Pandas | SQL | Easy | Average Time of Process per Machine
see the Successfully Accepted Submission\n\n\nimport pandas as pd\n\ndef get_average_time(activity: pd.DataFrame) -> pd.DataFrame:\n    \n    # First, we filter
[see the Successfully Accepted Submission](https://leetcode.com/submissions/detail/1056441593/)\n\n```\nimport pandas as pd\n\ndef get_average_time(activity: pd.DataFrame) -> pd.DataFrame:\n    \n    # First, we filter rows from the activity DataFrame where the value in the "activity_type" column is equal to \'start\'\n    start_activities = activity[activity[\'activity_type\'] == \'start\']\n\n     # Meanwhile, we also filter rows from the activity DataFrame where the value in the "activity_type" column is equal to \'end\'\n    end_activities = activity[activity[\'activity_type\'] == \'end\']\n\n    # Then, we merge two DataFrames, `start_activities` and `end_activities`, based on the columns `machine_id` and `process_id`. Also, as a result it adds the suffixes \'_start\', \'_end\' to the columns to distinguish them after merging. \n    merged_start_end = start_activities.merge(\n        end_activities,\n        on=[\'machine_id\', \'process_id\'],\n        suffixes=(\'_start\', \'_end\')\n    )\n\n    # After that, we calculate the `processing_time` as the difference between timestamps by  calculating the time duration between two the columns, specifically "timestamp_end" and "timestamp_start"\n    merged_start_end[\'processing_time\'] = merged_start_end[\'timestamp_end\'] - merged_start_end[\'timestamp_start\']\n    \n\n    # Next, we group data by the "machine_id" column and then select a specific column ("processing_time") from within each group.\n    grouped_start_end = merged_start_end.groupby(\'machine_id\')[\'processing_time\']\n    \n    # After grouping by `machine_id`, the average `processing_time` is calculated.\n    average_time = grouped_start_end.mean()\n    \n    # Now, we have to transform the grouped DataFrame back into a more conventional format, so we will reset the index after the Series has been grouped above. Because, after grouping the index often changes. By default, pandas assigns a new index to the resulting grouped DataFrame. This new index is typically a multi-level index, with one level for each grouping key. The original index becomes part of the DataFrame as metadata.\n    structured_df = average_time.reset_index()\n    \n    # Finally, we  round the numerical values in the structured_df. Because, in the task it is asked to that the average time should be rounded to 3 decimal places.\n    average_time_to_complete = structured_df.round(3)\n\n    return average_time_to_complete\n    \n```\n\n**SQL**\n[see the Successfully Accepted Submission](https://leetcode.com/submissions/detail/1061746957/)\n\n```\nSELECT a.machine_id, ROUND(SUM((c.timestamp - a.timestamp)) / COUNT(a.machine_id),3) as processing_time  \n\nFROM Activity as a \n\nJOIN Activity as c ON a.machine_id = c.machine_id \nand a.process_id = c.process_id \nand a.activity_type = "start" \nand c.activity_type = "end"\n\nGROUP BY a.machine_id\n```\n\n```\n-- Select \'machine_id\' and calculate the average processing time for each machine\n-- The processing time is calculated as the difference in timestamps between "start" and "end" activities\nSELECT a.machine_id,\n        ROUND(SUM((c.timestamp - a.timestamp)) / COUNT(a.machine_id), 3) as processing_time  \n\n-- Retrieve data from the \'Activity\' table and alias it as \'a\'\nFROM Activity as a \n\n-- Join the \'Activity\' table with itself as \'c\' based on specific conditions\n-- - The same \'machine_id\'\n-- - The same \'process_id\'\n-- - \'activity_type\' is "start" for \'a\' and "end" for \'c\'\nJOIN Activity as c ON a.machine_id = c.machine_id \nand a.process_id = c.process_id \nand a.activity_type = "start" \nand c.activity_type = "end"\n\n-- Group the results by \'machine_id\' to calculate the average processing time for each machine\nGROUP BY a.machine_id;\n```\n\n![image](https://assets.leetcode.com/users/images/04dd34e1-d7b5-4481-857e-8461fc196ece_1695399610.5759363.jpeg)\n\n# Pandas & SQL | SOLVED & EXPLAINED LIST\n\n**EASY**\n\n- [Combine Two Tables](https://leetcode.com/problems/combine-two-tables/solutions/4051076/pandas-sql-easy-combine-two-tables/)\n\n- [Employees Earning More Than Their Managers](https://leetcode.com/problems/employees-earning-more-than-their-managers/solutions/4051991/pandas-sql-easy-employees-earning-more-than-their-managers/)\n\n- [Duplicate Emails](https://leetcode.com/problems/duplicate-emails/solutions/4055225/pandas-easy-duplicate-emails/)\n\n- [Customers Who Never Order](https://leetcode.com/problems/customers-who-never-order/solutions/4055429/pandas-sql-easy-customers-who-never-order-easy-explained/)\n\n- [Delete Duplicate Emails](https://leetcode.com/problems/delete-duplicate-emails/solutions/4055572/pandas-sql-easy-delete-duplicate-emails-easy/)\n- [Rising Temperature](https://leetcode.com/problems/rising-temperature/solutions/4056328/pandas-sql-easy-rising-temperature-easy/)\n\n- [ Game Play Analysis I](https://leetcode.com/problems/game-play-analysis-i/solutions/4056422/pandas-sql-easy-game-play-analysis-i-easy/)\n\n- [Find Customer Referee](https://leetcode.com/problems/find-customer-referee/solutions/4056516/pandas-sql-easy-find-customer-referee-easy/)\n\n- [Classes More Than 5 Students](https://leetcode.com/problems/classes-more-than-5-students/solutions/4058381/pandas-sql-easy-classes-more-than-5-students-easy/)\n- [Employee Bonus](https://leetcode.com/problems/employee-bonus/solutions/4058430/pandas-sql-easy-employee-bonus/)\n\n- [Sales Person](https://leetcode.com/problems/sales-person/solutions/4058824/pandas-sql-easy-sales-person/)\n\n- [Biggest Single Number](https://leetcode.com/problems/biggest-single-number/solutions/4063950/pandas-sql-easy-biggest-single-number/)\n\n- [Not Boring Movies](https://leetcode.com/problems/not-boring-movies/solutions/4065350/pandas-sql-easy-not-boring-movies/)\n- [Swap Salary](https://leetcode.com/problems/swap-salary/solutions/4065423/pandas-sql-easy-swap-salary/)\n\n- [Actors & Directors Cooperated min Three Times](https://leetcode.com/problems/actors-and-directors-who-cooperated-at-least-three-times/solutions/4065511/pandas-sql-easy-explained-step-by-step-actors-directors-cooperated-min-three-times/)\n\n- [Product Sales Analysis I](https://leetcode.com/problems/product-sales-analysis-i/solutions/4065545/pandas-sql-easy-product-sales-analysis-i/)\n\n- [Project Employees I](https://leetcode.com/problems/project-employees-i/solutions/4065635/pandas-sql-easy-project-employees-i/)\n- [Sales Analysis III](https://leetcode.com/problems/sales-analysis-iii/solutions/4065755/sales-analysis-iii-pandas-easy/)\n\n- [Reformat Department Table](https://leetcode.com/problems/reformat-department-table/solutions/4066153/pandas-sql-easy-explained-step-by-step-reformat-department-table/)\n\n- [Top Travellers](https://leetcode.com/problems/top-travellers/solutions/4066252/top-travellers-pandas-easy-eaxplained-step-by-step/)\n\n- [Replace Employee ID With The Unique Identifier](https://leetcode.com/problems/replace-employee-id-with-the-unique-identifier/solutions/4066321/pandas-sql-easy-replace-employee-id-with-the-unique-identifier/)\n- [Group Sold Products By The Date](https://leetcode.com/problems/group-sold-products-by-the-date/solutions/4066344/pandas-sql-easy-explained-step-by-step-group-sold-products-by-the-date/)\n\n- [Customer Placing the Largest Number of Orders](https://leetcode.com/problems/customer-placing-the-largest-number-of-orders/solutions/4068822/pandas-sql-easy-explained-step-by-step-customer-placing-the-largest-number-of-orders/)\n\n- [Article Views I](https://leetcode.com/problems/article-views-i/solutions/4069777/pandas-sql-easy-article-views-i/)\n\n- [User Activity for the Past 30 Days I](https://leetcode.com/problems/user-activity-for-the-past-30-days-i/solutions/4069797/pandas-sql-easy-user-activity-for-the-past-30-days-i/)\n\n- [Find Users With Valid E-Mails](https://leetcode.com/problems/find-users-with-valid-e-mails/solutions/4069810/pandas-sql-easy-find-users-with-valid-e-mails/)\n\n- [Patients With a Condition](https://leetcode.com/problems/patients-with-a-condition/solutions/4069817/pandas-sql-easy-patients-with-a-condition/)\n\n- [Customer Who Visited but Did Not Make Any Transactions](https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/solutions/4072542/pandas-sql-easy-customer-who-visited-but-did-not-make-any-transactions/)\n\n\n- [Bank Account Summary II](https://leetcode.com/problems/bank-account-summary-ii/solutions/4072569/pandas-sql-easy-bank-account-summary-ii/)\n\n- [Invalid Tweets](https://leetcode.com/problems/invalid-tweets/solutions/4072599/pandas-sql-easy-invalid-tweets/)\n\n- [Daily Leads and Partners](https://leetcode.com/problems/daily-leads-and-partners/solutions/4072981/pandas-sql-easy-daily-leads-and-partners/)\n\n- [Recyclable and Low Fat Products](https://leetcode.com/problems/recyclable-and-low-fat-products/solutions/4073003/pandas-sql-easy-recyclable-and-low-fat-products/)\n\n- [Rearrange Products Table](https://leetcode.com/problems/rearrange-products-table/solutions/4073028/pandas-sql-easy-rearrange-products-table/)\n- [Calculate Special Bonus](https://leetcode.com/problems/calculate-special-bonus/solutions/4073595/pandas-sql-easy-calculate-special-bonus/)\n\n- [Count Unique Subjects](https://leetcode.com/problems/number-of-unique-subjects-taught-by-each-teacher/solutions/4073666/pandas-sql-easy-count-unique-subjects/)\n\n- [Count Unique Subjects](https://leetcode.com/problems/number-of-unique-subjects-taught-by-each-teacher/solutions/4073666/pandas-sql-easy-count-unique-subjects/)\n\n- [Fix Names in a Table](https://leetcode.com/problems/fix-names-in-a-table/solutions/4073695/pandas-sql-easy-fix-names-in-a-table/)\n- [Primary Department for Each Employee](https://leetcode.com/problems/primary-department-for-each-employee/solutions/4076183/pandas-sql-easy-primary-department-for-each-employee/)\n\n- [The Latest Login in 2020](https://leetcode.com/problems/the-latest-login-in-2020/solutions/4076240/pandas-sql-easy-the-latest-login-in-2020/)\n\n- [Find Total Time Spent by Each Employee](https://leetcode.com/problems/find-total-time-spent-by-each-employee/solutions/4076313/pandas-sql-easy-find-total-time-spent-by-each-employee/)\n\n- [Find Followers Count](https://leetcode.com/problems/find-followers-count/solutions/4076342/pandas-sql-easy-find-followers-count/)\n- [Percentage of Users Attended a Contest](https://leetcode.com/problems/percentage-of-users-attended-a-contest/solutions/4077301/pandas-sql-easy-percentage-of-users-attended-a-contest/)\n\n- [Employees With Missing Information](https://leetcode.com/problems/employees-with-missing-information/solutions/4077308/pandas-sql-easy-employees-with-missing-information/)\n\n- [Average Time of Process per Machine](https://leetcode.com/problems/average-time-of-process-per-machine/solutions/4077402/pandas-sql-easy-average-time-of-process-per-machine/)
['MySQL']