Getting Started with Python Integration to SAS® Viya® - Part 19 - Load Multiple CSV Files

Welcome to the continuation of my series Getting Started with Python Integration to SAS Viya. In this post I'll discuss how to load multiple CSV files into memory as a single table using the loadTable action.

Load and prepare data on the CAS server

To start, we need to create multiple CSV files in a folder. I created this script to load and prepare the CSV files in the CAS server for this post. This should work in your environment. The script does the following:

Loads the WARRANTY_CLAIMS_0117.sashdat files from the Samples caslib into memory.

Modifies the in-memory table by renaming and dropping columns.

Adds a sub directory in the Casuser caslib named csv_file_blogs.

Saves a CSV file for each distinct year in the csv_file_blogs folder.

Drops the original CAS table.

import swat
import pandas as pd

## Connect to CAS
conn = swat.CAS(Enter your CAS connection information)

##
## Data prep functions
##
def prep_data():
"""
Load and prepare the warranty_claims_0017.sashdat file in CAS
"""
## Load the WARRANTY_CLAIMS_0117.sashdat from the Samples caslib into memory in Casuser
conn.loadTable(path='WARRANTY_CLAIMS_0117.sashdat', caslib='samples',
casout={'name':'warranty_claims',
'caslib':'casuser',
'replace':True})
##
## DATA PREP
##

## Reference the CAS table in an object
castbl = conn.CASTable('warranty_claims', caslib = 'casuser')

## Store the column names and labels in a dataframe
df_col_names = castbl.columnInfo()['ColumnInfo'].loc[:,['Column','Label']]

## Create a list of dictionaries of how to rename each column using the column labels
renameColumns = []
for row in df_col_names.iterrows():
colName = row[1].values[0]
labelName = row[1].values[1].replace(' ','_')
renameColumns.append(dict(name=colName, rename=labelName))

## List of columns to keep in the CAS table
keepColumns = {'Campaign_Type', 'Platform','Trim_Level','Make','Model_Year','Engine_Model',
'Vehicle_Assembly_Plant','Claim_Repair_Start_Date', 'Claim_Repair_End_Date'}

## Rename and drop columns to make the table easier to use
castbl.alterTable(columns = renameColumns, keep = keepColumns)

## Return the CASTable object reference
return castbl

def save_cas_table_as_csv_files(cas_table_reference):
"""
Create a subdirectory in Casuser and save mutliple CSV files in it.
"""
## Create a subdirectory in the Casuser caslib named csv_file_blogs
conn.addCaslibSubdir(name = 'casuser', path = 'csv_file_blogs')

## Create a CSV file for each year
for year in list(castbl.Model_Year.unique()):
(cas_table_reference
.query(f"Model_Year ='{year}'")
.save(name = f'csv_file_blogs/warranty_claims_{year}.csv',
caslib = 'casuser',
replace = True)
)

## Drop the CAS Table
cas_table_reference.dropTable()

## View files in the csv_file_blogs subdirectory
fi = conn.fileInfo(allFiles = True, caslib = 'casuser')
fi_subdir = conn.fileInfo(path = 'csv_file_blogs', caslib = 'casuser')
display(fi, fi_subdir)

## Create the CAS table
castbl = prep_data()

## Save the CAS table as a CSV file for each year
save_cas_table_as_csv_files(castbl)

## and the results
NOTE: Cloud Analytic Services made the file WARRANTY_CLAIMS_0117.sashdat available as table WARRANTY_CLAIMS in caslib CASUSER(Peter).
NOTE: Cloud Analytic Services saved the file csv_file_blogs/warranty_claims_2015.csv in caslib CASUSER(Peter).
NOTE: Cloud Analytic Services saved the file csv_file_blogs/warranty_claims_2016.csv in caslib CASUSER(Peter).
NOTE: Cloud Analytic Services saved the file csv_file_blogs/warranty_claims_2017.csv in caslib CASUSER(Peter).
NOTE: Cloud Analytic Services saved the file csv_file_blogs/warranty_claims_2018.csv in caslib CASUSER(Peter).
NOTE: Cloud Analytic Services saved the file csv_file_blogs/warranty_claims_2019.csv in caslib CASUSER(Peter).
NOTE: Cloud Analytic Services dropped table warranty_claims from caslib CASUSER(Peter.Styliadis).

The results show that five CSV files named warranty_claims<year>.csv were created in the subdirectory csv_file_blogs in the Casuser caslib.

Next, I'll use the fileInfo action to view the new csv_file_blogs subdirectory in the Casuser caslib. In the fileInfo CAS action use the includeDirectories parameter to view subdirectories.

conn.fileInfo(includeDirectories = True, caslib = 'casuser')

Lastly, I'll view the available files in the csv_file_blogs subdirectory. To view files in a subdirectory in a caslib add the folder name in the path parameter.

conn.fileInfo(path = 'csv_file_blogs', caslib = 'casuser')

The results show that the subdirectory has five CSV files.

Load all of the CSV files as a single table

