convert a vector of string with many chunks to a data frame with a header


I have a string vector imported from excel file as a single string "\r\n\r\nName\r\n\r\nPosition\r\n\r\nAge\r\n\r\nGender\r\n\r\nSam\r\n\r\nManager\r\n\r\n30\r\n\r\n Male\r\n\r\nMira\r\n\r\nHead\r\n\r\n25\r\n\r\nFemale\r\n\r\nMario\r\n\r\n40\r\n\r\nDirector\r\n\r\n Maria\r\n\r\n25\r\n\r\nOfficer\r\n\r\nFemale"

I am looking to convert this string to a data frame with column names are coming from first four values (Name, Position, Age, Gender). if there any missing cells to be Null or NA.

the table to be

Name |Position |Age |Gender

Sam |Manager |30 |Male

Mira |Head |25 |Female

Mario |Director |40 |NA

Maria |Officer |25 |Officer

I split the data into a vector with many rows,

bb <- c("\r\n\r\nName\r\n\r\nPosition\r\n\r\nAge\r\n\r\nGender\r\n\r\nSam\r\n\r\nManager\r\n\r\n30\r\n\r\n Male\r\n\r\nMira\r\n\r\nHead\r\n\r\n25\r\n\r\nFemale\r\n\r\nMario\r\n\r\n40\r\n\r\nDirector\r\n\r\n Maria\r\n\r\n25\r\n\r\nOfficer\r\n\r\nFemale")

bb_split <- bb %>% str_split(pattern ="\r\n\r\n") %>% unlist()

Then I tried many ways to add a column with a number and try to group rows with this number, but it turned into having a nest list without reaching to my gaol.

I appreciate your support.


What makes this very difficult is the missing values and the changing order of the values. The column headers are Name, Position, Age Gender but the last two people seem to be in the order Name, Age, Position, Gender. Is that really how the data are?
Is there a short list of possible values for Position?

The data is received through email in HTML format, I tried to use RDCOMClient package to get the data from outlook to R but it though an error. and I couldn't solve that error. so I decided to use Power Automate from Microsoft to download specific email to Excel on OneDrive. so I can extract the data, which now in that format. I don't know if there any better and simple way to get the data from Outlook directly to R

This topic was automatically closed 21 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.