Reshaping panel data with long_panel() and widen_panel()

Wave indicators at the end of variable names

Let’s return to the wide data we looked at earlier.

Here we can see that the time indicators are at the end of the variable names (_W1), time indicators of 1, 2, and 3, and a prefix of _W. With that in mind, we can use long_panel() to convert the data to long format.

long_panel(wide, prefix = "_W", begin = 1, end = 3, label_location = "end")

Perfect! The first argument, w, was the wide data. prefix is self-explanatory. begin and end refer to the range of the time indicators, since they are consecutive. You can instead use periods = c(1, 2, 3) if you prefer. That’s especially true if you have non-consecutive time indicators like a biannual survey that uses the year as an indicator, like periods = c(1990, 1992, 1994).

Comparing with base R

I should note that base R has a function, reshape() that can work in this situation without making you pull your hair out too much:

reshape(as.data.frame(wide), sep = "_W", times = c(1, 2, 3), direction = "long",
        varying = c("Q1_W1", "Q1_W2", "Q1_W3", "Q2_W1", "Q2_W2", "Q2_W3"))

You can see how frustrating that could be if you had many more variables — it wouldn’t be unusual to have hundreds of columns in the wide format, not all of which would be variables that vary over time (e.g., race). Truth be told, long_panel() uses reshape() internally, but only after a lot of processing. Other options include the reshape2 and tidyr packages, but they are not purpose-built for the panel setting and therefore can have a learning curve to avoid having data that end up a bit too long.

A more challenging example

Here’s a wide dataset with what is usually a trickier format to handle due to limitations of reshape().

Key characteristics:

• Wave indicators are at the beginning of the variable names.
• The time indicators are letters, from A to C.
• There is a prefix (W) and suffix (_).

While you don’t have to recognize this to use the function properly, notice that in this case Q2 was only measured at times A and C. This can add considerable difficulty to when trying to reshape data “by hand.”

long_panel(wide, prefix = "W", suffix = "_", label_location = "beginning",
           begin = "A", end = "C")

Just what we were looking for. Note that panel_data objects must have an ordered wave variable, but long_data() understands how to order letters and handles that for you. The missingness in Q2 is by design, since it wasn’t measured in wave B.

Another issue that can come up is the treatment of constants — that is, variables that do not change over time. The best wide data should come labeled in a way that makes it clear the constants are constants. For instance, a variable signifying race wouldn’t be called race_W1, but instead just race. long_panel() automatically checks your data for variables that are labeled as if they vary over time but actually do not.

For instance, data that start by looking like this:

Can easily end up shaped like this:

But obviously just because the wide data marked race with a wave label, that doesn’t mean it was unknown in the other waves. You’ll get the right result with long_panel():

long_panel(wide, prefix = "_W", label_location = "end", begin = 1, end = 3)

Other details

If you have an ID variable in the wide data, you can pass the name of that variable to long_panel() with the id argument, which is "id" by default. If there is no variable with the name you give to id, one will be created. You can also choose the name of the wave variable via wave, which is "wave" by default.

You can also choose not to have the output of long_panel() be a panel_data object by setting as_panel_data to FALSE.

Advanced options

There are some other options available to you for tougher cases. For instance, when use.regex is TRUE, the arguments for prefix and suffix are treated as regular expressions for more complicated patterns.

Internally, time-varying variables are detected by the presence of prefix, one of the time periods, and suffix in the variable name. The “root” variable without the indicator is whatever is left. Sometimes, though, this can cause false matches. Here’s an example I have encountered. My wide data looked like this:

My ID variable was called CaseID and the periods — which were A, B, and C — were labeled at the beginning of the column names. Following the earlier examples, this will confuse long_panel():

long_panel(wide, begin = "A", end = "C", label_location = "beginning", id = "CaseID")

See what happened? The Consent variable in the wide data looked just like a constant variable that was measured at time point C. This isn’t the end of the world, but errors like this can be more confusing and damaging in other scenarios. Fortunately, I knew more about the labeling of the time-varying variables than what I told long_panel(). Yes, there is A/B/C at the beginning with no prefix/suffix, but also each time-varying item has a number that comes after A/B/C.

long_panel() offers the argument match for situations like these. This is the regular expression used to match and then capture the variable name sans time indicator. By default, match is ".*", meaning any character any number of times. To reflect what I know about these data, I change it to "\\d+.*", meaning at least one digit following by any number of other characters.

long_panel(wide, begin = "A", end = "C", label_location = "beginning", 
           id = "CaseID", match = "\\d+.*")

Now it rightly ignores Consent as a variable that lacks a time indicator. In general, long_panel() tries to protect you from having to use or even know how to use regular expressions, but sometimes there’s no way around it.