0

I have the following columns' names of dataframe in year/week format

cols = colnames(vci1981.df)

 [1] "gid"     "xcoord"  "ycoord"  "col"     "row"     "gwno"    "country" "km2"     "1981035" "1981036"
[11] "1981037" "1981038" "1981039" "1981040" "1981041" "1981042" "1981043" "1981044" "1981045" "1981046"
[21] "1981047" "1981048" "1981049" "1981050" "1981051" "1981052"

Note: year 1981- week 0xx

How can I transform them into the corresponding year-month format please ?

Example: 1981-035 = 1981-08

I tried the same method as here Transform year/day colnames into date in r without success.

cols = ymd(parse_date_time(cols[9:length(cols)], orders="yw"))

I got NA here.

and

cols = as.Date(cols[9:length(cols)], "%Y%W")

I got non corresponding values here: [1] "1981-06-29" "1981-06-29" "1981-06-29" "1981-06-29" "1981-06-29" "1981-06-29" "1981-06-29" "1981-06-29" [9] "1981-06-29" "1981-06-29" "1981-06-29" "1981-06-29" "1981-06-29" "1981-06-29" "1981-06-29" "1981-06-29" [17] "1981-06-29" "1981-06-29"

Myr TH
  • 175
  • 1
  • 9

1 Answers1

4

Try this:

## Creating the Dataframe
cols = c( "gid","xcoord","ycoord","col","row","gwno","country","km2","1981035","1981036",
       "1981037","1981038","1981039","1981040","1981041","1981042","1981043","1981044","1981045",
       "1981046","1981047","1981048","1981049","1981050","1981051","1981052"
)
df = data.frame(ColNames = cols)

## Creating empty list
df1=list()
df2=list()

## For loop to extract the year and the week
for (i in 9:nrow(df)){
  a = substring(df$ColNames[i],1,4)
  b = substring(df$ColNames[i],5,nchar(df$ColNames[i]))
  df1 = append(df1,a)
  df2 = append(df2,b)
}

## unlisting all lists
df1=unlist(df1)
df2=unlist(df2)

## Converting month number to digit month number
df2 = sprintf("%02d",sapply(strsplit(df2, ''), function(x) sum(as.numeric(x))))


## Replacing original values with new formatted values
cols[9:length(cols)] = paste(df1,"-",df2, sep="")

Output

> cols
 [1] "gid"     "xcoord"  "ycoord"  "col"     "row"     "gwno"    "country" "km2"     "1981-08" "1981-09" "1981-10" "1981-11" "1981-12"
[14] "1981-04" "1981-05" "1981-06" "1981-07" "1981-08" "1981-09" "1981-10" "1981-11" "1981-12" "1981-13" "1981-05" "1981-06" "1981-07"
Deepansh Arora
  • 724
  • 1
  • 3
  • 15
  • 1
    Hello! If this answer meets your requirement, please consider clicking the tick mark on the left side to accept the answer. This will help the other users who are struggling with the same issue. Thanks! – Deepansh Arora Jun 29 '22 at 17:54
  • when I try to work with the data I have a problem. cols = cols[9:length(cols)] = paste(df1,"-",df2, sep="") names(vci1981.df[9:26]) = paste(cols) Then: vci1981.df2 = pivot_longer( vci1981.df, cols = c(9:26)) and I got the following message "Error in `loc_validate()`: ! Can't subset columns past the end. ℹ Locations 19, 20, 21, 22, 23, … don't exist. ℹ There are only 18 columns. " Do you know why? – Myr TH Jun 30 '22 at 08:56
  • Is it possible that you could share a subset of your data like first 6 rows? You could put it on a google drive and share the link here in comments and I can see and resolve the issue. @MyrTH – Deepansh Arora Jun 30 '22 at 13:22
  • Sure, here: https://docs.google.com/document/d/1mgIEs3JBqFbUIsP4KO1no-6DKNJhkbq6IHX86jM_dN0/edit – Myr TH Jun 30 '22 at 18:09
  • @MyrTH I just sent you request for access. – Deepansh Arora Jun 30 '22 at 18:32
  • Try this: ```df %>% pivot_longer(!c(gid,xcoord,ycoord,col,row,gwno,country,km2), names_to="Year-Week",values_to = "Values")``` where df is your dataset – Deepansh Arora Jun 30 '22 at 18:48
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/246080/discussion-between-deepansh-arora-and-myr-th). – Deepansh Arora Jun 30 '22 at 19:35
  • Unfortunately, the dates are wrong. It starts well but then it goes messy. So I have found a way. I first, replace the '0' between the year and the week by a dash. Then: as.Date(str_c(cols, "-1"), format="%Y-%U-%u") as related to this post https://stackoverflow.com/questions/45549449/transform-year-week-to-date-object/45587644#45587644. My only concern, is that I would have preferred an ISO format but could not find a way regarding my context. – Myr TH Jul 01 '22 at 09:48