Even Me: How to Modify Your Excel Data to Easily Slide into Spotfire

Yup. That was my last time. Popping a stack of Fortran cards into a reader and watching my do-loop generate infinite amounts of paper, chunking madly out of a central machine while everyone hovered and watched and sighed because their cards had yet to run. Yup, that’s my last recollection of computer technology. Humiliation. I was a chemistry major and we had to do one computer class. I did it and was done.

Not quite. Now I’m sitting at my desk discovering Spotfire. I need to do this because I work for a data analytics company, albeit the ex-dancer now business administrator of the company, and everyone here does Spotfire. Everyone here does Spotfire, splendidly.

But I’m excited. I’m entering the world of technology, big time. Oh sure I know my way around twitter or facebook, even a few crazy, technoapps. But now, this is real. This is the stuff of computer wizardry. I’m ready to join the ranks of those that make computers really work and data come alive.

Tutorial 1

It matters how you add data.  It matters what the column headers say.  It matters.  All of the details matter.  First thing I learned—You have to get that data table information just so, or you’ve got just nothing.

I imported a CSV, an excel-ly type, file of profit and loss data to do my financial report.  I wanted to create a beautiful bar/line chart with each rainbowed expense rising from the X axis, and an associated profit line catapulting like an arrow across the multi-colored costs beneath.

But my data just said, Column 1, Column 2, Column 3, etc. to Column 12 across the top, and down the left side was row after row of all my interesting information which included titles like months, income, wages, advertising, taxes, supplies, rent, software, travel, and so on.  In the middle of the grid the column names hovered over pertinent row information.

Spotfire was unimpressed.  In that small insert box that comes up, oh so helpfully, with the presumptuous title of ‘Something You Might Want’—subtitle, If You Knew What You Were Doing—I had a graph which plotted Column 1 through Column 12 on the X-axis and income on the y-axis, which gave me sixteen equal blobs of blue.

I was unimpressed.  It was time to call in a life-line.  So I drew an actual pen on paper picture of what I wanted and walked down the hall to the intern—a recent Rice graduate who picked up Spotfire in thirty easy minutes and has gone on in his first week to create numerous program-like products.  I’ve been here a year.  Grrrr…..

He looked at my graph and my data and immediately saw my problem.  You have to transpose the data, he said to me in his everyone-should-know-this voice. (I’d like to see him tango around a dance floor backwards.)  Transpose, I repeated knowingly.  Right.

But it turned out to be, not just a great answer, but the answer.  I flipped that data table on it’s side, literally.  I put important stuff across the top like profit and expenses and just for a layer of added excitement, total expenses.  Then each row became the months of the year with all the right values spilling easily and neatly into their correct place in my brilliant new data table.

With the data all hunkered down in correct orientation, those graphs just spilled out across the page nice and easy.  I clicked the bar/line graph button and whamo, months were strewn across the x axis.  I pumped that y axis drop down menu, snagged the Total Profit and I had blue bars of varying sizes.  Then for the final touch, I added Total Expenses on the second y-axis drop down menu.  Suddenly I had blue bars and green bars.  So, I moused over to the ‘Series by’ on the right hand side of the graph, and desiring Total Profits as my line, clicked on the blue bars, up comes another drop down menu, and I selected in very excellent data analytics style, a lovely blue line.  (Got lost?  That’s all good–instructions and visuals are included below.)

There it was.  Right there.  Beautiful green bars rising at appropriate heights from an X-axis complete with month by month expense information.  And over all, a blue line heralding the Total Profits for anyone, anyone who looked at my colorful construction to see.  Yes, I could do it.  Even me.

Here’s what I did:

Start up excel.  Here’s my example data.

transpose

    1. In excel, erase all the rows of information you don’t need.
    2. Select all data
    3. Right-click, Copy
    4. Select an empty cell below your data
    5. Right-click and under the Paste options click Transpose
    6. Select Data above your new table, Right-click delete

Import the new data table into Spotfire.

spotfire

  1. Click off the box that comes up with recommendations—you can do better than that.
  2. Click on the bar/line graph.
  3. The X-axis will be the months.
  4. For the Y-axis, Gross Profit is already indicated blue bars for each month.
  5. Next, click on the drop down menu on the next arrow head on the Y axis.  Click on Total Expenses.  Now you’ll have green bars next to your blue bars.
  6. But wait!  Go right and hit the blue bars under the Series By.  You’ll get a box.  In that box click on ‘show as lines.’  Do it!  Now you have green bars, a blue line, and a beautiful graph that says something very nice.

Hope this helps you next time you find yourself with excel data that just won’t quite fit properly into Spotfire.

You can download the files for this tutorial here.

1 thought on “Even Me: How to Modify Your Excel Data to Easily Slide into Spotfire

  1. Robert Walker Reply

    Great post. After going through the tutorial, I thought there has got to be a way to do all the transposing steps inside of Spotfire from the raw data. I figured out a way and thought I would share. The use case for this is for a template for data that one might receive in a certain format on a continual basis and need to transpose it when importing into Spotfire.

    1. Start the Analysis file using the “Add Data Tables” option instead of “Open File”
    2. Select Add -> File
    3. The file will load the like the steps in your tutorial with the “Import Settings” screen, however when you click ok it takes you back to the Add Data Tables screen.
    4. Now we need to Add Transformations to perform the transpose operation. We this by selecting the appropriate transformation from the drop-down list in the Transformations section. Then Select add.
    5. Configure the transformation and select ok.
    6. Repeat for the second and third required transformations.
    7. Click OK to load the data.

    Here are the three transformations to add in the following order.
    1. Unpivot
    Columns to pass through: 2015 – 2016
    Columns to transform:
    Jan
    Feb
    Mar
    Apr
    May
    Jun
    Jul
    Aug
    Sep
    Oct
    Nov
    Dec
    Category column name: Column
    Category column data type: String
    Value column name: Value
    Value column data type: Integer
    Empty values included: Yes

    2. Pivot
    Row identifiers: Column
    Value columns and aggregation methods: none(Value)
    Column titles: 2015 – 2016
    Column naming pattern: %C
    Transfer columns and aggregation methods: (None)
    Transfer column naming pattern: %A(%T)

    3. Change Column Names
    Columns to rename: Column
    Expression: Substitute([%C],[%C],”2015-2016″)

Leave a Reply

Your email address will not be published. Required fields are marked *