title
Python Pandas Tutorial (Part 11): Reading/Writing Data to Different Sources - Excel, JSON, SQL, Etc

description
In this video, we will be learning how to import and export data from multiple different sources. We will cover CSV, JSON, Excel, SQL, and more. This video is sponsored by Brilliant. Go to https://brilliant.org/cms to sign up for free. Be one of the first 200 people to sign up with this link and get 20% off your premium subscription. In this Python Programming video, we will be learning how to load and save data using multiple different sources. We will learn how to read/write data to CSV, JSON, Excel, SQL, and more. This covers the vast majority of formats you'll see in the data science field and will be extremely useful to know. Let's get started... Video Timestamps: Read CSV - 0:56 Write CSV - 3:20 Write TSV - 4:40 Read TSV - 6:00 Write Excel - 6:15 Read Excel - 10:42 (Start at 6:15 to see installed packages) Write JSON - 12:18 Read JSON - 15:41 Write SQL - 16:59 Read SQL - 24:57 (Start at 16:59 to see installed packages) The code for this video can be found at: bit.ly/Pandas-11 StackOverflow Survey Download Page - http://bit.ly/SO-Survey-Download Environment Variables Tutorial - https://youtu.be/5iWhQWVXosU ✅ Support My Channel Through Patreon: https://www.patreon.com/coreyms ✅ Become a Channel Member: https://www.youtube.com/channel/UCCezIgC97PvUuR4_gbFUs5g/join ✅ One-Time Contribution Through PayPal: https://goo.gl/649HFY ✅ Cryptocurrency Donations: Bitcoin Wallet - 3MPH8oY2EAgbLVy7RBMinwcBntggi7qeG3 Ethereum Wallet - 0x151649418616068fB46C3598083817101d3bCD33 Litecoin Wallet - MPvEBY5fxGkmPQgocfJbxP6EmTo5UUXMot ✅ Corey's Public Amazon Wishlist http://a.co/inIyro1 ✅ Equipment I Use and Books I Recommend: https://www.amazon.com/shop/coreyschafer ▶️ You Can Find Me On: My Website - http://coreyms.com/ My Second Channel - https://www.youtube.com/c/coreymschafer Facebook - https://www.facebook.com/CoreyMSchafer Twitter - https://twitter.com/CoreyMSchafer Instagram - https://www.instagram.com/coreymschafer/ #Python #Pandas