To easily load multiple CSV files as a single in-memory CAS table use the table.loadTable CAS action. The only requirements when loading multiple CSV files:

All of the CSV files must end with .csv.

Set the multiFile option to True in the importOptions parameter.

The CSV files must have the same number of columns and the columns must have the same data type.

Here, I'll use the loadTable action with the path parameter to specify the subdirectory csv_file_blogs and the caslib parameter to specify the Casuser caslib. The importOptions parameter uses the fileType option to determine the other parameters that apply. The CSV file type has a multiFile option. If you set the multiFile option to True, it will try to load all CSV files from the path into a single in-memory table. The casOut parameter simply specifies output CAS table information.

conn.loadTable(path="csv_file_blogs", caslib = 'casuser', ## Specify the subdirectory name (csv_file_blogs) and the input caslib name
importOptions = { ## Specify the import options
'fileType' : 'CSV',
'multiFile' : True
},
casOut = { ## Specify output CAS table information
'name' : 'allCSVFiles',
'caslib' : 'casuser',
'replace' : True
})

## and the results
NOTE: The file, '/cas/data/caslibs/casuserlibraries/peter/csv_file_blogs/warranty_claims_2015.csv' was used to create the CAS Table column names.
NOTE: The CSV file table load for table, 'allCSVFiles' produced 153217 rows from 5 files.
NOTE: Cloud Analytic Services made the file csv_file_blogs available as table ALLCSVFILES in caslib CASUSER(Peter).

The action concatenated each CSV file and loaded them as a single distributed CAS table named ALLCSVFILES.

Next, I'll run the tableInfo action to view available in-memory tables in the Casuser caslib.

conn.tableInfo(caslib = 'casuser')

The action results show one CAS table is in memory.

Then I'll make a client-side reference to the distributed CAS table and run the head method from the SWAT package.

allcsvfilesTbl = conn.CASTable('allcsvfiles', caslib = 'casuser')
allcsvfilesTbl.head()

Finally, I'll run the SWAT value_counts on the Model_year column.

(allcsvfilesTbl ## CAS table
.Model_Year ## CAS column
.value_counts() ## SWAT value_counts method
)
## and the results
2017.0 70479
2018.0 43975
2016.0 32707
2019.0 3510
2015.0 2546
dtype: int64

The results show all five years of data were imported into the CAS table, one for each CSV file.

Add file name and path columns to the CAS table

The importOptions parameter has a variety of CSV options you can use to modify how to import the files. Two useful options are showFile and showPath. The showFile option includes a column that shows the CSV file name. The showPath option includes a column that shows the fully-qualified path to the CSV file.

conn.loadTable(path="csv_file_blogs", caslib = 'casuser', ## Specify the subdirectory name (csv_file_blogs) and the input caslib name
importOptions = { ## Specify the import options
'fileType' : 'CSV',
'multiFile' : True,
'showFile' : True,
'showPath' : True
},
casOut = { ## Specify output CAS table information
'name' : 'allCSVFiles_path_info',
'caslib' : 'casuser',
'replace' : True
})

## and the results
NOTE: The file, '/cas/data/caslibs/casuserlibraries/peter/csv_file_blogs/warranty_claims_2015.csv' was used to create the CAS Table column names.
NOTE: The CSV file table load for table, 'allCSVFiles_path_info' produced 153217 rows from 5 files.
NOTE: Cloud Analytic Services made the file csv_file_blogs available as table ALLCSVFILES_PATH_INFO in caslib CASUSER(Peter).

I'll run the tableInfo action to view available CAS tables.

conn.tableInfo(caslib = 'casuser')

The action shows that two CAS tables are now in-memory.

I'll reference and preview the new CAS table .

allcsvfiles_path_infoTbl = conn.CASTable('allcsvfiles_path_info', caslib = 'casuser')
allcsvfiles_path_infoTbl.head()

The preview shows the new CAS table has a column named path and a column named fileName.

Lastly, I'll use the SWAT value_counts method to view how many rows came from each CSV file. I'll specify the CAS table reference, the column name, then the value_counts method.

(allcsvfiles_path_infoTbl
.fileName
.value_counts()
)

## and the results
warranty_claims_2017.csv 70479
warranty_claims_2018.csv 43975
warranty_claims_2016.csv 32707
warranty_claims_2019.csv 3510
warranty_claims_2015.csv 2546
dtype: int64

The results show the CSV files were concatenated into a single CAS table. We can see how many rows came from each file.

Summary

The SWAT package blends the world of pandas and CAS to process your distributed data. In this example I focused on using table.loadTable CAS action to concatenate multiple CSV files into a single distributed CAS table using a single method.

Additional and related resources

Getting Started with Python Integration to SAS® Viya® - Index

SWAT API Reference

table.loadTable CAS action

SAS® Cloud Analytic Services: Fundamentals

CAS Action Documentation

CAS Action! - a series on fundamentals

SAS Course - SAS® Viya® and Python Integration Fundamentals

Getting Started with Python Integration to SAS® Viya® - Part 19 - Load Multiple CSV Files was published on SAS Users.