Transform a list into a dataframe with multiple columns in R

Question

I have a list of names and categories, as follows:

Name
Country
Role
Type
Position
A
USA
P
I
SZ
B
India
R
E
DS
C
USA
H
I
FG
D
France
P
S
RTG
E
USA
R
I
ZA
F
India
H
E
SFG
G
USA
Z
I
DFZ
H
France
Z
S
FSZ

And I would like to put it as follows with R

Column1  Column2   Column3  Column4  Column5
A        USA       P        I        SZ
B        India     R        E        DS
C        USA       H        I        FG
D        France    P        S        RTG
E        USA       R        I        ZA
F        India     H        E        SFG
G        USA       Z        I        DFZ
H        France    Z        S        FSZ

I know how to do it in Excel, using the following formula:

=INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))

I have no idea how to do it in R.

Thanks

Doctor G · Accepted Answer · 2020-06-27 08:31:34Z

1

Here is an attempt using tidyverse and assuming your string looks a like:

"Name\nCountry\nRole\nType\nPosition\nA\nUSA\nP\nI\nSZ\nB\nIndia\nR\nE\nDS\nC\nUSA\nH\nI\nFG\nD\nFrance\nP\nS\nRTG\nE\nUSA\nR\nI\nZA\nF\nIndia\nH\nE\nSFG\nG\nUSA\nZ\nI\nDFZ\nH\nFrance\nZ\nS\nFSZ"

in the R Console.

library(tidyverse)
mystring <- "Name\nCountry\nRole\nType\nPosition\nA\nUSA\nP\nI\nSZ\nB\nIndia\nR\nE\nDS\nC\nUSA\nH\nI\nFG\nD\nFrance\nP\nS\nRTG\nE\nUSA\nR\nI\nZA\nF\nIndia\nH\nE\nSFG\nG\nUSA\nZ\nI\nDFZ\nH\nFrance\nZ\nS\nFSZ" 
dd <- mystring %>% 
  str_split("\\n") %>%
  first() %>% 
  matrix(ncol = 5, byrow = TRUE)

It remains to extract the first line and use colnames(dd) <- to assign them to your columnames, see ?colnames.

edited Jun 27, 2020 at 8:31

answered Jun 26, 2020 at 17:09

Doctor G

1731 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sofiane Over a year ago

This one can work if the elements don't have spaces inside each individual cell.How about if in Column 1 has corporations names, and Column 5 has remarks or comments (phrases) ?

Doctor G Over a year ago

It should also work with spaces in cells as long as the cells are separated by the newline character \n. @user4715 can you provide a working example with what you call remarks or comments?

slava-kohut · Accepted Answer · 2020-06-27 00:33:18Z

0

Base R:

# data    
s <- "Name\nCountry\nRole\nType\nPosition\nA\nUSA space\nP space\nI\nSZ\nB\nIndia\nR\nE\nDS\nC\nUSA\nH\nI\nFG\nD\nFrance\nP\nS\nRTG\nE\nUSA\nR\nI\nZA\nF\nIndia\nH\nE\nSFG\nG\nUSA\nZ\nI\nDFZ\nH\nFrance\nZ\nS\nFSZ"

n_col <- 5
df <- read.table(text = s, sep="\n")
n_row <- nrow(df)/n_col

df <- as.data.frame(matrix(df$V1, n_row, n_col, byrow=TRUE))[-1,]

Output

V1     V2 V3 V4  V5
1  A    USA  P  I  SZ
2  B  India  R  E  DS
3  C    USA  H  I  FG
4  D France  P  S RTG
5  E    USA  R  I  ZA
6  F  India  H  E SFG
7  G    USA  Z  I DFZ
8  H France  Z  S FSZ

edited Jun 27, 2020 at 0:33

answered Jun 26, 2020 at 17:40

slava-kohut

4,2331 gold badge10 silver badges24 bronze badges

1 Comment

Sofiane Over a year ago

This one can work if the elements don't have spaces inside each individual cell.How about if in Column 1 has corporations names, and Column 5 has remarks or comments (phrases) ?

Collectives™ on Stack Overflow

Transform a list into a dataframe with multiple columns in R

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related