Melting and Transposing Data

Melting data

Another transformation we can do to data is “melt” it. Before looking at the method to melt data, let’s discuss the structure of data. Two ways to organize the same data are “wide” (also called “stacked” or “record” form) and “long” (sometimes called “tidy” form) data. (Note that this is different from “big data,” which refers to the amount of data.)

An OLAPOnline Analytical Processing database is an analytical database optimized for reporting. In OLAP terms, there is a notion of a fact and a dimension. A fact is a value that is measured and reported on, and a dimension is a value that describes the conditions of the fact. There are often multiple dimensions for a fact. In a sales scenario, typical facts would be the number of sales of an item and the cost. The dimensions might include the store where the item was sold, the date, and the customer.

The dimensions can then be sliced to explore the data. We might want to view sales by store. A dimension may be hierarchical—for example, a store could have a region, zip code, and state—and we could view sales by any of these dimensions.

Here’s data that tracks students’ ages and scores. The test columns are fact columns, and the other columns are dimensions: