How to create a factor variable from a data.frame and plot the columns on a side-by-side plot

Question

Some of you this could be an easy question.

I have 2 data frames:

dput(head(Activitieslessthan35))

structure(list(`Main job: Working time in main job` = c(470, 
440, 430, 430, 410, 150), Sleep = c(420, 450, 450, 420, 450, 
460), `Unspecified TV video or DVD watching` = c(60, 40, 210, 
190, 60, 0), Eating = c(80, 60, 40, 70, 60, 130), `Other personal care:Wash and dress` = c(60, 
60, 50, 50, 70, 50), `Travel to work from home and back only` = c(60, 
60, 50, 90, 90, 30), `Unspecified radio listening` = c(140, 180, 
50, 90, 140, 160), `Other specified social life` = c(350, 270, 
310, 330, 710, 440), `Socialising with family` = c(350, 270, 
360, 330, 730, 540), `Food preparation and baking` = c(410, 310, 
420, 380, 1000, 950)), row.names = c(NA, 6L), class = "data.frame")

and

   dput(head(ActivitiesMoreOrEqual35))

structure(list(`Main job: Working time in main job` = c(360, 
420, 390, 490, 540, 390), Sleep = c(590, 480, 310, 560, 280, 
370), `Unspecified TV video or DVD watching` = c(100, 60, 130, 
120, 60, 30), Eating = c(70, 100, 70, 40, 190, 80), `Other personal care:Wash and dress` = c(10, 
30, 100, 60, 270, 90), `Travel to work from home and back only` = c(0, 
50, 260, 50, 0, 0), `Unspecified radio listening` = c(50, 80, 
260, 80, 210, 200), `Other specified social life` = c(190, 320, 
790, 250, 580, 420), `Travel in the course of work` = c(50, 80, 
260, 70, 120, 200), `Food preparation and baking` = c(440, 570, 
820, 570, 820, 590)), row.names = c(NA, 6L), class = "data.frame")

I would like to convert the data.frames into factors - for example to have a factor variable called Activitieslessthan35 with colums of the data frame to be used as levels such as `Main job: Working time in main job', 'Sleep', etc. Later I would like also to plot (the sum) the levels of the factors on a side-by-side bar plot.

I don't know if you care transform a data.frame into factor variable as well how to change the format of the data.frames to create the plot

Any suggestion is welcome

Answer 1

If I understand well, you want to have both of your dataframe in a long format of two columns, one column containing all colnames of your dataframe, and the second column with all values, then summarise each "factor" of the first column, merging both dataframes and plotting both dataframes into a single plot. Am I right ?

Here a way to do it. I called df the dataframe Activitieslessthan35 and df2 the dataframe ActivitiesMoreOrEqual35.

First, we are going to transpose to a long format each of your dataframe using pivot_longer

library(tidyr)
library(dplyr)
df <- df %>% pivot_longer(everything(), names_to = "Activities", values_to = "Values_less_than35")
df2 <- df2 %>% pivot_longer(everything(),names_to = "Activities", values_to = "Values_More_than_35")

Then, we will calculate the sum value for each factor of each of your dataframe:

df_sum = df%>% group_by(Activities) %>% summarise(Values_less_than35 = sum(Values_less_than35))
df2_sum = df2 %>% group_by(Activities) %>% summarise(Values_More_than_35 = sum(Values_More_than_35))

Then, we are merging both dataframe into a singe one by using "Activities" as merging columns

final_df = merge(df_sum,df2_sum, by.x = "Activities", by.y = "Activities", all = TRUE)

Finally, we are transposing one last time final_df in order to have values in the correct shape for plotting them with ggplot2

final_df <- final_df %>% pivot_longer(., -Activities, names_to = "Variable", values_to = "Value")

And now we can plot your final dataframe using ggplot2

library(ggplot2)
ggplot(final_df, aes(x = stringr::str_wrap(Activities, 15), y = Value, fill = Variable)) +
  geom_col(stat = "identity", position = position_dodge()) +
  coord_flip()+
  xlab("")

And you get the following plot:

Does it look what you are expecting ?