create colnames from first two rows of dataframe

Question

I have a dataframe where the rows 1 and 2 are actually the colnames.

                   V1    V2      V3    V4                            V5                V6         V7       V8
1                                                          Bristol 014C  Bristol, City of South West  England
2                                         Super Output Area Lower Layer Unitary Authority     Region  Country
3 All Usual Residents Count Persons Mar11                          1654            428234    5288935 53012456
4         Age Under 1 Count Persons Mar11                            24              6459      59969   675065
5               Age 1 Count Persons Mar11                            17              6150      59165   668662
6               Age 2 Count Persons Mar11                            23              5927      59051   663119
...

How can I make the colnames out of rows 1 and 2?

                                                         Bristol 014C\n Bristol, City of\n South West\n  England\n
X X.1                 X.2   X.3     X.4   Super Output Area Lower Layer  Unitary Authority       Region    Country
3 All Usual Residents Count Persons Mar11                          1654             428234      5288935   53012456
...

The raw CSV dataset is here:

"Age by Single Year (QS103EW)"
,,,,"Bristol, City of","South West","England"
,,,,"Unitary Authority","Region","Country"
"All Usual Residents","Count","Persons","Mar11",428234,5288935,53012456
"Age Under 1","Count","Persons","Mar11",6459,59969,675065
"Age 1","Count","Persons","Mar11",6150,59165,668662
"Age 2","Count","Persons","Mar11",5927,59051,663119
"Age 3","Count","Persons","Mar11",5698,59230,663574
"Age 4","Count","Persons","Mar11",5399,58679,648029
"Age 5","Count","Persons","Mar11",5005,56756,632090
"Age 6","Count","Persons","Mar11",4784,54333,600847
"Age 7","Count","Persons","Mar11",4582,55049,594673
"Age 8","Count","Persons","Mar11",4240,53364,572874
"Age 9","Count","Persons","Mar11",4226,53187,572148
"Age 10","Count","Persons","Mar11",4174,55365,587056
"Age 11","Count","Persons","Mar11",4326,57859,605569
"Age 12","Count","Persons","Mar11",4362,59970,618918
"Age 13","Count","Persons","Mar11",4474,61139,628858
"Age 14","Count","Persons","Mar11",4367,62559,640528
"Age 15","Count","Persons","Mar11",4408,64003,650826
"Age 16","Count","Persons","Mar11",4486,63827,648677
"Age 17","Count","Persons","Mar11",4436,65108,665447
"Age 18","Count","Persons","Mar11",5650,66351,674980
"Age 19","Count","Persons","Mar11",8061,68788,700335
"Age 20","Count","Persons","Mar11",9611,70266,727389
"Age 21","Count","Persons","Mar11",9412,68414,711470
"Age 22","Count","Persons","Mar11",8860,66641,715130
"Age 23","Count","Persons","Mar11",8364,65065,728435
"Age 24","Count","Persons","Mar11",8124,62780,712897
"Age 25","Count","Persons","Mar11",8066,61533,731640
"Age 26","Count","Persons","Mar11",8514,61882,730870
"Age 27","Count","Persons","Mar11",8051,60924,725203
"Age 28","Count","Persons","Mar11",7947,60960,728376
"Age 29","Count","Persons","Mar11",8174,61860,734792
"Age 30","Count","Persons","Mar11",8019,62908,747536
"Age 31","Count","Persons","Mar11",7956,62286,738069
"Age 32","Count","Persons","Mar11",7166,59382,700814
"Age 33","Count","Persons","Mar11",6726,55917,660464
"Age 34","Count","Persons","Mar11",6551,56298,662338
"Age 35","Count","Persons","Mar11",6277,58203,668403
"Age 36","Count","Persons","Mar11",6152,61097,685626
"Age 37","Count","Persons","Mar11",5939,63688,699168
"Age 38","Count","Persons","Mar11",5987,67826,731914
"Age 39","Count","Persons","Mar11",6080,71550,764005
"Age 40","Count","Persons","Mar11",5892,73098,775472
"Age 41","Count","Persons","Mar11",5638,73194,761698
"Age 42","Count","Persons","Mar11",5584,75361,780374
"Age 43","Count","Persons","Mar11",5428,75596,777994
"Age 44","Count","Persons","Mar11",5765,78289,790396
"Age 45","Count","Persons","Mar11",5651,79060,790748
"Age 46","Count","Persons","Mar11",5650,79232,795338
"Age 47","Count","Persons","Mar11",5379,78975,781209
"Age 48","Count","Persons","Mar11",5337,77057,767090
"Age 49","Count","Persons","Mar11",5059,75259,745430
"Age 50","Count","Persons","Mar11",4931,73367,723908
"Age 51","Count","Persons","Mar11",4685,70689,690689
"Age 52","Count","Persons","Mar11",4693,70449,680476
"Age 53","Count","Persons","Mar11",4687,69254,666006
"Age 54","Count","Persons","Mar11",4323,66674,639016
"Age 55","Count","Persons","Mar11",4257,64482,614577
"Age 56","Count","Persons","Mar11",4165,64033,602320
"Age 57","Count","Persons","Mar11",4146,65515,605276
"Age 58","Count","Persons","Mar11",3984,64223,591365
"Age 59","Count","Persons","Mar11",3840,64945,583454
"Age 60","Count","Persons","Mar11",3847,64877,586619
"Age 61","Count","Persons","Mar11",3932,68392,605525
"Age 62","Count","Persons","Mar11",4004,70719,620903
"Age 63","Count","Persons","Mar11",4280,77954,676509
"Age 64","Count","Persons","Mar11",4015,79565,682721
"Age 65","Count","Persons","Mar11",3113,61314,523808
"Age 66","Count","Persons","Mar11",3262,65965,553369
"Age 67","Count","Persons","Mar11",3175,61203,516594
"Age 68","Count","Persons","Mar11",3083,58555,488921
"Age 69","Count","Persons","Mar11",2589,50403,425462
"Age 70","Count","Persons","Mar11",2539,47363,409195
"Age 71","Count","Persons","Mar11",2668,49314,426526
"Age 72","Count","Persons","Mar11",2591,48034,417526
"Age 73","Count","Persons","Mar11",2383,46204,403761
"Age 74","Count","Persons","Mar11",2322,44547,387121
"Age 75","Count","Persons","Mar11",2234,42240,367663
"Age 76","Count","Persons","Mar11",2233,40936,350111
"Age 77","Count","Persons","Mar11",2072,38185,326669
"Age 78","Count","Persons","Mar11",2060,37834,318178
"Age 79","Count","Persons","Mar11",2098,36471,306724
"Age 80","Count","Persons","Mar11",1925,35511,297352
"Age 81","Count","Persons","Mar11",1889,33113,273007
"Age 82","Count","Persons","Mar11",1730,30617,250274
"Age 83","Count","Persons","Mar11",1464,27746,226334
"Age 84","Count","Persons","Mar11",1530,26106,211806
"Age 85","Count","Persons","Mar11",1386,24105,191681
"Age 86","Count","Persons","Mar11",1257,21970,171121
"Age 87","Count","Persons","Mar11",1173,19873,153717
"Age 88","Count","Persons","Mar11",997,17646,136061
"Age 89","Count","Persons","Mar11",985,16306,123731
"Age 90","Count","Persons","Mar11",830,14560,110027
"Age 91","Count","Persons","Mar11",618,10825,82336
"Age 92","Count","Persons","Mar11",390,6599,49584
"Age 93","Count","Persons","Mar11",325,4989,37630
"Age 94","Count","Persons","Mar11",255,4575,34145
"Age 95","Count","Persons","Mar11",219,3549,26370
"Age 96","Count","Persons","Mar11",151,2863,21040
"Age 97","Count","Persons","Mar11",114,2049,15044
"Age 98","Count","Persons","Mar11",78,1429,10327
"Age 99","Count","Persons","Mar11",57,953,6738
"Age 100 and Over","Count","Persons","Mar11",77,1492,10576
"Age by Single Year, 2011 (QS103EW), Mar11","LastUpdated","30 January 2013"
"Age by Single Year, 2011 (QS103EW), Mar11","Source","Office for National Statistics"
"Age by Single Year (QS103EW)","National Statistics"
"This material is Crown Copyright. You may re-use this information (not including logos) free of charge in any format or medium, under the terms of the Open Government Licence. To view this licence, visit www.nationalarchives.gov.uk/doc/open-government-licence Information Policy Team, The National Archives, Kew, London TW9 4DU, or email:psi@nationalarchives.gsi.gov.uk. When reproducing this material, the source should be acknowledged."

