As a Python programmer, you may have encountered the need to convert some data into or read from an excel file throughout your time working with the programming language. It's not a difficult task, and it doesn't necessitate a lot of programming. This article will teach you how to read and write Excel files in Python.
How to read and write Excel files with Pandas Library
Just like other files you can be able to read and write the Excel files in python using the Pandas library. You only need to install the Pandas library on your PC first using the Pandas library installation commands.
The Pandas library installation commands differ from the operating system you are running on. If you are on Windows operating system, you will need to run the command “$ python-pip install pandas” on your windows command prompt while If you are running on macOS, you will just run “$pip install pandas”.
How to write the Excel files in python
We will utilize the DataFrame and the built-in to excel() method to write our data into the excel files. Let's go through the steps below to write our data into excel files.
Step 1: Using the command "import pandas as pd," import the Pandas module.
Step 2: then, use the dictionary to populate the DataFrame
|
df = pd.DataFrame({'Countries':['India', 'USA', 'Japan', 'Congo', 'Australia', 'Qatar'], 'Cities':['Mumbai', 'Ney York', 'Tokyo', 'Kinshasha', 'Melbourne', 'Doha'], 'Region':['Asia', 'North America', 'Asia', 'Africa', 'Australia', 'Middle East']}) |
The keys in the dictionary are going to serve as the column names and the values will automatically become the rows containing the information.
Step 3: Lastly, we have our data set, now let’s convert our data contents into excel using the to_excel () function. This function will also specify the path and the name of our new excel file. For instance, for the above data, we can name it countries. Xlsx using the function “df.to_excel('./countries.xlsx')”.
How to read the excel files in Python
We have learned how to write excel files in python. Now let’s read the excel (.xls, .xlsx) files.
Step 1: first of all, create an excel file and input some data. This is the file that we are going to read in our python program.
Step 2: To enable Pandas to read the .xls and .xlsx files, we need to install the xlrd library. To install the Xlrd simply run the command: "$ pip install xlrd”. If you proceed and call the function pandas.read_excel() without installing first the xlrd, you will definitely be going to get an error.
Step 3: Using the DataFrame function, provide the excel file path in your first argument. Your DataFrame should look like the following:
|
Import pandas as pd df = pd.read_excel('myexcelfile.xlsx') Print (df) |
Now, run your code and the “myexcelfile.xlsx” file data will be displayed on your screen. That’s it.
Conclusion
Now in you have learned how to read and write the excel files in python with Pandas Library. In your next python project, you will not have to transfer your excel data into another database formats in order to read or to write. It’s simply easy as demonstrated.
|
Article Contributed By :
|
|
|
|
|
727 Views |
|