I'm working on a dataframe consisting of 528 column and 2,643,246 rows. Eight of these are character-variables, and the rest integers. In total, this adds up to 11.35 GiB of data, with my available RAM being at 164 GiB.
I now wanted to run a pivot_longer on said dataframe, having one row for each column + two ID variables (year and institution). There are a total of 671,370 institutions over 76 years.
So atm the data are structured such as this:
| Institution | Year | X | Y | Z |
|---|---|---|---|---|
| A | 1 | 2 | 1 | 3 |
| A | 2 | 3 | 4 | 4 |
| B | 1 | 3 | 4 | 2 |
| B | 2 | 5 | 3 | 2 |
Where I would like to change it so the structure becomes:
| Institution | Year | G | N |
|---|---|---|---|
| A | 1 | X | 2 |
| A | 1 | Y | 1 |
| A | 1 | Z | 3 |
| A | 2 | X | 3 |
| A | 2 | Y | 1 |
| A | 2 | Z | 4 |
| B | 1 | X | 3 |
| B | 1 | Y | 4 |
| B | 1 | Z | 2 |
| B | 2 | X | 5 |
| B | 2 | Y | 3 |
| B | 2 | Z | 2 |
To achieve this I attempted the following code:
library(tidyverse)
Df <- Df %>% pivot_longer(17:527,
names_to = "G",
values_to = "N"
)
When running this on a small sample-data I manage to achieve the expected results, however when attempting to do the same on the whole dataset I quickly run out of memory. From the object using 11 GiB of memory, it quickly increased to above 150 GiB before returning a "cannot allocate vector of size x Gb" error.
Since I haven't added any data, I can't quite understand where the extra memory usage is coming from. What I wonder therefore is what creates this increase, and whether there is a more efficient way to solve this problem through some other code. Thanks in advance for any help!