reshaping dataframe before plotting the evolution of variables

August 2, 2022

I would like to plot the evolution of a lot of economic aggregates from a dataframe (if possible, by faceting to have one plot per variable). First, I need to rearrange my dataframe, changing rows into columns, but I can’t seem to manage to do it. Does anyone know how to proceed ?

My dataframe has the following structure :

 bilan_consolide               31/12/2021    31/12/2020   31/12/2019

 Goodwill                        28031         35335       72 267
 Other intangible assets         49034         61385       73 408
 Property, Plant and Equipment   1589041       1688724     1 923 267

My desired output would be something like that :

                 Goodwill       Other intangible assets

 31/12/2019       72 267             73 408
 31/12/2020       35335              61385

I have tried this, without success (it does not give me the output I want at all) :

library(reshape)
data_wide <- reshape(test_2,direction="wide", idvar = "bilan_consolide", timevar = 
"Variable 1")
data_wide
df <- data.frame(x = seq_along(test_2[, c(2:4)]),
             test_2)
df <- melt(df, id.vars = "x")

For reproducibility, here is the head of my dataframe test_2 :

 structure(list(bilan_consolide = c("NON-CURRENT ASSETS", "Goodwill", 
 "Other intangible assets", "Property, Plant and Equipment", "Right of use", 
 "Investment property"), `31/12/2021` = c("", "28031", "49034", 
 "1589041", "1429100", "104935"), `31/12/2020` = c("", "35335", 
 "61385", "1688724", "1186918", "102998"), `31/12/2019` = c("", 
 "72 267", "73 408", "1 923 267", "1 251 255", "116 267")), row.names = c(NA, 
 6L), class = "data.frame")

>Solution :

A combination of pivot_longer() and pivot_wider() will suffice:

library(tidyverse)
#> Warning: package 'tidyverse' was built under R version 4.2.1
#> Warning: package 'tibble' was built under R version 4.2.1

df <-  structure(list(bilan_consolide = c("NON-CURRENT ASSETS", "Goodwill", 
                                          "Other intangible assets", "Property, Plant and Equipment", "Right of use", 
                                          "Investment property"), `31/12/2021` = c("", "28031", "49034", 
                                                                                   "1589041", "1429100", "104935"), `31/12/2020` = c("", "35335", 
                                                                                                                                     "61385", "1688724", "1186918", "102998"), `31/12/2019` = c("", 
                                                                                                                                                                                                "72 267", "73 408", "1 923 267", "1 251 255", "116 267")), row.names = c(NA, 
                                                                                                                                                                                                                                                                         6L), class = "data.frame")
df |> 
  as_tibble() |> 
  pivot_longer(cols = -bilan_consolide,
               names_to = "date",
               values_to = "value") |> 
  pivot_wider(names_from = bilan_consolide,
              values_from = value) |> 
  arrange(date)
#> # A tibble: 3 × 7
#>   date       `NON-CURRENT ASSETS` Goodwill `Other intangible …` `Property, Pla…`
#>   <chr>      <chr>                <chr>    <chr>                <chr>           
#> 1 31/12/2019 ""                   72 267   73 408               1 923 267       
#> 2 31/12/2020 ""                   35335    61385                1688724         
#> 3 31/12/2021 ""                   28031    49034                1589041         
#> # … with 2 more variables: `Right of use` <chr>, `Investment property` <chr>

^{Created on 2022-08-02 by the reprex package (v2.0.1)}