Pandas | SQL | Explained Step By Step | Capital Gain/Loss
\nsee the Successfully Accepted Submission\nPython\nimport pandas as pd\n\ndef capital_gainloss(stocks: pd.DataFrame) -> pd.DataFrame:\n\n    grouped_stock = st
\n[see the Successfully Accepted Submission](https://leetcode.com/submissions/detail/1056971441/)\n```Python\nimport pandas as pd\n\ndef capital_gainloss(stocks: pd.DataFrame) -> pd.DataFrame:\n\n    grouped_stock = stocks.groupby(by=[\'stock_name\', \'operation\'])\n    \n    total_stock = grouped_stock.agg({\'price\':\'sum\'}).reset_index()\n    \n    calculated_consecutive_items = total_stock[\'price\'].diff(1)\n\n    total_stock[\'capital_gain_loss\'] = calculated_consecutive_items\n    \n    # we select rows starting from the second row (index 1) and then selects every second row after that.\n    gain_loss = total_stock[[\'stock_name\', \'capital_gain_loss\']].iloc[1::2]\n\n    return gain_loss\n```\n\n**Intuituin**\n\nGroup the \'stocks\' DataFrame by \'stock_name\' and \'operation\'.\n```\n    grouped_stock = stocks.groupby(by=[\'stock_name\', \'operation\'])\n```\n\nCalculate the total price for each group (stock_name and operation) and reset the index.\n```\n    total_stock = grouped_stock.agg({\'price\':\'sum\'}).reset_index()\n```\n\nCalculate the consecutive differences between \'price\' values within each group.\n```\n    calculated_consecutive_items = total_stock[\'price\'].diff(1)\n```\nCreate a new column \'capital_gain_loss\' in the \'total_stock\' DataFrame with the calculated differences.\n```\n    total_stock[\'capital_gain_loss\'] = calculated_consecutive_items\n```\n\nSelect rows starting from the second row (index 1) and then select every second row after that.This step effectively filters the DataFrame to include only rows where \'operation\' is \'buy\' or \'sell\'.\n```\n    gain_loss = total_stock[[\'stock_name\', \'capital_gain_loss\']].iloc[1::2]\n```\n \n \n**SQL**\n\n[see the Successfully Accepted Submission](https://leetcode.com/submissions/detail/1062903103/)\n\n```SQL\nSELECT stock_name, \nSUM(\n    IF(operation = "Buy", price*(-1), price)) capital_gain_loss\n    \nFROM stocks\nGROUP BY stock_name\n```\n\n![image](https://assets.leetcode.com/users/images/b1e4af48-4da1-486c-bc97-b11ae02febd0_1696186577.992237.jpeg)\n\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/)\n
['MySQL']