Data Wrangling in R with DateTime column and Multiple observations

Question

I have a commonly formatted data set in a CSV file that I read into R as either a tibble or data.frame. Since this comes from elsewhere it is in a wide format like this with the first row being the header of the data frame:

datetime City1 City2 City3 City4
2020-01-01 00:15 2.3 2.6 2.1 2.2
2020-01-01 00:30 1.1 1.8 1.6 1.2
...

To make use of the data for further analysis in R, it needs to be in a long format where the datetime is repeated, so it should be converted to something like this (with a new header):

datetime Location Value
2020-01-01 00:15 City1 2.3
2020-01-01 00:15 City2 2.6
2020-01-01 00:15 City3 2.1
2020-01-01 00:15 City4 2.2
2020-01-01 00:30 City1 1.1
2020-01-01 00:30 City2 1.8
2020-01-01 00:30 City3 1.6
2020-01-01 00:30 City4 1.2
...

This seems like such a common data wrangling task but I've not been able to find a good example after readying through pivot_longer documentation (it's also possible I don't fully understand the provided examples).

Answer 1

Does this answer:

> df
# A tibble: 2 x 5
  datetime            City1 City2 City3 City4
  <dttm>              <dbl> <dbl> <dbl> <dbl>
1 2020-01-01 00:15:00   2.3   2.6   2.1   2.2
2 2020-01-01 00:30:00   1.1   1.8   1.6   1.2
> df %>% pivot_longer(cols = starts_with('City'), names_to = 'Location')
# A tibble: 8 x 3
  datetime            Location value
  <dttm>              <chr>    <dbl>
1 2020-01-01 00:15:00 City1      2.3
2 2020-01-01 00:15:00 City2      2.6
3 2020-01-01 00:15:00 City3      2.1
4 2020-01-01 00:15:00 City4      2.2
5 2020-01-01 00:30:00 City1      1.1
6 2020-01-01 00:30:00 City2      1.8
7 2020-01-01 00:30:00 City3      1.6
8 2020-01-01 00:30:00 City4      1.2
> 

Answer 2

I hope that helps:

library(data.table)
df <- setDT(your_df)
melt(df,
     id.vars='datetime',
     variable.name = 'Location', 
     value.name = 'Value')