Convert single column to multiple columns

Question

I'm a newbie. I have a dataset with one column and many rows. I want to convert this column to 5 columns. For example, my dataset looks like this:

Column
----
City
Nation
Area
Metro Area
Urban Area
Shanghai
China
24,000,000
1230040
4244234
New york 
America 
343423  
23423434    
343434
Etc

The result should look like this:

City | Nation | Area | Metro City | Urban Area
----- -------  ------ ------------ -----------
Shangai China  2400000  1230040     4244234
New york America 343423  23423434    343434

The first 5 rows of the data set (City, Nation, Area, etc.) should be names of 5 columns, and I want the rest of the data to be populated under these 5 columns. Please, help.

Answer 1

I am going to go on a limb and guess that the data you are using is listed in the URL: https://en.wikipedia.org/wiki/List_of_largest_cities .

, ( , R ), , , .

:

library(rvest)

URL <- "https://en.wikipedia.org/wiki/List_of_largest_cities"
XPATH <- '//*[@id="mw-content-text"]/table[2]'

cities <- URL %>% 
  read_html() %>% 
  html_nodes(xpath=XPATH) %>% 
  html_table(fill = TRUE)

. ( , "rowspan" ):

head(cities[[1]])
##       City     Nation Image     Population        Population                            Population
## 1                     Image    City proper Metropolitan area                         Urban area[7]
## 2 Shanghai      China        24,256,800[8]     34,750,000[9]                         23,416,000[a]
## 3  Karachi   Pakistan       23,500,000[10]    25,400,000[11]                            25,400,000
## 4  Beijing      China       21,516,000[12]    24,900,000[13]                            21,009,000
## 5    Dhaka Bangladesh       16,970,105[14]        15,669,000 18,305,671[15][not in citation given]
## 6    Delhi      India       16,787,941[16]        24,998,000                        21,753,486[17]

:

cities <- cities[[1]][-1, ]
names(cities) <- c("City", "Nation", "Image", "Pop_City", "Pop_Metro", "Pop_Urban")
cities["Image"] <- NULL
head(cities)
cities[] <- lapply(cities, function(x) type.convert(gsub("\\[.*|,", "", x)))
head(cities)
#       City     Nation Pop_City Pop_Metro Pop_Urban
# 2 Shanghai      China 24256800  34750000  23416000
# 3  Karachi   Pakistan 23500000  25400000  25400000
# 4  Beijing      China 21516000  24900000  21009000
# 5    Dhaka Bangladesh 16970105  15669000  18305671
# 6    Delhi      India 16787941  24998000  21753486
# 7    Lagos    Nigeria 16060303  13123000  21000000
str(cities)
# 'data.frame': 163 obs. of  5 variables:
#  $ City     : Factor w/ 162 levels "Abidjan","Addis Ababa",..: 133 74 12 41 40 84 66 148 53 102 ...
#  $ Nation   : Factor w/ 59 levels "Afghanistan",..: 13 41 13 7 25 40 54 31 13 25 ...
#  $ Pop_City : num  24256800 23500000 21516000 16970105 16787941 ...
#  $ Pop_Metro: int  34750000 25400000 24900000 15669000 24998000 13123000 13520000 37843000 44259000 17712000 ...
#  $ Pop_Urban: num  23416000 25400000 21009000 18305671 21753486 ...

Answer 2

(, column , .. df$column <- as.character(df$column))

setNames(data.frame(matrix(unlist(df[-c(1:5),]), ncol = 5, byrow = TRUE)), c(unlist(df[1:5,])))

#      City  Nation       Area Metro_Area Urban_Area
#1 Shanghai   China 24,000,000    1230040    4244234
#2 New_york America     343423   23423434     343434