Using Pandas Data Frames to Process Spreadsheet Data -- All in the cloud! | Try WayScript Development Hub

Introduction

Pandas is a great data analysis tool to use, and WayScript provides users the ability to use pandas within the WayScript platform and in conjunction with several other modules. This article will detail how we manipulate pandas dataframes and pass them into other modules to use within our script. Keep reading to learn more!

Building our Script

The first thing we’ll do for this script is create sample data by using the Create Variable module. These data will be used in the dataframe we’ll create later. In the toolbar at the left, we’ll create two simple lists; the first will contain numbers and the second will just contain a string of letters.

Next, add a Python module and open the editor to input custom code.

The first line of code will import pandas by simply using:

import pandas as pd

Now we need to create Python variables from the variables we created in WayScript earlier. This can be done with the following code:

list_a = variables[‘list_a’]
list_b = variables[‘list_b’]

The next few lines of code will begin creating the dataframe. First, we need to adjust the data so that it can be read correctly. Then, we’ll create the dataframe and print it.

data = {‘numbers’ : list_a, ‘letters’ : list_b}
df = pd.DataFrame(data) 
print(df)

Once we run the code, the dataframe generates on the right of the screen.

There are a number of different manipulations you can perform on the data frame after you’ve created it. In this example, we’ll create a new column of values that multiplies each number in the “numbers” column by 5.

df[‘new_values’] = df[‘numbers’] \* 5  

We can also write this dataframe to an Excel spreadsheet.

df.to_excel(‘sample.xlsx’)  

This will create a new Excel file in the File Manager. We can then access this spreadsheet by using an Excel module and selecting the file under the “Choose File to Read” drop-down menu. From here, you can import values just like you could with any other spreadsheet. We can also export specific columns of data and create new WayScript variables from them, using this code:

variables[‘new_list’] = df[‘new_values’].to_list()  

Once run, the new variable will generate under the ones we created in the first step.

Then, we can use this variable in any other module, just like the others.

This tutorial was just a quick example showing how easy it is working with panda dataframes within the WayScript platform. Not only can you manipulate the data, you can also seamlessly integrate it with other modules to get the most functionality for you and your business.

Conclusion

Questions about this script or anything else? Join our discord. We're always around to help. If you want to work the full script template, just find it here.