detail
{'title': 'Python Pandas Tutorial (Part 11): Reading/Writing Data to Different Sources - Excel, JSON, SQL, Etc', 'heatmap': [{'end': 1926.297, 'start': 1885.214, 'weight': 0.887}], 'summary': 'This tutorial series covers reading and writing data in csv, excel, json, and sql formats using pandas, including file manipulation, data filtering, database connections, and managing sql data, providing practical examples and learning resources for further exploration.', 'chapters': [{'end': 53.731, 'segs': [{'end': 53.731, 'src': 'embed', 'start': 0.189, 'weight': 0, 'content': [{'end': 5.012, 'text': "Hey there, how's it going everybody? In this video, we're going to be learning how to read and write data to different sources.", 'start': 0.189, 'duration': 4.823}, {'end': 11.996, 'text': "So we'll learn how to read and write data using CSV files, Excel files, JSON, and also SQL databases.", 'start': 5.392, 'duration': 6.604}, {'end': 19.881, 'text': "Now in this series so far, we've been reading data from CSV files, but in data science, there are so many different ways for data to be stored.", 'start': 12.377, 'duration': 7.504}, {'end': 27.026, 'text': "So by the end of this video, you should be able to get your data to and from pandas, no matter what data format you're using.", 'start': 20.302, 'duration': 6.724}, {'end': 32.506, 'text': "now, if you're watching this video because you're looking for how to read and write a specific file format,", 'start': 27.506, 'duration': 5}, {'end': 38.948, 'text': "then i'll be sure to add timestamps in the description section below to where we read and write from each different format.", 'start': 32.506, 'duration': 6.442}, {'end': 43.509, 'text': 'now i would like to mention that we do have a sponsor for this series of videos, and that is brilliant.', 'start': 38.948, 'duration': 4.561}, {'end': 46.23, 'text': 'so i really want to thank brilliant for sponsoring this series,', 'start': 43.509, 'duration': 2.721}, {'end': 51.171, 'text': 'and it would be great if you all can check them out using the link in the description section below and support the sponsors,', 'start': 46.23, 'duration': 4.941}, {'end': 53.731, 'text': "and i'll talk more about their services in just a bit.", 'start': 51.171, 'duration': 2.56}], 'summary': 'Learn to read and write data from various sources like csv, excel, json, and sql databases, enabling data transfer to and from pandas.', 'duration': 53.542, 'max_score': 0.189, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao189.jpg'}], 'start': 0.189, 'title': 'Reading and writing data formats', 'summary': 'Covers reading and writing data using csv, excel, json, and sql databases, focusing on pandas integration and enabling the audience to work with data in different formats.', 'chapters': [{'end': 53.731, 'start': 0.189, 'title': 'Reading and writing data formats', 'summary': 'Covers reading and writing data using various formats such as csv, excel, json, and sql databases, aiming to enable the audience to work with data in different formats, with a focus on pandas integration.', 'duration': 53.542, 'highlights': ['The video covers reading and writing data using CSV files, Excel files, JSON, and SQL databases, providing a comprehensive understanding of working with different data formats.', 'By the end of the video, the audience should be able to handle data transfer to and from pandas regardless of the data format, ensuring a versatile data processing skill set.', "The series is sponsored by Brilliant, with a call to action for the audience to check out the sponsor's services and support them, indicating a partnership and support for the content."]}], 'duration': 53.542, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao189.jpg', 'highlights': ['The video covers reading and writing data using CSV, Excel, JSON, and SQL databases.', 'By the end, the audience should handle data transfer to/from pandas regardless of format.', 'The series is sponsored by Brilliant, with a call to action for the audience.']}, {'end': 179.862, 'segs': [{'end': 157.583, 'src': 'embed', 'start': 114.735, 'weight': 0, 'content': [{'end': 124.543, 'text': 'And when I read in the CSV, we can see that it sets this data frame equal to the data, and we can print this data out down here at the bottom.', 'start': 114.735, 'duration': 9.808}, {'end': 129.805, 'text': 'So that is the read CSV method and it allows us to pull data in to pandas.', 'start': 125.163, 'duration': 4.642}, {'end': 133.166, 'text': "Now let's learn how to write this data back to a CSV.", 'start': 130.245, 'duration': 2.921}, {'end': 138.529, 'text': "So maybe you're going to make some changes and some different analysis here to your data frame.", 'start': 133.607, 'duration': 4.922}, {'end': 146.452, 'text': 'And then we want to export this back to our file system for later use or so that we can share it with someone else or something like that.', 'start': 138.829, 'duration': 7.623}, {'end': 151.257, 'text': "So, for example, let's filter down for a specific location in this survey.", 'start': 146.932, 'duration': 4.325}, {'end': 157.583, 'text': "You know, maybe you're doing some analysis for your specific country and you just want to see the survey results from that location.", 'start': 151.557, 'duration': 6.026}], 'summary': 'Introduction to reading and writing data in csv format using pandas in python.', 'duration': 42.848, 'max_score': 114.735, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao114735.jpg'}], 'start': 53.731, 'title': 'Reading and writing csv files in pandas', 'summary': 'Covers reading and writing csv files in pandas, including reading in data, setting index, and filtering data, demonstrating how to read and write data in a data frame using pandas.', 'chapters': [{'end': 179.862, 'start': 53.731, 'title': 'Reading and writing csv files in pandas', 'summary': 'Covers reading and writing csv files in pandas, including reading in data, setting index, and filtering data, demonstrating how to read and write data in a data frame using pandas.', 'duration': 126.131, 'highlights': ['The chapter covers reading and writing CSV files in Pandas.', 'Demonstrates reading in data from a CSV file and setting index to the respondent column.', 'Explains how to write data back to a CSV file after making changes and filtering data.']}], 'duration': 126.131, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao53731.jpg', 'highlights': ['Demonstrates reading in data from a CSV file and setting index to the respondent column.', 'Explains how to write data back to a CSV file after making changes and filtering data.', 'The chapter covers reading and writing CSV files in Pandas.']}, {'end': 504.517, 'segs': [{'end': 238.199, 'src': 'embed', 'start': 206.565, 'weight': 2, 'content': [{'end': 209.366, 'text': 'So to do this, we can use the to CSV method.', 'start': 206.565, 'duration': 2.801}, {'end': 216.788, 'text': "So we can say I'll just say India underscore DF, which is our data frame dot to underscore CSV.", 'start': 209.806, 'duration': 6.982}, {'end': 222.269, 'text': "And now I'm just going to pass it into that same location in that data directory.", 'start': 217.508, 'duration': 4.761}, {'end': 225.27, 'text': "And then I'll just call this modified dot CSV.", 'start': 222.769, 'duration': 2.501}, {'end': 230.393, 'text': "So if I run this, we can see that we don't get any errors.", 'start': 226.61, 'duration': 3.783}, {'end': 238.199, 'text': 'And now if I go back and look at my file system here, then I have this modified.csv.', 'start': 230.773, 'duration': 7.426}], 'summary': 'Using the to_csv method, the india_df dataframe is saved as modified.csv without errors.', 'duration': 31.634, 'max_score': 206.565, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao206565.jpg'}, {'end': 350.669, 'src': 'embed', 'start': 324.172, 'weight': 0, 'content': [{'end': 331.136, 'text': 'Now you can pass in anything here if you want a file that is separated by hashes or anything.', 'start': 324.172, 'duration': 6.964}, {'end': 334.638, 'text': 'But commas and tabs are probably the most common.', 'start': 331.636, 'duration': 3.002}, {'end': 340.362, 'text': "So I'm going to put a backslash T there because that's how we specify tabs in Python.", 'start': 334.939, 'duration': 5.423}, {'end': 346.666, 'text': "And now, if I run this cell, I'm going to go back to our data directory.", 'start': 340.882, 'duration': 5.784}, {'end': 350.669, 'text': 'here we can see that now we have this modified.tsv.', 'start': 346.666, 'duration': 4.003}], 'summary': 'Demonstrates how to specify file separator in python using backslash t. creates modified.tsv in data directory.', 'duration': 26.497, 'max_score': 324.172, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao324172.jpg'}, {'end': 512.076, 'src': 'embed', 'start': 480.297, 'weight': 1, 'content': [{'end': 482.339, 'text': "Let's say I'll save it in that data folder again.", 'start': 480.297, 'duration': 2.042}, {'end': 487.323, 'text': "I'll call this modified.xlsx.", 'start': 482.839, 'duration': 4.484}, {'end': 490.826, 'text': "So I'm going to write to the newer Excel format.", 'start': 487.483, 'duration': 3.343}, {'end': 498.892, 'text': "So if I run this, then it might take a second here for this to work because it's actually creating this Excel file on the back end.", 'start': 491.186, 'duration': 7.706}, {'end': 500.894, 'text': "So let's let this finish.", 'start': 499.573, 'duration': 1.321}, {'end': 504.517, 'text': "And we can tell it's finished when this turns from an asterisk to a number here.", 'start': 501.074, 'duration': 3.443}, {'end': 512.076, 'text': "Okay, so once that's finished, let's flip over to our data folder here and we can see that we do have that .", 'start': 505.512, 'duration': 6.564}], 'summary': 'Creating modified.xlsx in data folder in newer excel format.', 'duration': 31.779, 'max_score': 480.297, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao480297.jpg'}], 'start': 180.042, 'title': 'Data filtering and file format operations', 'summary': "Demonstrates filtering a dataframe to include 'india' and exporting data to a csv file, also reading and writing tab delimited and excel files in pandas, including using custom separators and installing necessary packages like xlwt, openpyxl, and xlrd.", 'chapters': [{'end': 281.721, 'start': 180.042, 'title': 'Filtering and exporting data in python', 'summary': "Demonstrates how to filter a dataframe to include only 'india' in the country column using the df.loc method, and then export the filtered data to a csv file, confirming the successful export and content.", 'duration': 101.679, 'highlights': ["The process demonstrates filtering a DataFrame using df.loc to include only 'India' in the country column.", "The method to_csv() is used to export the filtered data to a CSV file named 'modified.csv' in a specified location within the data directory.", "The successful export is confirmed by checking the file system for the presence of 'modified.csv' and inspecting its content to verify the inclusion of 'India' in the country column."]}, {'end': 504.517, 'start': 282.081, 'title': 'Reading and writing to different file formats in pandas', 'summary': 'Demonstrates how to read and write tab delimited files and excel files in pandas, including specifying custom separators and installing necessary packages, such as xlwt, openpyxl, and xlrd, to work with excel files.', 'duration': 222.436, 'highlights': ["The chapter explains how to write to a tab delimited file in pandas by changing the file extension to .tsv and specifying a custom separator, using the sep argument, like sep='\t', resulting in a modified.tsv file in the data directory.", 'The chapter details the installation of XLWT, OpenPyXL, and XLRD packages to write and read Excel files in pandas, emphasizing the need for the correct environment and the ability to pip install multiple packages at once.', 'The chapter demonstrates how to write to an Excel file in pandas using the to_excel method, specifying the file name and format, such as modified.xlsx, and showcasing the creation of the Excel file in the data folder.']}], 'duration': 324.475, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao180042.jpg', 'highlights': ['The chapter details the installation of XLWT, OpenPyXL, and XLRD packages for writing and reading Excel files in pandas.', "The chapter demonstrates filtering a DataFrame using df.loc to include only 'India' in the country column.", "The method to_csv() is used to export the filtered data to a CSV file named 'modified.csv' in a specified location within the data directory.", 'The chapter explains how to write to a tab delimited file in pandas by changing the file extension to .tsv and specifying a custom separator.']}, {'end': 841.1, 'segs': [{'end': 573.635, 'src': 'embed', 'start': 505.512, 'weight': 1, 'content': [{'end': 512.076, 'text': "Okay, so once that's finished, let's flip over to our data folder here and we can see that we do have that .", 'start': 505.512, 'duration': 6.564}, {'end': 513.996, 'text': 'xlsx file.', 'start': 512.076, 'duration': 1.92}, {'end': 518.48, 'text': "Now this likely won't open up in Jupyter because this is an Excel file.", 'start': 514.738, 'duration': 3.742}, {'end': 522.202, 'text': "We can see here that we can't open this up in the browser.", 'start': 518.539, 'duration': 3.663}, {'end': 523.363, 'text': 'We actually need Excel.', 'start': 522.241, 'duration': 1.122}, {'end': 527.944, 'text': 'So let me open up my Finder window here.', 'start': 524.222, 'duration': 3.722}, {'end': 536.646, 'text': 'I have this open down here and I am within this data folder and we can see that we have our modified .', 'start': 529.244, 'duration': 7.402}, {'end': 538.446, 'text': 'xlsx file here.', 'start': 536.646, 'duration': 1.8}, {'end': 541.747, 'text': "Now I don't actually have Excel on this machine.", 'start': 538.926, 'duration': 2.821}, {'end': 542.567, 'text': 'I have Numbers.', 'start': 541.887, 'duration': 0.68}, {'end': 544.348, 'text': "So I'm going to open this up in Numbers.", 'start': 542.907, 'duration': 1.441}, {'end': 549.609, 'text': 'It should basically be the same on Windows, but you can just open it up with Excel.', 'start': 544.808, 'duration': 4.801}, {'end': 556.19, 'text': 'Now, again, this might take a second to open up because we do still have a lot of rows here in this data.', 'start': 550.149, 'duration': 6.041}, {'end': 561.872, 'text': "OK, so we've got this opened up in Excel.", 'start': 558.891, 'duration': 2.981}, {'end': 568.173, 'text': "Again, I'm on numbers because I'm on a Mac and I don't have Excel installed, but it should open up fine in Excel as well.", 'start': 562.352, 'duration': 5.821}, {'end': 573.635, 'text': 'Let me zoom in a little bit here so we can see and we can format these if we need to.', 'start': 568.193, 'duration': 5.442}], 'summary': 'Demonstration of opening .xlsx file in numbers and excel, with mention of difficulty opening in jupyter.', 'duration': 68.123, 'max_score': 505.512, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao505512.jpg'}, {'end': 678.927, 'src': 'embed', 'start': 631.479, 'weight': 0, 'content': [{'end': 640.881, 'text': 'If you Google this method name to Excel, then you can find the arguments that you can pass in and all the additional details in the documentation.', 'start': 631.479, 'duration': 9.402}, {'end': 650.584, 'text': "So for now, let's go ahead and move on and see how that we can read in that same Excel file that we just created and make sure that this works.", 'start': 641.381, 'duration': 9.203}, {'end': 657.247, 'text': "Now by default, it's going to load in with a default index just like when we read a CSV file.", 'start': 650.984, 'duration': 6.263}, {'end': 661.888, 'text': "So we'll have to specify that we want our index column to be that respondent column.", 'start': 657.687, 'duration': 4.201}, {'end': 669.731, 'text': "So to do that, I'm just going to call this test since we're going to be creating a new data frame here from that Excel file that we just created.", 'start': 662.308, 'duration': 7.423}, {'end': 674.198, 'text': "And we're going to use the read underscore Excel method here.", 'start': 670.271, 'duration': 3.927}, {'end': 678.927, 'text': "And now we just want to pass in the location and I'll just go ahead and copy this here.", 'start': 674.579, 'duration': 4.348}], 'summary': 'Using the read_excel method in python to read an excel file, specifying the index column as the respondent column.', 'duration': 47.448, 'max_score': 631.479, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao631479.jpg'}], 'start': 505.512, 'title': 'Excel file manipulation in jupyter and python', 'summary': 'Discusses opening, modifying, and reading excel files in jupyter and python, covering the use of numbers on mac, addressing the need for excel, formatting data, utilizing different sheets, advanced operations, specifying index column, and writing to a json file.', 'chapters': [{'end': 630.878, 'start': 505.512, 'title': 'Opening and modifying excel file in jupyter', 'summary': 'Discusses opening and modifying an excel file in jupyter using numbers on mac, addressing the need for excel, and formatting the data, with emphasis on the concept of different sheets and advanced operations in excel.', 'duration': 125.366, 'highlights': ['The chapter covers opening and modifying an Excel file in Jupyter, emphasizing the process of using Numbers on a Mac and the need for Excel on Windows (Mac vs Windows compatibility).', 'It also addresses the process of formatting the data in Excel, such as changing column sizes and ensuring proper display of data (data formatting techniques).', 'Furthermore, it mentions the concept of different sheets in Excel, allowing multiple spreadsheets in one file, and the ability to read or write to a specific sheet (working with multiple sheets in Excel).', 'The chapter briefly touches on more advanced operations in Excel, including starting from different columns and rows, and the use of specific arguments for reading or writing to a specific sheet (advanced Excel operations).']}, {'end': 841.1, 'start': 631.479, 'title': 'Reading and writing data in python', 'summary': 'Covers how to read in an excel file and specify the index column, then write the data frame to a json file using the to_json method, demonstrating the process and file formats.', 'duration': 209.621, 'highlights': ['Demonstrating how to read in an Excel file and specifying the index column', 'Writing the data frame to a JSON file using the to_json method']}], 'duration': 335.588, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao505512.jpg', 'highlights': ['Cover opening and modifying Excel in Jupyter, emphasizing Mac vs Windows compatibility', 'Address data formatting techniques in Excel, such as changing column sizes', 'Discuss working with multiple sheets in Excel, allowing multiple spreadsheets in one file', 'Demonstrate reading an Excel file and specifying the index column', 'Show how to write the data frame to a JSON file using the to_json method', 'Briefly touch on advanced operations in Excel, including starting from different columns and rows']}, {'end': 1155.875, 'segs': [{'end': 916.318, 'src': 'embed', 'start': 868.386, 'weight': 1, 'content': [{'end': 875.133, 'text': 'let me spell that right will just make each of these on a new line, so it might be a little bit easier to read.', 'start': 868.386, 'duration': 6.747}, {'end': 879.455, 'text': 'Now, if you want to see the exact arguments that you can pass into orient,', 'start': 875.693, 'duration': 3.762}, {'end': 887.419, 'text': "then again just look up pandas to JSON method and it'll take you to the documentation with all the different things that you can pass in here.", 'start': 879.455, 'duration': 7.964}, {'end': 895.704, 'text': "So let me run this and now let's go back and reload our JSON file to see how this looks.", 'start': 887.9, 'duration': 7.804}, {'end': 898.606, 'text': 'and now what we have here is more list.', 'start': 896.244, 'duration': 2.362}, {'end': 911.295, 'text': 'like so, before we had a single dictionary where the values were a list of all of the responses, but now we have one response at a time.', 'start': 898.606, 'duration': 12.689}, {'end': 916.318, 'text': 'so we have the main branch and then, uh so this is actually this first one here.', 'start': 911.295, 'duration': 5.023}], 'summary': 'Demonstrating pandas to json method for data transformation', 'duration': 47.932, 'max_score': 868.386, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao868386.jpg'}, {'end': 1098.281, 'src': 'embed', 'start': 1066.922, 'weight': 0, 'content': [{'end': 1068.804, 'text': "It's having trouble shutting down.", 'start': 1066.922, 'duration': 1.882}, {'end': 1075.648, 'text': 'Okay, so let me go back to the terminal that I have opened to where I can install some different packages.', 'start': 1069.724, 'duration': 5.924}, {'end': 1077.55, 'text': "And that's my Jupyter notebook.", 'start': 1076.249, 'duration': 1.301}, {'end': 1078.931, 'text': "Where's my other terminal? Here we go.", 'start': 1077.61, 'duration': 1.321}, {'end': 1083.473, 'text': "Okay, so to connect to our database, we're going to want to install SQLAlchemy.", 'start': 1079.571, 'duration': 3.902}, {'end': 1089.716, 'text': 'And this is a very popular ORM for Python that allows us to more easily work with databases.', 'start': 1083.893, 'duration': 5.823}, {'end': 1094.199, 'text': "If you don't know what an ORM is, it stands for Object Relational Mapper.", 'start': 1090.057, 'duration': 4.142}, {'end': 1098.281, 'text': "And it's just a way for us to use Python objects in order to connect to a database.", 'start': 1094.559, 'duration': 3.722}], 'summary': 'Trouble shutting down system. installing sqlalchemy for database connection.', 'duration': 31.359, 'max_score': 1066.922, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1066922.jpg'}], 'start': 841.1, 'title': 'Using pandas to json method and working with json and sql databases', 'summary': 'Demonstrates using pandas to json method to change the orient argument resulting in a list-like structure, and covers exporting and importing data to json files, specifying arguments for writing and reading json files, and connecting to a postgres database using sqlalchemy and psychopg2 to work with python objects.', 'chapters': [{'end': 934.049, 'start': 841.1, 'title': 'Pandas to json method', 'summary': 'Demonstrates how to use the pandas to json method to change the orient argument, resulting in a more list-like structure with one response at a time, making it easier to read, and each response within the survey is on a different line.', 'duration': 92.949, 'highlights': ['The chapter demonstrates how to use the pandas to JSON method to change the orient argument, resulting in a more list-like structure with one response at a time, making it easier to read.', 'Each response within the survey is on a different line.']}, {'end': 1155.875, 'start': 934.53, 'title': 'Working with json and sql databases', 'summary': 'Covers exporting and importing data to json files, including specifying arguments for writing and reading json files, as well as connecting to a postgres database using sqlalchemy and psychopg2 to work with python objects.', 'duration': 221.345, 'highlights': ['Connecting to a Postgres database using SQLAlchemy and PsychoPG2', 'Reading and writing data from SQL databases', 'Specifying arguments for writing and reading JSON files']}], 'duration': 314.775, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao841100.jpg', 'highlights': ['Connecting to a Postgres database using SQLAlchemy and PsychoPG2', 'Specifying arguments for writing and reading JSON files', 'The chapter demonstrates how to use the pandas to JSON method to change the orient argument, resulting in a more list-like structure with one response at a time, making it easier to read.', 'Each response within the survey is on a different line.']}, {'end': 1393.491, 'segs': [{'end': 1203.247, 'src': 'embed', 'start': 1156.575, 'weight': 1, 'content': [{'end': 1159.938, 'text': "So first, we're going to want to import everything that we need.", 'start': 1156.575, 'duration': 3.363}, {'end': 1168.305, 'text': "So from SQLAlchemy, I'm going to want to import their create engine.", 'start': 1160.378, 'duration': 7.927}, {'end': 1172.487, 'text': 'And this will allow us to connect to the database.', 'start': 1169.425, 'duration': 3.062}, {'end': 1177.47, 'text': "Now I'm also going to want to import PsychoPG2.", 'start': 1172.847, 'duration': 4.623}, {'end': 1178.931, 'text': 'So let me run this cell.', 'start': 1177.87, 'duration': 1.061}, {'end': 1184.674, 'text': 'And now that those are imported, we should be able to create the engine, which is basically our database connection.', 'start': 1179.431, 'duration': 5.243}, {'end': 1189.997, 'text': "And again, I'm going to assume that you've already created this database and have a username and password.", 'start': 1185.174, 'duration': 4.823}, {'end': 1199.143, 'text': 'So to create this, I can say engine is equal to and use that create engine function that we just imported from SQL Alchemy.', 'start': 1190.457, 'duration': 8.686}, {'end': 1203.247, 'text': 'And now we need our Postgres connection string.', 'start': 1199.844, 'duration': 3.403}], 'summary': 'Imported sqlalchemy and psychopg2 to create a postgres connection string.', 'duration': 46.672, 'max_score': 1156.575, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1156575.jpg'}, {'end': 1256.157, 'src': 'embed', 'start': 1231.162, 'weight': 0, 'content': [{'end': 1239.027, 'text': "Now another thing here that I'd like to mention is that you probably shouldn't put credentials within code like this.", 'start': 1231.162, 'duration': 7.865}, {'end': 1243.29, 'text': "I'll leave a link in the description section below where I show how.", 'start': 1239.807, 'duration': 3.483}, {'end': 1248.893, 'text': 'in Python, you should use something like environment variables or a config file to hide this information.', 'start': 1243.29, 'duration': 5.603}, {'end': 1252.115, 'text': "But for the sake of this tutorial, I'm just going to put it directly in here.", 'start': 1249.413, 'duration': 2.702}, {'end': 1256.157, 'text': "But if you're doing this in production code,", 'start': 1252.835, 'duration': 3.322}], 'summary': 'Avoid putting credentials in code; use environment variables or config files instead.', 'duration': 24.995, 'max_score': 1231.162, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1231162.jpg'}, {'end': 1304.32, 'src': 'embed', 'start': 1273.285, 'weight': 3, 'content': [{'end': 1275.247, 'text': 'And now the name of the database.', 'start': 1273.285, 'duration': 1.962}, {'end': 1287.692, 'text': "Now I have pgadmin open here where I can see my databases and we can see that I've just created an empty database here called sample underscore DB.", 'start': 1278.248, 'duration': 9.444}, {'end': 1291.673, 'text': "So that is the database that I'm going to connect to.", 'start': 1288.272, 'duration': 3.401}, {'end': 1299.256, 'text': 'Okay, so if I typed everything correctly here, then I should be able to get a connection to that database.', 'start': 1292.333, 'duration': 6.923}, {'end': 1304.32, 'text': "So now let's try to write our modified data frame to a table in this database.", 'start': 1299.696, 'duration': 4.624}], 'summary': 'Created an empty database called sample_db and connected to it.', 'duration': 31.035, 'max_score': 1273.285, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1273285.jpg'}], 'start': 1156.575, 'title': 'Database connection and data transfer', 'summary': 'Covers setting up a database connection using sqlalchemy and psychopg2, emphasizing the creation of an engine and the need for a postgres connection string. it also demonstrates how to connect a pandas dataframe to a postgresql database, providing guidance on creating a connection string, handling credentials securely, specifying the database and table, and verifying the data transfer, with emphasis on the importance of securing credentials and successful data write to the database.', 'chapters': [{'end': 1203.247, 'start': 1156.575, 'title': 'Database connection setup', 'summary': 'Introduces the process of setting up a database connection using sqlalchemy and psychopg2, emphasizing the creation of an engine for database connection and the need for a postgres connection string.', 'duration': 46.672, 'highlights': ['The process begins with importing necessary modules such as create engine from SQLAlchemy and PsychoPG2 for database connection.', 'The creation of an engine using the imported function from SQLAlchemy is highlighted as the key step in establishing the database connection.', 'Emphasizes the requirement of a Postgres connection string and assumes the existence of a pre-created database, username, and password.']}, {'end': 1393.491, 'start': 1204.028, 'title': 'Connecting pandas dataframe to postgresql', 'summary': 'Demonstrates how to connect a pandas dataframe to a postgresql database, providing guidance on creating a connection string, handling credentials securely, specifying the database and table, and verifying the data transfer, with emphasis on the importance of securing credentials and the successful data write to the database.', 'duration': 189.463, 'highlights': ['The importance of not putting credentials within code and using environment variables or a config file for security, is emphasized, with a link provided for further guidance.', 'Guidance is given on specifying the database and table to write the data to, demonstrating the process of connecting a Pandas dataframe to a PostgreSQL database.', 'The successful transfer of data from Pandas to SQL is verified, with the demonstration of checking the table in the PG admin tool, ensuring the data was written into the database.']}], 'duration': 236.916, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1156575.jpg', 'highlights': ['The creation of an engine using SQLAlchemy is key for database connection', 'Emphasizes the requirement of a Postgres connection string and pre-created database', 'Guidance on securing credentials using environment variables or a config file', 'Demonstrates connecting a Pandas dataframe to a PostgreSQL database', 'Verification of successful data transfer from Pandas to SQL']}, {'end': 1780.826, 'segs': [{'end': 1418.764, 'src': 'embed', 'start': 1394.271, 'weight': 0, 'content': [{'end': 1400.115, 'text': 'But now what if we updated our data and wanted to rewrite that data to this database?', 'start': 1394.271, 'duration': 5.844}, {'end': 1403.718, 'text': "So let's go back to our notebook and see what this would look like.", 'start': 1400.476, 'duration': 3.242}, {'end': 1413.542, 'text': "Now, if I try to run the same line again, where we export this to SQL, then we're actually going to get it in error because this table already exists.", 'start': 1404.258, 'duration': 9.284}, {'end': 1418.764, 'text': 'If you want to write over a table, then we can add in an additional argument.', 'start': 1414.022, 'duration': 4.742}], 'summary': 'Updating data requires handling existing table error and adding additional arguments to write over a table.', 'duration': 24.493, 'max_score': 1394.271, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1394271.jpg'}, {'end': 1527.599, 'src': 'embed', 'start': 1517.893, 'weight': 2, 'content': [{'end': 1521.375, 'text': "because we're going to reuse that same connection to read in our data.", 'start': 1517.893, 'duration': 3.482}, {'end': 1523.616, 'text': 'okay. so this is pretty simple.', 'start': 1521.915, 'duration': 1.701}, {'end': 1527.599, 'text': 'now that we actually have this database connection set up to do this,', 'start': 1523.616, 'duration': 3.983}], 'summary': 'Setting up database connection for data reading and reuse.', 'duration': 9.706, 'max_score': 1517.893, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1517893.jpg'}, {'end': 1584.062, 'src': 'embed', 'start': 1558.062, 'weight': 1, 'content': [{'end': 1565.328, 'text': "So I'll say index column is equal to, and that is going to be this respondent row right here.", 'start': 1558.062, 'duration': 7.266}, {'end': 1567.49, 'text': 'For your data, that might be different.', 'start': 1565.948, 'duration': 1.542}, {'end': 1569.972, 'text': 'So whatever you want to be your index, just pass it in there.', 'start': 1567.77, 'duration': 2.202}, {'end': 1575.357, 'text': 'If you want pandas to just do a default index, then you can just leave this off entirely.', 'start': 1570.412, 'duration': 4.945}, {'end': 1584.062, 'text': "Okay, so if I run this, then let's look at SQL df.head to make sure this worked.", 'start': 1575.917, 'duration': 8.145}], 'summary': 'Setting index column for respondent row in pandas dataframe.', 'duration': 26, 'max_score': 1558.062, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1558062.jpg'}, {'end': 1633.57, 'src': 'embed', 'start': 1603.554, 'weight': 6, 'content': [{'end': 1611.56, 'text': 'To do this, we can use the method read underscore SQL underscore query to run a specific SQL query.', 'start': 1603.554, 'duration': 8.006}, {'end': 1615.643, 'text': 'So let me just copy what I did here.', 'start': 1612.3, 'duration': 3.343}, {'end': 1623.706, 'text': "and paste this down here and now, instead of reading in this entire table, i'm going to actually run a query here.", 'start': 1616.483, 'duration': 7.223}, {'end': 1633.57, 'text': "so i'll do read underscore sql, underscore query, and now, instead of the table name here, i'm actually going to pass in a sql query.", 'start': 1623.706, 'duration': 9.864}], 'summary': 'Using read_sql_query to run a specific sql query.', 'duration': 30.016, 'max_score': 1603.554, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1603554.jpg'}], 'start': 1394.271, 'title': 'Managing sql data', 'summary': 'Covers updating data in an sql database, addressing table existence errors, and provides options for replacing, throwing errors, or appending data. it also demonstrates loading and reading data in sql and pandas, including replacing data in existing tables, reading data from a database using sql, and loading data from a url using pandas, with practical examples and step-by-step explanations.', 'chapters': [{'end': 1443.928, 'start': 1394.271, 'title': 'Updating data in sql database', 'summary': 'Discusses the process of updating data in an sql database, including handling table existence errors, with options to replace, throw error, or append data, to ensure efficient data management.', 'duration': 49.657, 'highlights': ['The chapter discusses the process of updating data in an SQL database, including handling table existence errors, with options to replace, throw error, or append data, to ensure efficient data management.', "The speaker mentions adding an additional argument 'if_exists' when rewriting data to an existing table in the SQL database.", 'The speaker explains that the default behavior when trying to overwrite an existing table in SQL database is to throw an error.', 'The speaker also mentions other options like appending data to a table, providing flexibility in data management.']}, {'end': 1780.826, 'start': 1444.128, 'title': 'Loading and reading data in sql and pandas', 'summary': 'Demonstrates how to load and read data in sql and pandas, including replacing data in an existing table, reading data from a database using sql, and loading data from a url using pandas, with practical examples and step-by-step explanations.', 'duration': 336.698, 'highlights': ['The chapter demonstrates how to load and read data in SQL and Pandas, including replacing data in an existing table.', 'Reading data from a database using SQL.', 'Loading data from a URL using Pandas.']}], 'duration': 386.555, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1394271.jpg', 'highlights': ['The chapter covers updating data in an SQL database, including handling table existence errors, with options to replace, throw error, or append data.', 'The chapter demonstrates loading and reading data in SQL and Pandas, including replacing data in an existing table.', "The speaker mentions adding an additional argument 'if_exists' when rewriting data to an existing table in the SQL database.", 'The default behavior when trying to overwrite an existing table in SQL database is to throw an error.', 'The speaker also mentions other options like appending data to a table, providing flexibility in data management.', 'Reading data from a database using SQL.', 'Loading data from a URL using Pandas.']}, {'end': 1952.214, 'segs': [{'end': 1814.172, 'src': 'embed', 'start': 1781.487, 'weight': 0, 'content': [{'end': 1784.608, 'text': 'And we can see that I do have my sample posts here.', 'start': 1781.487, 'duration': 3.121}, {'end': 1785.909, 'text': 'These are the sample posts.', 'start': 1784.648, 'duration': 1.261}, {'end': 1790.112, 'text': 'that I used on that website series.', 'start': 1786.429, 'duration': 3.683}, {'end': 1798.459, 'text': "so, depending on the data in that URL, you should be able to use the methods that we've seen to load in data from a URL, just like we did here.", 'start': 1790.112, 'duration': 8.347}, {'end': 1803.423, 'text': 'now, before we end here, I would like to thank the sponsor of this video, and that is Brilliant.', 'start': 1798.459, 'duration': 4.964}, {'end': 1807.787, 'text': 'I really enjoy the tutorials that Brilliant provides and would definitely recommend checking them out.', 'start': 1803.423, 'duration': 4.364}, {'end': 1814.172, 'text': 'Brilliant is a problem solving website that helps you understand underlying concepts by actively working through guided lessons.', 'start': 1808.487, 'duration': 5.685}], 'summary': 'Demonstrates loading data from a url and mentions brilliant as sponsor.', 'duration': 32.685, 'max_score': 1781.487, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1781487.jpg'}, {'end': 1846.034, 'src': 'embed', 'start': 1819.157, 'weight': 2, 'content': [{'end': 1825.363, 'text': 'They have some excellent courses and lessons on data science that do a deep dive on how to think about and analyze data correctly.', 'start': 1819.157, 'duration': 6.206}, {'end': 1829.485, 'text': "so if you're watching my panda series because you're getting into the data science field,", 'start': 1825.843, 'duration': 3.642}, {'end': 1834.968, 'text': 'then i would highly recommend also checking out brilliant and seeing what other data science skills you can learn.', 'start': 1829.485, 'duration': 5.483}, {'end': 1840.971, 'text': 'they even use python in their statistics course and will quiz you on how to correctly analyze the data within the language.', 'start': 1834.968, 'duration': 6.003}, {'end': 1846.034, 'text': "their guided lessons will challenge you, but you'll also have the ability to get hints or even solutions if you need them.", 'start': 1840.971, 'duration': 5.063}], 'summary': 'Brilliant offers data science courses with python, challenging guided lessons, and assistance for learners.', 'duration': 26.877, 'max_score': 1819.157, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1819157.jpg'}, {'end': 1926.297, 'src': 'heatmap', 'start': 1885.214, 'weight': 0.887, 'content': [{'end': 1891.839, 'text': "Now, I'm probably going to take a break from this Pandas series after this video and do a few one-off videos that I've been wanting to cover.", 'start': 1885.214, 'duration': 6.625}, {'end': 1895.642, 'text': 'But I know that there are a lot of topics in Pandas left to cover.', 'start': 1892.199, 'duration': 3.443}, {'end': 1899.183, 'text': 'And I will get around to those more advanced topics in future videos.', 'start': 1896.002, 'duration': 3.181}, {'end': 1906.507, 'text': "But in the meantime, if you'd like a good source for learning pandas, then I would highly recommend checking out the channel Data School.", 'start': 1899.564, 'duration': 6.943}, {'end': 1911.709, 'text': "That's run by Kevin Markham, and he's done the pandas tutorials at PyCon for several years now.", 'start': 1906.547, 'duration': 5.162}, {'end': 1915.49, 'text': "Now, he didn't ask me to suggest his channel or anything like that.", 'start': 1912.089, 'duration': 3.401}, {'end': 1917.751, 'text': 'I just think that he does a good job.', 'start': 1915.851, 'duration': 1.9}, {'end': 1921.293, 'text': 'And his channel is actually completely devoted to pandas and data science.', 'start': 1918.132, 'duration': 3.161}, {'end': 1926.297, 'text': "So he's already covered some of the more advanced topics that I do plan to cover in future videos.", 'start': 1921.773, 'duration': 4.524}], 'summary': 'The transcript discusses taking a break from pandas series, with a recommendation for data school channel for learning pandas and advanced topics.', 'duration': 41.083, 'max_score': 1885.214, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1885214.jpg'}], 'start': 1781.487, 'title': 'Pandas tutorial summary', 'summary': 'Covers methods for loading data from a url, recommends brilliant for data science courses, and suggests further learning resources. it also mentions data school as a good source for learning pandas.', 'chapters': [{'end': 1952.214, 'start': 1781.487, 'title': 'Pandas tutorial summary', 'summary': 'Covers methods for loading data from a url, recommends brilliant for data science courses, and suggests further learning resources. it also mentions data school as a good source for learning pandas.', 'duration': 170.727, 'highlights': ['Brilliant offers 20% off annual premium subscription for the first 200 sign-ups through the provided link.', 'Recommendation to check out Brilliant for data science skills and courses.', 'Suggestion to check out Data School for learning pandas, run by Kevin Markham.']}], 'duration': 170.727, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/N6hyN6BW6ao/pics/N6hyN6BW6ao1781487.jpg', 'highlights': ['Brilliant offers 20% off annual premium subscription for the first 200 sign-ups through the provided link.', 'Recommendation to check out Brilliant for data science skills and courses.', 'Suggestion to check out Data School for learning pandas, run by Kevin Markham.']}], 'highlights': ['The series is sponsored by Brilliant, with a call to action for the audience.', 'Demonstrates connecting a Pandas dataframe to a PostgreSQL database', 'The chapter covers updating data in an SQL database, including handling table existence errors, with options to replace, throw error, or append data.', 'The chapter demonstrates loading and reading data in SQL and Pandas, including replacing data in an existing table.', "The method to_csv() is used to export the filtered data to a CSV file named 'modified.csv' in a specified location within the data directory.", 'Connecting to a Postgres database using SQLAlchemy and PsychoPG2', 'The creation of an engine using SQLAlchemy is key for database connection', 'The chapter details the installation of XLWT, OpenPyXL, and XLRD packages for writing and reading Excel files in pandas.', 'The video covers reading and writing data using CSV, Excel, JSON, and SQL databases.', 'By the end, the audience should handle data transfer to/from pandas regardless of format.', 'The chapter covers reading and writing CSV files in Pandas.', 'The chapter demonstrates how to use the pandas to JSON method to change the orient argument, resulting in a more list-like structure with one response at a time, making it easier to read.', "The speaker mentions adding an additional argument 'if_exists' when rewriting data to an existing table in the SQL database.", 'The default behavior when trying to overwrite an existing table in SQL database is to throw an error.', "The chapter demonstrates filtering a DataFrame using df.loc to include only 'India' in the country column.", 'The speaker also mentions other options like appending data to a table, providing flexibility in data management.']}