Separating one column into many as well as separating it's label to label all the new columns

I have this file, it has thousands of lines but one column, it's label lists what each column should be named (each one separated by a dot "." ) and the lines have the data separated by ";"
I don't really know how to capture the label and separate it and how to separate each column into many, the separate function seems to need the number of columns as an input but i need to split the label to count how many cols i need.

Here is an example with a toy data set of what I think you want to do.

library(stringr)
library(tidyr)
DF <- data.frame(First.Second.Third.Fourth = c("Here;234.24;There;Where",
                                               "How;0.94;Why;Who"))
DF
#>   First.Second.Third.Fourth
#> 1   Here;234.24;There;Where
#> 2          How;0.94;Why;Who
Nms <- as.vector(str_split_fixed(colnames(DF),pattern = "\\.", n=4))
Nms
#> [1] "First"  "Second" "Third"  "Fourth"
DFnew <- separate(DF, col = 1, into = Nms, sep = ";")
DFnew
#>   First Second Third Fourth
#> 1  Here 234.24 There  Where
#> 2   How   0.94   Why    Who

Created on 2023-01-05 with reprex v2.0.2

1 Like

Oh my god! Yes, this mostly works, now i realized the CSV file has lines where the info was separated into new lines instead of divided by the dot ".", this means that before i do your method i gotta make these split lines whole.
I guess i need to go line by line and if they don't start with a number i need to make them go into the previous line with the dot, i don't know the best way.
Should i make a new topic?


Here it happens in line 287, lines 288 and 289 are actually info from line 287 that got into their own lines

I think a new topic for this new problem is appropriate.

By the way, you should be able to read the original file with the read.csv2() file with skip=1 and header=FALSE.

DF <- read.csv2("Your_File_Name", skip = 1, header = FALSE)

That function assumes the separator is a semicolon.

1 Like

Oh, sorry, It works as intended, i just needed to find out how many cols i had and it worked. You're a life saver.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.