Convert Python Lists to Excel Files with pandas and numpy: A Step-by-Step Guide

Converting Python Lists to Excel Files with pandas and numpy

In this article, we’ll explore how to convert Python lists containing financial data into a neat table format in an Excel file. We’ll delve into the details of using pandas and numpy libraries for this task.

Introduction

Python is a versatile programming language that offers various ways to manipulate and analyze data. When working with large datasets, it’s essential to have tools that can help convert these datasets into formats like Excel files for easy sharing and editing.

In this article, we’ll focus on using pandas and numpy libraries in Python to convert lists containing financial data into an Excel file. We’ll cover the necessary steps, including formatting the data, serializing it into CSV format, and finally converting it to an Excel file.

Requirements

To follow along with this tutorial, you’ll need:

Python 3.x installed on your system
pandas library installed (you can install it using pip: pip install pandas)
numpy library installed (you can install it using pip: pip install numpy)

Understanding the Problem

The question at hand is about converting a Python list containing financial data into an Excel file. The list contains multiple rows and columns, with each column representing different types of financial data.

[                                  Unnamed: 0   IV Q 2016    I Q 2017<br/>
0     Przychody netto ze sprzedaży (tys. zł)       8 078       8 877<br/>
1   Zysk (strata) z działal. oper. (tys. zł)     -15 386        -562<br/>
2             Zysk (strata) brutto (tys. zł)     -16 300        -723<br/>
3             Zysk (strata) netto (tys. zł)*      -8 500        -707<br/>
4                      Amortyzacja (tys. zł)      -1 059       1 191<br/>
5                           EBITDA (tys. zł)     -16 445         629<br/>
6                           Aktywa (tys. zł)     112 657     152 271<br/>
7                  Kapitał własny (tys. zł)*       1 127      47 725<br/>
8                   Liczba akcji (tys. szt.)  44 260,410  44 260,410<br/>
9                         Zysk na akcję (zł)       -0192       -0016<br/>
10            Wartość ksiegowa na akcję (zł)        0025        1078<br/>
11             Raport zbadany przez audytora           N           N<br/>
 ]

Step 1: Import Necessary Libraries

First, we need to import the necessary libraries. In this case, we’ll use pandas and numpy.

import pandas as pd
import numpy as np

Step 2: Define the Data

Next, let’s define the data in a Python list.

data = [
    ['Unnamed: 0', 'IV Q 2016', 'I Q 2017'],
    ['Przychody netto ze sprzedaży (tys. zł)', 8078, 8877],
    ['Zysk (strata) z działal. oper. (tys. zł)', -15386, -562],
    ['Zysk (strata) brutto (tys. zł)', -16300, -723],
    ['Zysk (strata) netto (tys. zł)*', -8500, -707],
    ['Amortyzacja (tys. zł)', -1059, 1191],
    ['EBITDA (tys. zł)', -16445, 629],
    ['Aktywa (tys. zł)', 112657, 152271],
    ['Kapitał własny (tys. zł)*', 1127, 47725],
    ['Liczba akcji (tys. szt.)', 44260.410, 44260.410],
    ['Zysk na akcję (zł)', -19.2, -1.6],
    ['Wartość ksiegowa na akcję (zł)', 2.25, 1078],
    ['Raport zbadany przez audytora', 'N', 'N']
]

Step 3: Convert Data into DataFrame

Now that we have our data defined, let’s convert it into a pandas DataFrame.

df = pd.DataFrame(data[1:], columns=data[0])

The data[1:] part is used to exclude the header row from the data.

Step 4: Serialize Data into CSV Format

Next, we’ll serialize our DataFrame into CSV format using the to_csv() method.

df.to_csv('output.csv', index=False)

This will create a new file named output.csv in the current directory. The index=False parameter is used to exclude the row indices from the CSV file.

Step 5: Convert CSV File to Excel Format

Now that we have our data serialized into CSV format, let’s convert it to an Excel file using the openpyxl library.

First, install the openpyxl library using pip:

pip install openpyxl

Next, import the openpyxl library and create a new workbook object.

from openpyxl import load_workbook

wb = load_workbook('output.csv', data_only=False)

This will create an Excel file from the CSV file. The data_only=False parameter is used to include the header row in the Excel file.

Step 6: Save Changes and Close Workbook

Finally, let’s save our changes and close the workbook object.

wb.save('output.xlsx')

This will create a new Excel file named output.xlsx in the current directory. The data from the CSV file is now neatly formatted into an Excel table.

Conclusion

In this article, we’ve explored how to convert Python lists containing financial data into an Excel file using pandas and numpy libraries. We’ve covered the necessary steps, including formatting the data, serializing it into CSV format, and finally converting it to an Excel file.

Note that the openpyxl library is used in this example to create an Excel file from a CSV file. However, there are other libraries available for working with Excel files, such as xlwt and xlsxwriter.

By following these steps, you can easily convert your Python lists into neatly formatted Excel tables using pandas and numpy libraries.

Additional Tips

When working with large datasets, it’s essential to use efficient data structures like NumPy arrays or pandas DataFrames.
Always use meaningful variable names and follow proper coding conventions when writing code.
Use Markdown formatting for readability and consistency in your code.
Consider using libraries that provide more advanced features and functionality, such as pandas and numpy.

Example Use Cases

Financial data analysis: Convert Python lists containing financial data into an Excel file to perform calculations and analysis.
Data visualization: Use pandas and numpy libraries to create visualizations of your data in an Excel format.

References

Last modified on 2023-08-17