Learn how you can become a Python programmer in just 12 weeks.

    We respect your privacy. Unsubscribe at anytime.

    Built with ConvertKit

    Quick Tutorial on Pandas to Excel in 3 Steps – Master the Basics

    by

    What will we cover in this tutorial?

    We will cover the basic process of saving data from a Pandas DataFrame to an Excel sheet. This includes

    • The simple export of a DataFrame to Excel from Python.
    • How to set the Sheet name in Excel from Python.
    • To set which columns to export from the DataFrame to Excel.

    Step 1: The simple export of a full DataFrame

    Often you just need to figure out how to save the full DataFrame to an Excel sheet. This is often needed, as you have collected the data in Python, but want to explore it or work more on it in Excel.

    If you are new to Pandas, we can recommend to read this tutorial.

    Pandas is a great library to work with data similar to an Excel sheet. The advantage of Pandas is working with the data in Python and automate many things. That could be collecting data from API’s on the internet or simply automate the stuff you do manually in Excel.

    Another beauty of Pandas is that it has great built in methods that does all the work for you.

    Here we will demonstrate how a call to to_excel(…) can be done on a DataFrame.

    import pandas as pd
import numpy as np

# Generate some random increasing data
df = pd.DataFrame(
    {'A': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'B': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)]}
)

df.to_excel("output.xlsx")

    The code above creates a random DataFrame using the random function from NumPy.

    Then it continues to call to_excel on the data frame with the output Excel file name. This will create an Excel sheet, which could look similar to this.

    Step 2: How to set the sheet name in Excel from Python

    Sometimes you want your data more organized. Here we are going to show how to set the sheet name where you export your data.

    The default sheet name is Sheet1, hence in the previous step you would notice that the created sheet has that name.

    Sometimes you want a different name. Luckily, this is simple.

    import pandas as pd
import numpy as np

# Generate some random increasing data
df = pd.DataFrame(
    {'A': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'B': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)]}
)

df.to_excel("output.xlsx", sheet_name="My Sheet")

    Notice that we only set the sheet_name parameter in the call. That is all it takes.

    How did I know? I read the documentation.

    This creates an Excel file with the desired sheet name.

    Step 3: Only export the specific columns

    Often it happens that you are only interested in exporting some of the columns in your DataFrame. Luckily, this is also simple to do from the standard export function on the DataFrame.

    import pandas as pd
import numpy as np

# Generate some random increasing data
df = pd.DataFrame(
    {'A': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'B': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'C': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'D': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'E': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'F': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)]}
)

df.to_excel("output.xlsx", sheet_name="My Sheet", columns=['B', 'E', 'F'])

    In the above example we create a DataFrame with a few columns. We only export columns B, E, and F.

    This is set directly in the to_excel method, which can set the parameter columns. The parameter can accept a list of the column names to export to the Excel sheet.

    Next step

    This is only to get you started and see how simple it is to work with data from Python using Pandas. This enables you to make great things with only a few lines of code.

    Python for Finance: Unlock Financial Freedom and Build Your Dream Life

    Discover the key to financial freedom and secure your dream life with Python for Finance!

    Say goodbye to financial anxiety and embrace a future filled with confidence and success. If you’re tired of struggling to pay bills and longing for a life of leisure, it’s time to take action.

    Imagine breaking free from that dead-end job and opening doors to endless opportunities. With Python for Finance, you can acquire the invaluable skill of financial analysis that will revolutionize your life.

    Make informed investment decisions, unlock the secrets of business financial performance, and maximize your money like never before. Gain the knowledge sought after by companies worldwide and become an indispensable asset in today’s competitive market.

    Don’t let your dreams slip away. Master Python for Finance and pave your way to a profitable and fulfilling career. Start building the future you deserve today!

    Python for Finance a 21 hours course that teaches investing with Python.

    Learn pandas, NumPy, Matplotlib for Financial Analysis & learn how to Automate Value Investing.

    “Excellent course for anyone trying to learn coding and investing.” – Lorenzo B.

    Leave a Comment