BookmarkSubscribeRSS Feed

Exporting Data from CAS using Python

Started ‎04-13-2016 by
Modified ‎04-16-2019 by
Views 731

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_csvto_dictto_excelto_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.

Version history
Last update:
‎04-16-2019 09:06 AM
Updated by:
Contributors

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags