Exporting Data from CAS using Python

Exporting Data from CAS using Python

While the save action can export data to many formats and data sources, there are also ways of easily converting CAS table data to formats on the client as well. Keep in mind though that while you can export large data sets on the server, you may not want to attempt to bring tens of gigabytes of data down to the client using these methods.

While you can always use the fetch action to get the data from a CAS table, you might just want to export the data to a file. To make this easier, the CASTable objects support the same to_XXX methods as Pandas DataFrames. This includes to_csv, to_dict, to_excel, to_html, and others. Behind the scenes, the fetch action is called and the resulting DataFrame is exported to the file corresponding to the export method used. Let's look at some examples.

First we need a connection to the server.

In[1]: import swat

In[2]: conn = swat.CAS(host, port, username, password)

For purposes of this example, we will load some data into the server to work with. You may already have tables in your server that you can use.

In[3]: tbl = conn.read_csv('https://raw.githubusercontent.com/sassoftware/sas-viya-programming/master/data/cars.csv')

In[4]: tbl
Out[4]: CASTable('_T_FBA0C047_7F277C6870C0', caslib='CASUSERHDFS(kesmit)')

In[5]: tbl.head()

Screen Shot 2016-08-12 at 1.58.00 PM.png

Now that we have a CASTable object to work with, we can export the data from the CAS table that it references to a local file. We'll start with CSV. The to_csv method will return a string of CSV data if you don't specify a filename. We'll do it that way in the following code.

In[6]: print(tbl.to_csv())
,Make,Model,Type,Origin,DriveTrain,MSRP,Invoice,EngineSize,Cylinders,Horsepower,MPG_City,MPG_Highway,Weight,Wheelbase,Length
0,Acura,MDX,SUV,Asia,All,36945.0,33337.0,3.5,6.0,265.0,17.0,23.0,4451.0,106.0,189.0
1,Buick,Rendezvous CX,SUV,USA,Front,26545.0,24085.0,3.4,6.0,185.0,19.0,26.0,4024.0,112.0,187.0
2,Chrysler,300M 4dr,Sedan,USA,Front,29865.0,27797.0,3.5,6.0,250.0,18.0,27.0,3581.0,113.0,198.0
3,GMC,Envoy XUV SLE,SUV,USA,Front,31890.0,28922.0,4.2,6.0,275.0,15.0,19.0,4945.0,129.0,208.0
4,Isuzu,Rodeo S,SUV,Asia,Front,20449.0,19261.0,3.2,6.0,193.0,17.0,21.0,3836.0,106.0,178.0
5,Lincoln,Town Car Signature 4dr,Sedan,USA,Rear,41815.0,38418.0,4.6,8.0,239.0,17.0,25.0,4369.0,118.0,215.0
... output truncated ...

The to_html method works the same way.

In[7]: print(tbl.to_html())
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Make</th>
      <th>Model</th>
      <th>Type</th>
      <th>Origin</th>
      <th>DriveTrain</th>
      <th>MSRP</th>
      <th>Invoice</th>
      <th>EngineSize</th>
      <th>Cylinders</th>
      <th>Horsepower</th>
      <th>MPG_City</th>
      <th>MPG_Highway</th>
      <th>Weight</th>
      <th>Wheelbase</th>
      <th>Length</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>Acura</td>
      <td>MDX</td>
      <td>SUV</td>
      <td>Asia</td>
      <td>All</td>
      <td>36945.0</td>
      <td>33337.0</td>
      <td>3.5</td>
      <td>6.0</td>
      <td>265.0</td>
      <td>17.0</td>
      <td>23.0</td>
      <td>4451.0</td>
      <td>106.0</td>
      <td>189.0</td>
    </tr>
... output truncated ...
  </tbody>
</table>

Finally, for the LaTeX users out there, you can export to that as well.

In[8]: print(tbl.to_latex())
\begin{tabular}{llllllrrrrrrrrrr}
\toprule
{} &           Make &                                    Model &    Type &  Origin & DriveTrain &      MSRP &   Invoice &  EngineSize &  Cylinders &  Horsepower &  MPG\_City &  MPG\_Highway &  Weight &  Wheelbase &  Length \\
\midrule
0   &          Acura &                                      MDX &     SUV &    Asia &        All &   36945.0 &   33337.0 &         3.5 &        6.0 &       265.0 &      17.0 &         23.0 &  4451.0 &      106.0 &   189.0 \\
1   &          Buick &                            Rendezvous CX &     SUV &     USA &      Front &   26545.0 &   24085.0 &         3.4 &        6.0 &       185.0 &      19.0 &         26.0 &  4024.0 &      112.0 &   187.0 \\
... output truncated ...
\bottomrule
\end{tabular}

There are many other to_XXX methods on the CASTable object, each of which corresponds to the same to_XXX method on Pandas DataFrames. The CASTable methods take the same arguments as the DataFrame counterparts, so you can read the Pandas documentation for more information.

Resources

You can download the Jupyter notebook version of this article at https://github.com/sassoftware/sas-viya-programming/tree/master/communities.