Back Python Pandas Tutorial 4: Read Write Excel CSV File
20-30
Created By: codebasics
Published At: 2017-02-04
Search Query: Python Pandas Tutorial,Python Read Files,Python Write Create Files
Tags: pandas excel python pandas excel pandas csv python pandas csv pandas excel tutorial pandas csv tutorial pandas read_csv pandas to_csv pd read_csv pandas write csv python read excel python csv tutorial python write csv python excel pandas write pandas read excel pandas write excel pandas read csv how to read csv file in python read csv file in python csv python pandas read csv file how to read csv file in pandas read csv file in pandas
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
logo Ask VidTutorAssistant
Comment
or
Question
Your name
Comments with generated responses
@mrwilbroadtv4230
2021-08-07

Very helpfull video but i wish to see web scraping in your tutorial

@codebasics

I am happy this was helpful to you.

VidTutorAssistant (GPT-4)

I'm glad to hear that you found the video helpful, @mrwilbroadtv4230! Thank you for your interest in web scraping. Although this tutorial focused on reading and writing CSV and Excel files in pandas, I appreciate your suggestion. A tutorial on web scraping using Python is indeed a great idea, as it's a popular and useful technique especially when working with data. Stay tuned to the channel, I'll consider adding this topic to future tutorials! Keep enjoying and keep learning!

@ankeshsingh2576
2019-06-29

Hey can we add a new row with the functio used in Converters. Suppose we are reading Fuel Type Column which has Petrol, Diesel, CNG , xtra premium and then we want to add a new column say Fuel_price reading the values for each row of the column Fuel_Type, Please let me know if that's possible.


Any way I love the way you explain, specially for working professional like me who don't have time to study the document.


Thank you so much <3

@codebasics

To create fueld_price from fuel_type column you can use apply function. After your dataframe is created do this,
def type_to_price(type):
if type=='Petrol':
return 80
else if type=='Diesel':
return 78
df['Fuel_price']=df['Fuel_type'].apply(type_to_price)

VidTutorAssistant (GPT-4)

Hello @ankeshsingh2576, thank you for your kind words and that is actually a great question. Indeed, it is very much possible to do so! You can programmatically add a new column 'Fuel_price' based upon the individual fuel type values. The 'apply()' function is the one we will be using in this case, applying a new logic to each row of 'Fuel_Type' and generating corresponding 'Fuel_price'.

For example, you might define a function like this:
```python
def convert_fuel_type_to_price(fuel_type):
# Write your logic to convert a fuel type to a fuel price
Fuel_Type_Price_Dict={'Petrol':x,'Diesel':y,'CNG':z,'xtra premium':a}
if fuel_type in Fuel_Type_Price_Dict.keys():
return Fuel_Type_Price_Dict[fuel_type]
else:
return None
```
You can then create a new column like this:
```
df['Fuel_Price'] = df['Fuel_Type'].apply(convert_fuel_type_to_price)
```
I'm glad you found the videos useful! Keep learning, and never hesitate to ask if you have additional questions. Cheers!

@rohank2825
2020-04-07

‘Those who know, do. Those that understand, teach.’ –Aristotle
thank you very much sir for your effort .

@codebasics

Another great quote Rohan 👏👏👏

VidTutorAssistant (GPT-4)

Thank you for your kind words, @rohank2825! As Aristotle said, understanding is at the core of teaching and it's my pleasure to share my understanding of Python with you. If you have any questions or topics you want me to cover in future videos, feel free to share. Keep coding and happy learning!

@abdulquadirkhan8558
2019-08-18

4:10 can I skip multiple rows?
Answer as soon as possible. Please.

@codebasics

Yes. If you want to skip first 3 rows then specify skiprows=3.
If you want to skip first and 3rd row only and still keep 2nd row then you can specify a callable function in skiprows argument. For example skiprows = lambda x: x in [0,2]

VidTutorAssistant (GPT-4)

Yes, you can definitely skip multiple rows while reading in a CSV file! Using the skiprows argument in pandas' read_csv function, you can specify the number of rows to skip. For example, if you want to skip the first 5 rows, you would write: `pd.read_csv('filename.csv', skiprows=5)`. Also, you can skip specific rows by passing a list instead, like `pd.read_csv('filename.csv', skiprows=[1,2,5])`. In the latter case, it would skip the 2nd, 3rd and 6th rows (index starts from 0). Hope this helps. Happy coding!

@abdulquadirkhan8558
2019-08-18

How can I add new column to the existing CSV file which is difference of values of two other cells in same CSV file?

@codebasics

you can first parse the csv file into dataframe. let say columns names are df.a and df.b. now you can do,
df['c']=df.a+df.b and that will add new column which is addition of a and b columns. Then you can do df.to_csv() to export it to a new csv file

VidTutorAssistant (GPT-4)

Hi @abdulquadirkhan8558, that's a great question! Remember, we use pandas primarily to handle CSV file operations in Python. Here's how you can add a new column to the existing dataframe which is the difference of values of two other cells in the same CSV file:

First, read the CSV file into a pandas dataframe:
```python
df = pd.read_csv('yourfile.csv')
```
Then, you can add a new column to `df` by simply defining it. Assuming you want to subtract the values in 'column1' from the values in 'column2', for instance, you'd do something like:
```python
df['new_column'] = df['column2'] - df['column1']
```
This will create a new column called 'new_column' where each entry is the difference between the corresponding entries in 'column2' and 'column1'.

Remember to write the dataframe back to the CSV if you want to save your changes:
```python
df.to_csv('yourfile.csv', index=False)
```
The `index=False` argument is used if you do not wish to write row indices into the CSV file. Remove this if you want the indices.

Your input matters to us, so please let us know if you have any other questions! Keep coding and have fun with pandas!

@kailashsinghrautela8253
2020-09-16

Seriously i never know that read function could be a bit challenging too

@codebasics

I am happy this was helpful to you.

VidTutorAssistant (GPT-4)

I'm glad you're finding value in the video, @kailashsinghrautela8253! Yes, the read function comes with nuances and can potentially be challenging, especially when dealing with different data formats and peculiarities. It's all part of the journey of learning more about Python and data handling in pandas. Each challenge is an opportunity to learn more. If you have any further questions, feel free to ask. Happy coding!