title
Python Pandas Tutorial 4: Read Write Excel CSV File
description
This tutorial covers how to read/write excel and csv files in pandas. We will cover,
1) Different options on cleaning up messy data while reading csv/excel files
2) Use convertors to transform data read from excel file
3) Export only portion of dataframe to excel file
Topics that are covered in this Python Pandas Video:
0:00 Introduction
1:26 Read CSV file using read_csv() method
2:39 Skip rows in dataframe using "skiprows"
4:44 Import data from CSV file with "null header"
6:28 Read limited data from CSV file
7:19 Clean up messy data from file "not available" and "n.a." replace with "na_values"
9:01 Supply dictionary for replace with "na_values"
11:40 Write dataframe into "csv" file with "to_csv() method"
15:27 Read excel file using read_excel() method
18:03 Converters argument in read_excel() method
20:17 Write dataframe into "excel" file with "to_excel() method"
22:56 Use ExcelWritter() class
25:13 All properties for Read Write Excel CSV File
Do you want to learn technology from me? Check https://codebasics.io/?utm_source=description&utm_medium=yt&utm_campaign=description&utm_id=description for my affordable video courses.
Very Simple Explanation Of Neural Network: https://www.youtube.com/watch?v=ER2It2mIagI
Code (jupyter notebook link): https://github.com/codebasics/py/tree/master/pandas/4_read_write_to_excel
Next Video:
Python Pandas Tutorial 5: Handle Missing Data: fillna, dropna, interpolate: https://www.youtube.com/watch?v=EaGbS7eWSs0&list=PLeo1K3hjS3uuASpe-1LjfG5f14Bnozjwy&index=5
Popular Playlist:
Complete python course: https://www.youtube.com/playlist?list=PLeo1K3hjS3uv5U-Lmlnucd7gqF-3ehIh0
Data science course: https://www.youtube.com/playlist?list=PLeo1K3hjS3us_ELKYSj_Fth2tIEkdKXvV
Machine learning tutorials: https://www.youtube.com/playlist?list=PLeo1K3hjS3uvCeTYTeyfe0-rN5r8zn9rw
Pandas tutorials: https://www.youtube.com/playlist?list=PLeo1K3hjS3uuASpe-1LjfG5f14Bnozjwy
Git github tutorials: https://www.youtube.com/playlist?list=PLeo1K3hjS3usJuxZZUBdjAcilgfQHkRzW
Matplotlib course: https://www.youtube.com/playlist?list=PLeo1K3hjS3uu4Lr8_kro2AqaO6CFYgKOl
Data structures course: https://www.youtube.com/playlist?list=PLeo1K3hjS3uu_n_a__MI_KktGTLYopZ12
Data Science Project - Real Estate Price Prediction: https://www.youtube.com/watch?v=rdfbcdP75KI&list=PLeo1K3hjS3uu7clOTtwsp94PcHbzqpAdg
To download csv and code for all tutorials: go to https://github.com/codebasics/py, click on a green button to clone or download the entire repository and then go to relevant folder to get access to that specific file.
🌎 My Website For Video Courses: https://codebasics.io/?utm_source=description&utm_medium=yt&utm_campaign=description&utm_id=description
Need help building software or data analytics and AI solutions? My company https://www.atliq.com/ can help. Click on the Contact button on that website.
#️⃣ Social Media #️⃣
🔗 Discord: https://discord.gg/r42Kbuk
📸 Dhaval's Personal Instagram: https://www.instagram.com/dhavalsays/
📸 Codebasics Instagram: https://www.instagram.com/codebasicshub/
🔊 Facebook: https://www.facebook.com/codebasicshub
📱 Twitter: https://twitter.com/codebasicshub
📝 Linkedin (Personal): https://www.linkedin.com/in/dhavalsays/
📝 Linkedin (Codebasics): https://www.linkedin.com/company/codebasics/
🔗 Patreon: https://www.patreon.com/codebasics?fan_landing=true
detail
{'title': 'Python Pandas Tutorial 4: Read Write Excel CSV File', 'heatmap': [{'end': 284.583, 'start': 235.851, 'weight': 0.729}, {'end': 772.236, 'start': 746.143, 'weight': 0.726}, {'end': 906.582, 'start': 850.412, 'weight': 0.702}, {'end': 1285.81, 'start': 1263.105, 'weight': 0.951}, {'end': 1348.672, 'start': 1292.274, 'weight': 0.713}, {'end': 1396.355, 'start': 1375.88, 'weight': 0.775}], 'summary': 'This tutorial explores reading and writing csv and excel files with pandas, covering topics such as handling extra headers, cleaning up messy data, converting negative values to nan, and writing dataframes to csv and excel files.', 'chapters': [{'end': 70.028, 'segs': [{'end': 30.12, 'src': 'embed', 'start': 0.47, 'weight': 0, 'content': [{'end': 6.641, 'text': 'Dear friends, in this tutorial we are going to look into reading and writing CSV and Excel files in pandas,', 'start': 0.47, 'duration': 6.171}, {'end': 9.587, 'text': 'and here is the list of items we are covering in this video.', 'start': 6.641, 'duration': 2.946}, {'end': 13.45, 'text': "okay, let's get started.", 'start': 11.269, 'duration': 2.181}, {'end': 17.713, 'text': 'what we are going to do first is start a Jupyter notebook.', 'start': 13.45, 'duration': 4.263}, {'end': 23.076, 'text': 'I prefer Jupyter notebook because it is great with data visualization.', 'start': 17.713, 'duration': 5.363}, {'end': 30.12, 'text': "you can use whatever IDE you prefer pycharm, not pet plus plus it really doesn't matter.", 'start': 23.076, 'duration': 7.044}], 'summary': 'Tutorial on reading/writing csv and excel in pandas, covering list of items and jupyter notebook preference.', 'duration': 29.65, 'max_score': 0.47, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ470.jpg'}, {'end': 82.378, 'src': 'embed', 'start': 53.218, 'weight': 1, 'content': [{'end': 58.681, 'text': 'I have tickers, earning per share, revenue, price, and the people associated with that company.', 'start': 53.218, 'duration': 5.463}, {'end': 62.923, 'text': 'CSV file is nothing but a comma-separated value file.', 'start': 59.281, 'duration': 3.642}, {'end': 70.028, 'text': 'So if you open it in plain Notepad++, it looks something like this, where you have values separated by comma.', 'start': 62.943, 'duration': 7.085}, {'end': 82.378, 'text': 'these files are lot similar to excel files, so whatever methods and properties we are going to cover, they pretty much apply to excel files as well.', 'start': 71.155, 'duration': 11.223}], 'summary': 'Transcript covers tickers, earnings per share, revenue, and price in csv files, similar to excel.', 'duration': 29.16, 'max_score': 53.218, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ53218.jpg'}], 'start': 0.47, 'title': 'Pandas: reading and writing csv and excel files', 'summary': 'Covers the process of reading a csv file into a pandas data frame and the structure of a csv file in pandas.', 'chapters': [{'end': 70.028, 'start': 0.47, 'title': 'Pandas: reading and writing csv and excel files', 'summary': 'Covers reading and writing csv and excel files in pandas, including the process of reading a csv file into a pandas data frame and the structure of a csv file.', 'duration': 69.558, 'highlights': ['The chapter covers reading and writing CSV and Excel files in pandas, including the process of reading a CSV file into a Pandas data frame.', 'The CSV file contains stock market data with tickers, earning per share, revenue, price, and associated people.', 'The tutorial starts with the process of starting a Jupyter notebook and creating a new notebook by selecting new and python.']}], 'duration': 69.558, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ470.jpg', 'highlights': ['The chapter covers reading and writing CSV and Excel files in pandas, including the process of reading a CSV file into a Pandas data frame.', 'The CSV file contains stock market data with tickers, earning per share, revenue, price, and associated people.', 'The tutorial starts with the process of starting a Jupyter notebook and creating a new notebook by selecting new and python.']}, {'end': 545.892, 'segs': [{'end': 106.708, 'src': 'embed', 'start': 71.155, 'weight': 0, 'content': [{'end': 82.378, 'text': 'these files are lot similar to excel files, so whatever methods and properties we are going to cover, they pretty much apply to excel files as well.', 'start': 71.155, 'duration': 11.223}, {'end': 96.942, 'text': "okay. so let's begin by importing pandas as pd, as usual, and then you will create a data frame by saying df equal to pd, dot read csv,", 'start': 82.378, 'duration': 14.564}, {'end': 106.708, 'text': 'And in the bracket you supply the name of the CSV file, which is stock underscore data dot CSV.', 'start': 98.001, 'duration': 8.707}], 'summary': "Pandas methods and properties apply to similar excel files. import pandas as pd, create data frame df=pd.read_csv('stock_data.csv').", 'duration': 35.553, 'max_score': 71.155, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ71155.jpg'}, {'end': 299.811, 'src': 'heatmap', 'start': 235.851, 'weight': 1, 'content': [{'end': 244.073, 'text': 'so if you look at here and if you search for skip, so here it says skip rows.', 'start': 235.851, 'duration': 8.222}, {'end': 247.094, 'text': 'okay. so I provided underscore, which was wrong.', 'start': 244.073, 'duration': 3.021}, {'end': 256.584, 'text': 'so now you can see that it skipped this first line and started reading from second line.', 'start': 249.423, 'duration': 7.161}, {'end': 264.866, 'text': 'you can also do the same thing by saying header equal to one, and it will produce the same output.', 'start': 256.584, 'duration': 8.282}, {'end': 271.008, 'text': "so it's saying that my header is located at row number one.", 'start': 264.866, 'duration': 6.142}, {'end': 272.408, 'text': 'okay, all right.', 'start': 271.008, 'duration': 1.4}, {'end': 284.583, 'text': "Now we can have a reverse situation, so that instead of having this, let's say, extra header, you don't have this header at all.", 'start': 273.797, 'duration': 10.786}, {'end': 292.987, 'text': "So let's say your CSV file is something like this where you don't have header, but you somehow know that this is ticker, this is EPS and so on.", 'start': 284.803, 'duration': 8.184}, {'end': 299.811, 'text': 'So what you want to do in that case is you want to say header is equal to none.', 'start': 293.728, 'duration': 6.083}], 'summary': "Demonstrates skipping rows and specifying headers in reading csv files, including the use of 'skip rows' and 'header equal to' options.", 'duration': 63.96, 'max_score': 235.851, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ235851.jpg'}, {'end': 444.366, 'src': 'embed', 'start': 411.694, 'weight': 3, 'content': [{'end': 424.743, 'text': 'so n rows means number of rows that you want to read into a data frame, and when I say 3, it will exactly read 3 rows, excluding your header.', 'start': 411.694, 'duration': 13.049}, {'end': 435.341, 'text': 'okay, so sometimes, if you have millions of live lines in your CSV or excel files and you want to limit How many lines you want to read,', 'start': 424.743, 'duration': 10.598}, {'end': 438.843, 'text': 'then you you can use n rows argument.', 'start': 435.341, 'duration': 3.502}, {'end': 444.366, 'text': 'okay?. The next use useful argument is any values.', 'start': 438.843, 'duration': 5.523}], 'summary': 'N rows specifies the number of rows to read, useful for limiting large files.', 'duration': 32.672, 'max_score': 411.694, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ411694.jpg'}, {'end': 545.892, 'src': 'embed', 'start': 519.129, 'weight': 4, 'content': [{'end': 527.516, 'text': 'So if you look at this file here, this, this, and this, these three cells are now replaced by nan.', 'start': 519.129, 'duration': 8.387}, {'end': 541.768, 'text': 'okay, so this is useful in cleaning up the cleaning up the messy data that you get from your CSV or excel file.', 'start': 528.357, 'duration': 13.411}, {'end': 545.892, 'text': 'okay, so we took care of not available and any values.', 'start': 541.768, 'duration': 4.124}], 'summary': "Three cells replaced by 'nan' to clean up messy csv/excel data.", 'duration': 26.763, 'max_score': 519.129, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ519129.jpg'}], 'start': 71.155, 'title': 'Reading csv files with pandas', 'summary': "Covers reading csv files into a data frame using pandas, including importing, printing, handling extra headers, missing headers, limiting rows, and cleaning up messy data with 'n rows', 'header', 'skip rows', 'header equal to none', 'names', and 'any values' arguments.", 'chapters': [{'end': 545.892, 'start': 71.155, 'title': 'Reading csv files with pandas', 'summary': "Covers reading csv files into a data frame using pandas, including importing, printing, handling extra headers, missing headers, limiting rows, and cleaning up messy data with 'n rows', 'header', 'skip rows', 'header equal to none', 'names', and 'any values' arguments.", 'duration': 474.737, 'highlights': ["Reading CSV file into a data frame Explains the process of importing and creating a data frame from a CSV file using pandas, highlighting the method 'pd.read_csv' and printing the data frame.", "Handling extra headers in CSV file Demonstrates how to skip extra headers in a CSV file by using 'skip rows' and 'header' arguments, with the examples of skipping a row and specifying the header location.", "Dealing with missing headers in CSV file Illustrates the method to handle missing headers in a CSV file by using 'header equal to none' and supplying column names using the 'names' argument, emphasizing the need to use these when encountering a missing header.", "Limiting rows while reading CSV file Describes how to limit the number of rows read from a large CSV file using the 'n rows' argument, providing an example of reading only three rows and highlighting the usefulness of this method for handling large CSV or excel files.", "Cleaning up messy data with 'any values' argument Explains the usage of 'any values' argument to replace problematic cells with 'not available' and 'n.a.' with 'nan' values, showcasing how this method helps in cleaning up messy data obtained from CSV or excel files."]}], 'duration': 474.737, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ71155.jpg', 'highlights': ["Covers reading csv files into a data frame using pandas, including importing and printing data frame with 'pd.read_csv'.", "Handling extra headers in CSV file by using 'skip rows' and 'header' arguments.", "Dealing with missing headers in CSV file using 'header equal to none' and supplying column names with 'names' argument.", "Limiting rows while reading CSV file with 'n rows' argument, useful for handling large CSV or excel files.", "Cleaning up messy data with 'any values' argument to replace problematic cells with 'not available' and 'n.a.' with 'nan' values."]}, {'end': 888.512, 'segs': [{'end': 642.228, 'src': 'embed', 'start': 599.817, 'weight': 1, 'content': [{'end': 606.542, 'text': 'well, in that case, instead of supplying a list, you can supply a Dictionary.', 'start': 599.817, 'duration': 6.725}, {'end': 619.887, 'text': 'so what you can do is you can supply dictionary and you can say this you can say that in my earning per share column,', 'start': 606.542, 'duration': 13.345}, {'end': 628.331, 'text': 'whenever you find n dot a or not available, convert that to an N.', 'start': 619.887, 'duration': 8.444}, {'end': 638.255, 'text': 'okay, but in my revenue column you can convert both of these things to.', 'start': 628.331, 'duration': 9.924}, {'end': 642.228, 'text': 'let me copy-paste.', 'start': 641.387, 'duration': 0.841}], 'summary': "Using a dictionary to convert 'n dot a' or 'not available' to 'n' in earning per share column and in revenue column.", 'duration': 42.411, 'max_score': 599.817, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ599817.jpg'}, {'end': 745.543, 'src': 'embed', 'start': 667.314, 'weight': 0, 'content': [{'end': 683.584, 'text': 'both are, and when you run this you will notice that minus one still stayed as minus one and my revenue, which was negative here,', 'start': 667.314, 'duration': 16.27}, {'end': 685.666, 'text': 'got converted to NaN.', 'start': 683.584, 'duration': 2.082}, {'end': 688.108, 'text': 'So this is a pretty powerful feature.', 'start': 686.046, 'duration': 2.062}, {'end': 694.133, 'text': 'This Na underscore values could be useful in cleaning up your messy data.', 'start': 688.128, 'duration': 6.005}, {'end': 698.817, 'text': 'And this allows you to do data munging or data wrangling.', 'start': 694.413, 'duration': 4.404}, {'end': 703.801, 'text': 'alright, so that was about read CSV.', 'start': 700.598, 'duration': 3.203}, {'end': 709.485, 'text': 'now, how about writing back to CSV?', 'start': 703.801, 'duration': 5.684}, {'end': 722.227, 'text': 'so for writing back to CSV, what you can do is so I have already this data frame with me whenever I want to write this back to CSV.', 'start': 709.485, 'duration': 12.742}, {'end': 727.031, 'text': 'the way I have read CSV file, there is a to CSV file.', 'start': 722.227, 'duration': 4.804}, {'end': 733.197, 'text': 'so I have to CSV file where I can say new dot CSV.', 'start': 727.031, 'duration': 6.166}, {'end': 742.562, 'text': 'so I am writing this particular data frame, which is this guy here, to a file called new.csv.', 'start': 733.197, 'duration': 9.365}, {'end': 745.543, 'text': 'And when I run this, it executed fine.', 'start': 742.842, 'duration': 2.701}], 'summary': 'Using read_csv and to_csv functions to handle messy data, converting negative revenue to nan, and successfully writing data to a new csv file.', 'duration': 78.229, 'max_score': 667.314, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ667314.jpg'}, {'end': 772.236, 'src': 'heatmap', 'start': 746.143, 'weight': 0.726, 'content': [{'end': 752.105, 'text': 'So if I go to my directory, I will find this new.csv file.', 'start': 746.143, 'duration': 5.962}, {'end': 756.847, 'text': 'And when I open it, okay, let me minimize this file.', 'start': 752.746, 'duration': 4.101}, {'end': 763.31, 'text': 'When I open it, you can see that it wrote this CSV file.', 'start': 757.748, 'duration': 5.562}, {'end': 772.236, 'text': 'Okay now, the one observation I have with this file is it wrote this index also.', 'start': 763.53, 'duration': 8.706}], 'summary': 'A new.csv file was created in the directory, with an index included.', 'duration': 26.093, 'max_score': 746.143, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ746143.jpg'}], 'start': 545.892, 'title': 'Dataframe cleaning and csv writing', 'summary': 'Covers cleaning up messy data by converting negative values to nan, using dictionary to handle specific column conversions, and writing dataframes to csv files with examples and options like excluding the index and specific columns.', 'chapters': [{'end': 888.512, 'start': 545.892, 'title': 'Dataframe cleaning and csv writing', 'summary': 'Covers cleaning up messy data by converting negative values to nan, using dictionary to handle specific column conversions, and writing dataframes to csv files with examples and options like excluding the index and specific columns.', 'duration': 342.62, 'highlights': ['Converting negative values to NaN The chapter explains converting negative values in the dataframe to NaN, ensuring revenue column cannot be negative, and the powerful feature of Na underscore values in cleaning up messy data.', 'Using Dictionary to handle specific column conversions Demonstrates using a Dictionary to specify column conversions, ensuring specific columns like revenue and EPS are handled differently, and converting negative revenue values to NaN while preserving negative earning per share values.', "Writing dataframes to CSV files with options Covers writing dataframes to CSV files, including examples of using 'to_csv' function, excluding the default index, selectively exporting specific columns, and handling scenarios where all columns do not need to be exported."]}], 'duration': 342.62, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ545892.jpg', 'highlights': ['Converting negative values to NaN The chapter explains converting negative values in the dataframe to NaN, ensuring revenue column cannot be negative, and the powerful feature of Na underscore values in cleaning up messy data.', 'Using Dictionary to handle specific column conversions Demonstrates using a Dictionary to specify column conversions, ensuring specific columns like revenue and EPS are handled differently, and converting negative revenue values to NaN while preserving negative earning per share values.', "Writing dataframes to CSV files with options Covers writing dataframes to CSV files, including examples of using 'to_csv' function, excluding the default index, selectively exporting specific columns, and handling scenarios where all columns do not need to be exported."]}, {'end': 1158.801, 'segs': [{'end': 1062.829, 'src': 'embed', 'start': 888.512, 'weight': 0, 'content': [{'end': 906.582, 'text': 'excellent, okay, now, sometimes you want to skip exporting the header, so to do that, you can provide header argument and say header equal to false.', 'start': 888.512, 'duration': 18.07}, {'end': 910.685, 'text': 'so when you do this, let me close this guy here.', 'start': 906.582, 'duration': 4.103}, {'end': 920.332, 'text': 'Run, this looks like it worked again, and if you look at This thing now here', 'start': 912.146, 'duration': 8.186}, {'end': 921.633, 'text': "You don't see any header.", 'start': 920.452, 'duration': 1.181}, {'end': 930.92, 'text': 'So it skipped a writing header to my CSV file Okay, that was all about to Writing to CSV file now.', 'start': 922.014, 'duration': 8.906}, {'end': 932.702, 'text': "Let's look at read excel.", 'start': 931.08, 'duration': 1.622}, {'end': 941.745, 'text': "Okay, so I'm going to clean up all these cells and i have an excel file here.", 'start': 932.862, 'duration': 8.883}, {'end': 945.006, 'text': 'so let me just open the excel file.', 'start': 941.745, 'duration': 3.261}, {'end': 950.047, 'text': 'it is basically same as the csv file that we had.', 'start': 945.006, 'duration': 5.041}, {'end': 954.207, 'text': "okay, so i'll just put it here.", 'start': 950.047, 'duration': 4.16}, {'end': 956.788, 'text': 'you can see the same stuff.', 'start': 954.207, 'duration': 2.581}, {'end': 961.429, 'text': 'i have this sheet one here and all the same columns.', 'start': 956.788, 'duration': 4.641}, {'end': 970.564, 'text': 'so import pandas as pd and the way you read excel file is by doing pd dot.', 'start': 962.609, 'duration': 7.955}, {'end': 983.099, 'text': 'read excel and the name of the excel file is stockdata.xlsx.', 'start': 973.052, 'duration': 10.047}, {'end': 988.363, 'text': 'now i have my file in the same folder as my jupyter notebook.', 'start': 983.099, 'duration': 5.264}, {'end': 993.366, 'text': 'if you have it in a different folder, then you need to provide the complete path.', 'start': 988.363, 'duration': 5.003}, {'end': 998.109, 'text': 'okay, alright, the next argument is the sheet name.', 'start': 993.366, 'duration': 4.743}, {'end': 1000.009, 'text': 'here it is sheet one.', 'start': 998.109, 'duration': 1.9}, {'end': 1005.812, 'text': 'so sheet one here, okay, and print DF.', 'start': 1000.009, 'duration': 5.803}, {'end': 1011.215, 'text': 'when you do this, you can see that it created data frame successfully.', 'start': 1005.812, 'duration': 5.403}, {'end': 1020.759, 'text': 'okay, now, sometimes you want to do conversion of your cell content from Excel.', 'start': 1011.215, 'duration': 9.544}, {'end': 1029.319, 'text': 'okay. so here you can see that I have NA in Walmarts people column.', 'start': 1020.759, 'duration': 8.56}, {'end': 1033.842, 'text': "now I know that Walmart's key person was Sam Walton.", 'start': 1029.319, 'duration': 4.523}, {'end': 1039.685, 'text': 'so if you want to convert this any into Sam Walton, then you can use converter.', 'start': 1033.842, 'duration': 5.843}, {'end': 1044.748, 'text': 'so the way you use your converter is basically this.', 'start': 1039.685, 'duration': 5.063}, {'end': 1051.411, 'text': 'so what you do is you will define a function.', 'start': 1044.748, 'duration': 6.663}, {'end': 1052.932, 'text': 'so let me first define a function.', 'start': 1051.411, 'duration': 1.521}, {'end': 1057.845, 'text': 'so you will say convert people.', 'start': 1052.932, 'duration': 4.913}, {'end': 1062.829, 'text': 'okay, convert people cell, and this is your cell content.', 'start': 1057.845, 'duration': 4.984}], 'summary': 'Demonstrates skipping header in csv export and reading excel data with pandas.', 'duration': 174.317, 'max_score': 888.512, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ888512.jpg'}], 'start': 888.512, 'title': 'Working with csv and excel files', 'summary': 'Covers writing data to a csv file, skipping headers, reading from excel, and demonstrates reading, converting excel data using pandas, creating dataframes, and modifying cell content with examples.', 'chapters': [{'end': 956.788, 'start': 888.512, 'title': 'Writing to csv and reading from excel', 'summary': 'Covers writing to a csv file, demonstrating how to skip the header, and then moves on to reading from an excel file, showing the similarity between the excel and csv files.', 'duration': 68.276, 'highlights': ['The chapter demonstrates skipping the header when writing to a CSV file by using the header argument and setting it to false.', 'The chapter shows the similarity between an Excel file and a CSV file when reading from an Excel file.', 'The chapter emphasizes the process of writing to a CSV file and reading from an Excel file.']}, {'end': 1158.801, 'start': 956.788, 'title': 'Reading and converting excel data', 'summary': "Explains how to read an excel file using pandas, create a dataframe, and use converters to modify cell content, with an example of converting 'na' to 'sam walton' in a column, demonstrating the use of converters in python dictionary.", 'duration': 202.013, 'highlights': ['The chapter explains how to read an Excel file using pandas, create a dataframe, and use converters to modify cell content.', "Demonstrates the use of converters in Python dictionary to convert 'NA' to 'Sam Walton' in a specific column.", 'Provides an example of defining a function and using it as a converter to modify cell content in the dataframe.']}], 'duration': 270.289, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ888512.jpg', 'highlights': ['The chapter demonstrates skipping the header when writing to a CSV file by using the header argument and setting it to false.', 'The chapter emphasizes the process of writing to a CSV file and reading from an Excel file.', 'The chapter explains how to read an Excel file using pandas, create a dataframe, and use converters to modify cell content.', "Demonstrates the use of converters in Python dictionary to convert 'NA' to 'Sam Walton' in a specific column.", 'Provides an example of defining a function and using it as a converter to modify cell content in the dataframe.', 'The chapter shows the similarity between an Excel file and a CSV file when reading from an Excel file.']}, {'end': 1622.129, 'segs': [{'end': 1222.013, 'src': 'embed', 'start': 1192.087, 'weight': 0, 'content': [{'end': 1203.376, 'text': 'you want to return none, okay, and return cell back, and when you run this you will see you got an N, okay.', 'start': 1192.087, 'duration': 11.289}, {'end': 1217.305, 'text': 'so this is how you use converters to convert your messy data into something meaningful that can be post processed later on easily, okay.', 'start': 1203.376, 'duration': 13.929}, {'end': 1222.013, 'text': 'last thing we are going to cover is writing excel file.', 'start': 1217.305, 'duration': 4.708}], 'summary': "Using converters to process data, returning 'n', and writing excel files.", 'duration': 29.926, 'max_score': 1192.087, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ1192087.jpg'}, {'end': 1290.953, 'src': 'heatmap', 'start': 1263.105, 'weight': 0.951, 'content': [{'end': 1266.086, 'text': 'OK And this should be data frame.', 'start': 1263.105, 'duration': 2.981}, {'end': 1271.761, 'text': 'when you run this, looks like it wrote to this file successfully.', 'start': 1267.438, 'duration': 4.323}, {'end': 1285.81, 'text': 'so if you open the directory aha, you find this new dot xlsx file and when you open it you will see that it wrote this file successfully.', 'start': 1271.761, 'duration': 14.049}, {'end': 1286.971, 'text': 'now again, same stuff.', 'start': 1285.81, 'duration': 1.161}, {'end': 1290.953, 'text': "if you don't want to write your index, you can supply index to be false.", 'start': 1286.971, 'duration': 3.982}], 'summary': 'Data frame successfully written to new .xlsx file.', 'duration': 27.848, 'max_score': 1263.105, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ1263105.jpg'}, {'end': 1348.672, 'src': 'heatmap', 'start': 1292.274, 'weight': 0.713, 'content': [{'end': 1299.356, 'text': "also, let's say, in this excel file you want to start writing at certain offset.", 'start': 1292.274, 'duration': 7.082}, {'end': 1306.779, 'text': "let's say you want to start writing at okay, so start row.", 'start': 1299.356, 'duration': 7.423}, {'end': 1307.499, 'text': 'there is a.', 'start': 1306.779, 'duration': 0.72}, {'end': 1309.039, 'text': 'there is an argument called start row.', 'start': 1307.499, 'duration': 1.54}, {'end': 1313.541, 'text': 'you want to start writing at row number one and column number.', 'start': 1309.039, 'duration': 4.502}, {'end': 1314.241, 'text': "let's say two.", 'start': 1313.541, 'duration': 0.7}, {'end': 1318.426, 'text': 'okay, so let me close this.', 'start': 1314.961, 'duration': 3.465}, {'end': 1330.133, 'text': "don't save, run this again, and when you do this, your new dot excel file has now return from column number two.", 'start': 1318.426, 'duration': 11.707}, {'end': 1331.494, 'text': 'so zero, one, two.', 'start': 1330.133, 'duration': 1.361}, {'end': 1334.717, 'text': 'so it started from column number two and row number two.', 'start': 1331.494, 'duration': 3.223}, {'end': 1336.518, 'text': 'so zero and one row number one.', 'start': 1334.717, 'duration': 1.801}, {'end': 1345.326, 'text': 'okay, so this is how you can write at certain offset or location in your excel file.', 'start': 1336.518, 'duration': 8.808}, {'end': 1348.672, 'text': 'also, same stuff.', 'start': 1346.952, 'duration': 1.72}], 'summary': 'Demonstrated writing data to specific location in excel using python with start row at 2 and column at 2.', 'duration': 56.398, 'max_score': 1292.274, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ1292274.jpg'}, {'end': 1400.516, 'src': 'heatmap', 'start': 1375.88, 'weight': 2, 'content': [{'end': 1389.052, 'text': "okay, Now, if you have, let's say, two data frames and you want to write these two data frames to same Excel files in different sheets,", 'start': 1375.88, 'duration': 13.172}, {'end': 1389.672, 'text': 'how do you do that??', 'start': 1389.052, 'duration': 0.62}, {'end': 1396.355, 'text': 'well, there is a class called Excel writer which you can use.', 'start': 1390.373, 'duration': 5.982}, {'end': 1400.516, 'text': 'so let me first create two data frames.', 'start': 1396.355, 'duration': 4.161}], 'summary': 'Using excelwriter class to write two data frames to the same excel file in different sheets.', 'duration': 33.439, 'max_score': 1375.88, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ1375880.jpg'}, {'end': 1520.687, 'src': 'embed', 'start': 1488.687, 'weight': 1, 'content': [{'end': 1500.917, 'text': 'and if you look at your data frame here, you can see you have your weather data 32, 35, 30, 28 okay, and if you look at your stocks,', 'start': 1488.687, 'duration': 12.23}, {'end': 1502.058, 'text': 'you have your stocks data.', 'start': 1500.917, 'duration': 1.141}, {'end': 1507.322, 'text': 'you can see all these values, which is same as this particular data frame.', 'start': 1502.058, 'duration': 5.264}, {'end': 1513.722, 'text': 'okay, So that was all about reading and writing to Excel files.', 'start': 1507.322, 'duration': 6.4}, {'end': 1520.687, 'text': "I didn't cover all the properties that reading and writing excellent CSV files offers.", 'start': 1514.103, 'duration': 6.584}], 'summary': 'Reading and writing excel files, weather data 32, 35, 30, 28, stocks data.', 'duration': 32, 'max_score': 1488.687, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ1488687.jpg'}, {'end': 1575.226, 'src': 'embed', 'start': 1542.422, 'weight': 4, 'content': [{'end': 1553.147, 'text': 'okay. so now in my case, I had a comma separated file where separator is comma, but sometimes you might have pipe or some other character as a separator,', 'start': 1542.422, 'duration': 10.725}, {'end': 1564.1, 'text': 'but using read csv file you can handle those characters by using this separator or delimiter argument.', 'start': 1553.147, 'duration': 10.953}, {'end': 1575.226, 'text': 'you have other useful properties as well, such as true values, false values, where you can say that if i have this particular value,', 'start': 1564.1, 'duration': 11.126}], 'summary': 'Handling different separators in csv files using read_csv file with delimiter argument.', 'duration': 32.804, 'max_score': 1542.422, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ1542422.jpg'}], 'start': 1158.801, 'title': 'Python data conversion and excel writing', 'summary': 'Covers the use of converters in python to transform messy data, such as eps cell, into meaningful information for easy post-processing, and also demonstrates writing an excel file. it also explains writing data frames to excel files, including specifying sheet names and starting rows, and using excelwriter to write multiple data frames to different sheets in a single excel file, with a brief mention of additional properties for reading and writing csv files.', 'chapters': [{'end': 1222.013, 'start': 1158.801, 'title': 'Python data conversion and excel writing', 'summary': 'Covers the use of converters in python to transform messy data, such as eps cell, into meaningful information for easy post-processing, and also demonstrates writing an excel file.', 'duration': 63.212, 'highlights': ['Using converters in Python allows for transforming messy data into meaningful information, such as converting EPS cell, for easy post-processing later on.', 'Demonstrates writing an Excel file, a crucial skill for data manipulation and analysis in Python.']}, {'end': 1622.129, 'start': 1222.013, 'title': 'Writing and reading excel files in pandas', 'summary': 'Explains how to write data frames to excel files, including specifying sheet names and starting rows, and using excelwriter to write multiple data frames to different sheets in a single excel file, with a brief mention of additional properties for reading and writing csv files.', 'duration': 400.116, 'highlights': ['The chapter demonstrates how to write a data frame to an Excel file using the to_excel function, specifying the file name and sheet name, and how to omit the index column by setting index to false, with a successful demonstration of writing and opening the Excel file. It also explains how to start writing at a specific offset by specifying the start row and start column, illustrated by writing to a specific location in the Excel file.', 'It further elaborates on writing multiple data frames to the same Excel file in different sheets using the ExcelWriter class, with a demonstration of creating two data frames and utilizing the ExcelWriter to write them to a single Excel file with separate sheets, along with a visual confirmation of the created Excel file with the two sheets containing the respective data frames.', 'The chapter briefly mentions additional properties for reading and writing CSV files, such as specifying the separator or delimiter, handling true and false values, skipping footers and blank lines, and hints at the upcoming tutorial on handling missing data in pandas.', 'The tutorial concludes with a request for feedback and a teaser for the next tutorial on handling missing data in pandas.']}], 'duration': 463.328, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-0NwrcZOKhQ/pics/-0NwrcZOKhQ1158801.jpg', 'highlights': ['Using converters in Python allows for transforming messy data into meaningful information, such as converting EPS cell, for easy post-processing later on.', 'Demonstrates writing an Excel file, a crucial skill for data manipulation and analysis in Python.', 'The chapter demonstrates how to write a data frame to an Excel file using the to_excel function, specifying the file name and sheet name, and how to omit the index column by setting index to false.', 'It further elaborates on writing multiple data frames to the same Excel file in different sheets using the ExcelWriter class, with a demonstration of creating two data frames and utilizing the ExcelWriter to write them to a single Excel file with separate sheets.', 'The chapter briefly mentions additional properties for reading and writing CSV files, such as specifying the separator or delimiter, handling true and false values, skipping footers and blank lines.']}], 'highlights': ['The tutorial starts with the process of starting a Jupyter notebook and creating a new notebook by selecting new and python.', "Covers reading csv files into a data frame using pandas, including importing and printing data frame with 'pd.read_csv'.", "Handling extra headers in CSV file by using 'skip rows' and 'header' arguments.", "Dealing with missing headers in CSV file using 'header equal to none' and supplying column names with 'names' argument.", "Limiting rows while reading CSV file with 'n rows' argument, useful for handling large CSV or excel files.", "Cleaning up messy data with 'any values' argument to replace problematic cells with 'not available' and 'n.a.' with 'nan' values.", 'Converting negative values to NaN The chapter explains converting negative values in the dataframe to NaN, ensuring revenue column cannot be negative, and the powerful feature of Na underscore values in cleaning up messy data.', 'Using Dictionary to handle specific column conversions Demonstrates using a Dictionary to specify column conversions, ensuring specific columns like revenue and EPS are handled differently, and converting negative revenue values to NaN while preserving negative earning per share values.', "Writing dataframes to CSV files with options Covers writing dataframes to CSV files, including examples of using 'to_csv' function, excluding the default index, selectively exporting specific columns, and handling scenarios where all columns do not need to be exported.", 'The chapter demonstrates skipping the header when writing to a CSV file by using the header argument and setting it to false.', 'The chapter emphasizes the process of writing to a CSV file and reading from an Excel file.', 'The chapter explains how to read an Excel file using pandas, create a dataframe, and use converters to modify cell content.', "Demonstrates the use of converters in Python dictionary to convert 'NA' to 'Sam Walton' in a specific column.", 'Provides an example of defining a function and using it as a converter to modify cell content in the dataframe.', 'The chapter shows the similarity between an Excel file and a CSV file when reading from an Excel file.', 'Using converters in Python allows for transforming messy data into meaningful information, such as converting EPS cell, for easy post-processing later on.', 'Demonstrates writing an Excel file, a crucial skill for data manipulation and analysis in Python.', 'The chapter demonstrates how to write a data frame to an Excel file using the to_excel function, specifying the file name and sheet name, and how to omit the index column by setting index to false.', 'It further elaborates on writing multiple data frames to the same Excel file in different sheets using the ExcelWriter class, with a demonstration of creating two data frames and utilizing the ExcelWriter to write them to a single Excel file with separate sheets.', 'The chapter briefly mentions additional properties for reading and writing CSV files, such as specifying the separator or delimiter, handling true and false values, skipping footers and blank lines.']}