Kelsey Kelsey - 1 month ago 5
R Question

How to Drop X in Column names after Merge

I've merged two data frames by common row names, and the merge worked fine, but I am getting an x before each column name.

How can I remove the X from each column header?

z<- merge(p, y, by='ID')
head(z)

ID x y V1 X198101 X198102 X198103 X198104 X198105 X198106
1 410320 -122.5417 37.75 NA 119.45 33.15 104.23 5.61 4.85 0
2 410321 -122.5000 37.75 NA 129.49 37.76 114.94 5.28 5.24 0
3 410322 -122.4583 37.75 NA 163.68 42.80 131.22 7.25 6.94 0
4 410323 -122.4167 37.75 NA 141.14 32.26 110.45 7.77 4.62 0
5 410324 -122.3750 37.75 NA 130.87 25.87 102.15 8.38 4.13 0
6 410325 -122.3333 37.75 NA 129.03 25.21 102.37 9.42 4.35 0


Thanks!

Answer

It is better to have column names not start with numbers. By default, the make.names or make.unique adds the X prefix when it starts with numbers. To remove it, one option is sub

names(z) <- sub("^X", "", names(z))
z
#      ID         x     y V1 198101 198102 198103 198104 198105 198106
#1 410320 -122.5417 37.75 NA 119.45  33.15 104.23   5.61   4.85      0
#2 410321 -122.5000 37.75 NA 129.49  37.76 114.94   5.28   5.24      0
#3 410322 -122.4583 37.75 NA 163.68  42.80 131.22   7.25   6.94      0
#4 410323 -122.4167 37.75 NA 141.14  32.26 110.45   7.77   4.62      0
#5 410324 -122.3750 37.75 NA 130.87  25.87 102.15   8.38   4.13      0
#6 410325 -122.3333 37.75 NA 129.03  25.21 102.37   9.42   4.35      0

If we apply make.names

make.names(names(z))
#[1] "ID"      "x"       "y"       "V1"      "X198101" "X198102" 
#[7] "X198103" "X198104" "X198105" "X198106"

The 'X' prefix is returned. So, in general, it is safe to have column names with 'character' prefix instead of just numbers. Also, if we wanted to extract say '198101' column, we need a backtick

z$198104
#Error: unexpected numeric constant in "z$198104"
z$`198104`
#[1] 5.61 5.28 7.25 7.77 8.38 9.42
Comments