Data is data. The right tool for the job is the one that’s available. It can be PowerBI, Tableau, Looker, Qlik Sense, even Excel is eligible and sometimes sexy. When someone mentioned whether I’ve seen an SPSS file, I gasped and exclaimed, “NO!”
Which is why we’re exploring with SPSS on Tableau and Python.
What is SPSS?
SPSS is an IBM statistics platform. The behavior of the system is that what we’d expect with most statistical programs. It offers linear regression, summarizes your data, allows for slight data transformations, and does it all in one application.
LinkedIn learning offers some lessons on SPSS. To gain familiarity with the application, I took this course. As of this writing, IBM has a free trial offer for their SPSS statistics platform. This trial offer is where and how I’ve completed most of this work. If you’ve already acquired an IBMiD, the download and installation is simple. Download the executable and follow the instructions.
Exporting SPSS for BI Analytics
The LinkedIn learning course I took was able to bring about many uses for the SPSS application. SPSS is especially fun for the way it simplifies our transformation process by treating column headings and variables with as meta data. That is to say, the application allows for one to define our categorical variables on a separate page, with numerical representations as is needed for most regression/ai models – while allowing a sort of comfort, so we can turn off/on the friendly column names as needed for our analysis. A demo of what I’m referring is pictured below.
This is great when we are sticking with SPSS, but what happens when want to perform an analysis on our transformed data outside of this application?
When searching for PowerBI options with SPSS files, there is one thread. The links seem to tell me that PowerBI does not offer any support for SPSS file types. I did open my PowerBI connection utility up and found that SPSS does not exist as a data connector or optional import source.
SPSS, itself offers the ability to export the data as a csv file. The data, however, does not include the metadata. This means, that you’ll need to work with PowerQuery or inside of a spreadsheet application like Excel to knit the data back together.
Tableau can read a statistics file but has the same restrictions as Excel or csv files. Their documentation indicates that this is with intention. For our explorative purposes, I opened the demo.sav file (found at SPSS Statistics\Samples\English) that is provided with the SPSS application in Tableau. When observing the internet column for this file, I anticipate four values “No, Yes, Does not know, No Answer.” Fields corresponding to the 0 and the 1 are custom value labels while fields responding to 8 or 9 are what are referred to missing values. As these are interpreted as missing, they do not appear in our exported csv file. They also do not appear in Tableau.
Pyreadstat and Python
A Google search returned a post that introduces one to Pyreadstat, a Python package that can not only read an SPSS model but can access the meta data that is associated with those column names. The package does include a disclaimer:
The good news, this exploration is not meant to report to the authorities. It’s just here to serve as an opportunity to learn how to obtain that metadata if I want to explore this data in another BI tool without losing those missing and custom values. Below is the gist for accessing and saving the demo.sav dataframe to csv for use with Tableau.
Update the path variable to your example sav file in order to run this this gist.
For more information on working with Pyreadstat, their documentation is available on Github.
For the Love of Technology
When it comes to technology, we will always be met with new challenges. This time around, I was fortunate enough to merely need to take a look at something. It’s always fascinating to see where one would never expect data to disappear, it’d go missing. In my journey and first look at this SPSS application – even during the course that was in front of me, I’d never would have expect there would be that small bit of data loss.
This is why I’m glad I took the time to explore with SPSS on Tableau and Python.
Leave a Reply