Note that I read the data with:

data <- head(read.csv2( file = 'Age by Single Year, 2011 (QS103EW) (2011).csv', skip = 1, header = FALSE, sep = ',' ), -4)

Could you add the result from `dput(head(data))` to the post so it's easier for us to read the data into R — Rich Scriven, Dec 02 '15 at 02:48
I've added the csv data and the command I used to read it in. — Chris Snow, Dec 02 '15 at 07:48

score 1 · Answer 1 · answered Dec 02 '15 at 03:15

Assuming your data is tab delimited in data.txt, read the column names into a data frame first by using the nrows argument.

colnames <- read.table('./data.txt', nrows = 2)

Then read your data into a data frame, skipping the first 2 lines of your text file with the skip argument.

df <- read.table('./data.txt', skip = 2)

You may have to inspect/modify colnames to ensure that the entries are in the correct position and that its length is correct. Then use names to set the names of the objects in your data frame.

names(df) <- colnames

score 0 · Answer 2 · edited Dec 02 '15 at 02:45

0

A smaller example may have made the question clearer.

Say your data.frame is DF. Then

newDF <- DF[-c(1:2), ]
dimnames(newDF)[[2]] <- paste(DF[1,], DF[2, ])

edited Dec 02 '15 at 02:45

alexwhan

15,636
5
52
66

answered Dec 02 '15 at 02:40

kangaroo_cliff

6,067
3
29
42

you can access row names through `rownames()` instead of `dimnames()[[2]]` – alexwhan Dec 02 '15 at 02:47
I think you meant `colnames()` :) – kangaroo_cliff Dec 02 '15 at 22:56

create colnames from first two rows of dataframe

2 Answers2