{"cells":[{"block_group":"05301af52179473fa5d894f0d2cf565f","cell_type":"markdown","execution_count":null,"metadata":{"deepnote_cell_height":271.125,"tags":[],"cell_id":"05301af52179473fa5d894f0d2cf565f","deepnote_block_group":"05301af52179473fa5d894f0d2cf565f","deepnote_cell_type":"markdown","deepnote_sorting_key":"0","deepnote_source":""},"source":""},{"block_group":"bf3b460953254cdf8cf7b731a8873dda","cell_type":"markdown","execution_count":null,"metadata":{"deepnote_cell_height":201.59375,"tags":[],"cell_id":"bf3b460953254cdf8cf7b731a8873dda","deepnote_block_group":"bf3b460953254cdf8cf7b731a8873dda","deepnote_cell_type":"markdown","deepnote_sorting_key":"1","deepnote_source":"# Fetch OneDrive files in Deepnote\n\nThis template shows you how to fetch, analyze and visualize files from OneDrive. The easiest way to do this is to share the file to be publicly accessible with a link. We're using this As an example, we're using [this sales pipeline generated with dummy data](https://1drv.ms/x/s!AjP7y6eiYDDSafV7BusEaDHrI0o?e=cL0Fqi). Click \"Run notebook\" at the top of this window to see it in action.\n\n_Want to fetch your own files?_ Follow the next steps at the end of the notebook 👇"},"source":"# Fetch OneDrive files in Deepnote\n\nThis template shows you how to fetch, analyze and visualize files from OneDrive. The easiest way to do this is to share the file to be publicly accessible with a link. We're using this As an example, we're using [this sales pipeline generated with dummy data](https://1drv.ms/x/s!AjP7y6eiYDDSafV7BusEaDHrI0o?e=cL0Fqi). Click \"Run notebook\" at the top of this window to see it in action.\n\n_Want to fetch your own files?_ Follow the next steps at the end of the notebook 👇"},{"block_group":"8762f7cad5b945daa453d2de8e02ab51","cell_type":"code","execution_count":null,"metadata":{"execution_start":1656705227834,"execution_millis":0,"source_hash":"1b603cf0","tags":[],"cell_id":"8762f7cad5b945daa453d2de8e02ab51","deepnote_to_be_reexecuted":false,"deepnote_block_group":"8762f7cad5b945daa453d2de8e02ab51","deepnote_cell_type":"code","deepnote_sorting_key":"2","deepnote_content_hash":"1b603cf0","deepnote_execution_started_at":"2022-07-01T19:53:47.834Z","deepnote_execution_finished_at":"2022-07-01T19:53:47.834Z","deepnote_source":"import pandas as pd\nimport base64"},"outputs":[],"source":"import pandas as pd\nimport base64"},{"block_group":"c3dc4cbdf295441ab959791c87776f70","cell_type":"markdown","execution_count":null,"metadata":{"deepnote_cell_height":130.796875,"tags":[],"cell_id":"c3dc4cbdf295441ab959791c87776f70","deepnote_block_group":"c3dc4cbdf295441ab959791c87776f70","deepnote_cell_type":"markdown","deepnote_sorting_key":"3","deepnote_source":"## Fetch a file from OneDrive\n\nOneDrive allows us to fetch publicly shared files by taking a share URL and passing it to the [`shares_get` OneDrive API](https://docs.microsoft.com/en-us/onedrive/developer/rest-api/api/shares_get). The function `get_onedrive_download` creates the URL to download any shared file."},"source":"## Fetch a file from OneDrive\n\nOneDrive allows us to fetch publicly shared files by taking a share URL and passing it to the [`shares_get` OneDrive API](https://docs.microsoft.com/en-us/onedrive/developer/rest-api/api/shares_get). The function `get_onedrive_download` creates the URL to download any shared file."},{"block_group":"3cab40c03c894fcd9af4b90e983bbcee","cell_type":"code","execution_count":null,"metadata":{"execution_start":1656705227835,"execution_millis":2,"source_hash":"f25fe75e","tags":[],"cell_id":"3cab40c03c894fcd9af4b90e983bbcee","deepnote_to_be_reexecuted":false,"deepnote_block_group":"3cab40c03c894fcd9af4b90e983bbcee","deepnote_cell_type":"code","deepnote_sorting_key":"4","deepnote_content_hash":"f25fe75e","deepnote_execution_started_at":"2022-07-01T19:53:47.835Z","deepnote_execution_finished_at":"2022-07-01T19:53:47.837Z","deepnote_source":"# Convert a share link to a direct download\n# See https://docs.microsoft.com/en-us/onedrive/developer/rest-api/api/shares_get\ndef get_onedrive_download(share_link):\n base64_link_bytes = base64.b64encode(bytes(share_link, 'utf-8'))\n base64_link_string = base64_link_bytes.decode('utf-8').replace('/','_').replace('+','-').rstrip(\"=\")\n return f\"https://api.onedrive.com/v1.0/shares/u!{base64_link_string}/root/content\"\n\nONEDRIVE_SHARE_LINK = \"https://1drv.ms/x/s!AjP7y6eiYDDSafV7BusEaDHrI0o?e=cL0Fqi\"\n\nget_onedrive_download(ONEDRIVE_SHARE_LINK)"},"outputs":[{"output_type":"execute_result","execution_count":2,"data":{"text/plain":"'https://api.onedrive.com/v1.0/shares/u!aHR0cHM6Ly8xZHJ2Lm1zL3gvcyFBalA3eTZlaVlERFNhZlY3QnVzRWFESHJJMG8_ZT1jTDBGcWk/root/content'"},"metadata":{}}],"source":"# Convert a share link to a direct download\n# See https://docs.microsoft.com/en-us/onedrive/developer/rest-api/api/shares_get\ndef get_onedrive_download(share_link):\n base64_link_bytes = base64.b64encode(bytes(share_link, 'utf-8'))\n base64_link_string = base64_link_bytes.decode('utf-8').replace('/','_').replace('+','-').rstrip(\"=\")\n return f\"https://api.onedrive.com/v1.0/shares/u!{base64_link_string}/root/content\"\n\nONEDRIVE_SHARE_LINK = \"https://1drv.ms/x/s!AjP7y6eiYDDSafV7BusEaDHrI0o?e=cL0Fqi\"\n\nget_onedrive_download(ONEDRIVE_SHARE_LINK)"},{"block_group":"3695ddd6234a4a1a868d60e7bed6d7a7","cell_type":"markdown","execution_count":null,"metadata":{"deepnote_cell_height":74.796875,"tags":[],"cell_id":"3695ddd6234a4a1a868d60e7bed6d7a7","deepnote_block_group":"3695ddd6234a4a1a868d60e7bed6d7a7","deepnote_cell_type":"markdown","deepnote_sorting_key":"5","deepnote_source":"We then use pandas to read the URL into a dataframe directly. Deepnote summarizes and paginates dataframes automatically. You can filter and sort each column to drill into the data."},"source":"We then use pandas to read the URL into a dataframe directly. Deepnote summarizes and paginates dataframes automatically. You can filter and sort each column to drill into the data."},{"block_group":"abdca7ab56004ae7ae7863240ed784c4","cell_type":"code","execution_count":null,"metadata":{"allow_embed":"output","execution_start":1656705227835,"execution_millis":1937,"source_hash":"5a58390f","tags":[],"cell_id":"abdca7ab56004ae7ae7863240ed784c4","owner_user_id":"fb4d863f-e14b-4cec-a271-8335944bc775","deepnote_to_be_reexecuted":false,"deepnote_block_group":"abdca7ab56004ae7ae7863240ed784c4","deepnote_cell_type":"code","deepnote_sorting_key":"6","deepnote_content_hash":"5a58390f","deepnote_execution_started_at":"2022-07-01T19:53:47.835Z","deepnote_execution_finished_at":"2022-07-01T19:53:49.772Z","deepnote_source":"df = pd.read_excel(get_onedrive_download(ONEDRIVE_SHARE_LINK))\ndf"},"outputs":[{"output_type":"execute_result","execution_count":3,"data":{"application/vnd.deepnote.dataframe.v3+json":{"column_count":4,"row_count":1658,"columns":[{"name":"Name","dtype":"object","stats":{"unique_count":1658,"nan_count":0,"categories":[{"name":"Victor Larson","count":1},{"name":"Consuela Lowery","count":1},{"name":"1656 others","count":1656}]}},{"name":"Status","dtype":"object","stats":{"unique_count":6,"nan_count":0,"categories":[{"name":"Closed","count":445},{"name":"Lead","count":367},{"name":"4 others","count":846}]}},{"name":"Priority","dtype":"object","stats":{"unique_count":3,"nan_count":0,"categories":[{"name":"Medium","count":849},{"name":"Low","count":504},{"name":"High","count":305}]}},{"name":"Estimated Value","dtype":"int64","stats":{"unique_count":73,"nan_count":0,"min":"300","max":"8100","histogram":[{"bin_start":300,"bin_end":1080,"count":483},{"bin_start":1080,"bin_end":1860,"count":346},{"bin_start":1860,"bin_end":2640,"count":446},{"bin_start":2640,"bin_end":3420,"count":88},{"bin_start":3420,"bin_end":4200,"count":53},{"bin_start":4200,"bin_end":4980,"count":67},{"bin_start":4980,"bin_end":5760,"count":93},{"bin_start":5760,"bin_end":6540,"count":65},{"bin_start":6540,"bin_end":7320,"count":11},{"bin_start":7320,"bin_end":8100,"count":6}]}},{"name":"_deepnote_index_column","dtype":"int64"}],"rows":[{"Name":"Victor Larson","Status":"Closed","Priority":"Low","Estimated Value":900,"_deepnote_index_column":0},{"Name":"Consuela Lowery","Status":"Lead","Priority":"Medium","Estimated Value":2500,"_deepnote_index_column":1},{"Name":"Chanelle Klein","Status":"Proposal","Priority":"Medium","Estimated Value":1800,"_deepnote_index_column":2},{"Name":"Gregg Harper","Status":"Closed","Priority":"High","Estimated Value":3500,"_deepnote_index_column":3},{"Name":"Walter Hobbs","Status":"Lead","Priority":"Low","Estimated Value":800,"_deepnote_index_column":4},{"Name":"Zada Cohen","Status":"Lead","Priority":"Medium","Estimated Value":1700,"_deepnote_index_column":5},{"Name":"Gary Ford","Status":"Qualified","Priority":"Low","Estimated Value":600,"_deepnote_index_column":6},{"Name":"Hunter Becker","Status":"Qualified","Priority":"Medium","Estimated Value":2800,"_deepnote_index_column":7},{"Name":"Shelton Bender","Status":"Lead","Priority":"Low","Estimated Value":800,"_deepnote_index_column":8},{"Name":"Billie Bauer","Status":"Qualified","Priority":"Low","Estimated Value":600,"_deepnote_index_column":9}]},"text/plain":" Name Status Priority Estimated Value\n0 Victor Larson Closed Low 900\n1 Consuela Lowery Lead Medium 2500\n2 Chanelle Klein Proposal Medium 1800\n3 Gregg Harper Closed High 3500\n4 Walter Hobbs Lead Low 800\n... ... ... ... ...\n1653 Darron Buckley Lead Medium 2100\n1654 Hunter Robertson Closed Medium 1800\n1655 Clint Foley Closed Medium 1600\n1656 Jonathan Best Qualified Medium 2200\n1657 Emile Kramer Qualified Low 1100\n\n[1658 rows x 4 columns]","text/html":"
| \n | Name | \nStatus | \nPriority | \nEstimated Value | \n
|---|---|---|---|---|
| 0 | \nVictor Larson | \nClosed | \nLow | \n900 | \n
| 1 | \nConsuela Lowery | \nLead | \nMedium | \n2500 | \n
| 2 | \nChanelle Klein | \nProposal | \nMedium | \n1800 | \n
| 3 | \nGregg Harper | \nClosed | \nHigh | \n3500 | \n
| 4 | \nWalter Hobbs | \nLead | \nLow | \n800 | \n
| ... | \n... | \n... | \n... | \n... | \n
| 1653 | \nDarron Buckley | \nLead | \nMedium | \n2100 | \n
| 1654 | \nHunter Robertson | \nClosed | \nMedium | \n1800 | \n
| 1655 | \nClint Foley | \nClosed | \nMedium | \n1600 | \n
| 1656 | \nJonathan Best | \nQualified | \nMedium | \n2200 | \n
| 1657 | \nEmile Kramer | \nQualified | \nLow | \n1100 | \n
1658 rows × 4 columns
\n| \n | Estimated Value | \nPriority | \nStatus | \n
|---|---|---|---|
| 0 | \n2500 | \nMedium | \nLead | \n
| 1 | \n1800 | \nMedium | \nProposal | \n
| 2 | \n800 | \nLow | \nLead | \n
| 3 | \n1700 | \nMedium | \nLead | \n
| 4 | \n600 | \nLow | \nQualified | \n
| ... | \n... | \n... | \n... | \n
| 1048 | \n2200 | \nMedium | \nQualified | \n
| 1049 | \n2200 | \nMedium | \nNegotiation | \n
| 1050 | \n2100 | \nMedium | \nLead | \n
| 1051 | \n2200 | \nMedium | \nQualified | \n
| 1052 | \n1100 | \nLow | \nQualified | \n
1053 rows × 3 columns
\n