novicegeek novicegeek - 1 month ago 10
R Question

Merging two columns in R considering other corresponding columns?

I am using R and have imported data from 2 excel sheets containing 3 columns each. The first matrix contains 3 columns (1-3) and 380 rows and the second matrix contains 3 columns and 365 rows. Columns 2 and 3 are always values corresponding to the first column. I would like to merge the first columns of the two matrices in a single column such that after merging the identical values in the two columns are just replaced (they should not be in individual rows one after the other) and the column is arranged in an ascending order. Also, the main condition should be that columns 2,3 of each matrix (that are values for column 1) should get rearranged correspondingly, but should not get merged. If there are some values in the first column (generated after merging) whose value is not present in the corresponding column then it should be replaced by zero. I have done merging and rearranging for the first column, but I am not able to make the corresponding changes in the other columns. How should I go around?

Here are the two matrices:

Matrix A

92.6691 1076.5 0.48
93.324 1110.1 0.5
96.9597 1123.3 0.5
97.7539 968.4 0.43
98.992 1006.1 0.45
99.0061 5584.6 2.49
101.0243 1555.7 0.69
101.0606 12821.2 5.72
102.1221 972 0.43


Matrix B

95.4466 974.2 0.43
99.0062 4721.9 2.06
100.0321 1040.1 0.45
101.0241 2115.8 0.92
101.0606 15202.8 6.64
102.2736 945.3 0.41
108.4273 1059.7 0.46
115.0397 25106.3 10.96
115.0761 54740 23.9


After merging, the results should be a single matrix:

Column 1 - Merged 1st columns of matrices A and B (ascending order)
Column 2 - Rearranged based on change in row positions of column 1 in matrix A
Column 3 - Rearranged based on change in row positions of column 1 in matrix A
Column 4 - Rearranged based on change in row positions of column 1 in matrix B
Column 5 - Rearranged based on change in row positions of column 1 in matrix B


Here is the resulting matrix:

92.6691 1076.5 0.48 0 0
93.324 1110.1 0.5 0 0
95.4466 0 0 974.2 0.43
96.9597 1123.3 0.5 0 0
97.7539 968.4 0.43 0 0
98.992 1006.1 0.45 0 0
99.0061 5584.6 2.49 0 0
99.0062 0 0 4721.9 2.06
100.0321 0 0 1040.1 0.45
101.0241 0 0 2115.8 0.92
101.0243 1555.7 0.69 0 0
101.0606 12821.2 5.72 15202.8 6.64
102.1221 972 0.43 0 0
102.2736 0 0 945.3 0.41
108.4273 0 0 1059.7 0.46
115.0397 0 0 25106.3 10.96
115.0761 0 0 54740 23.9


Note that in matrices A and B, the value 101.0606 is common.

Answer

This can be done easily with merge().

# read your data:
read.table(
         t="92.6691     1076.5      0.48
            93.324      1110.1      0.5
            96.9597     1123.3      0.5
            97.7539     968.4       0.43
            98.992      1006.1      0.45
            99.0061     5584.6      2.49
            101.0243    1555.7      0.69
            101.0606    12821.2     5.72
            102.1221    972         0.43") -> M1
read.table(
         t="95.4466     974.2       0.43
            99.0062     4721.9      2.06
            100.0321    1040.1      0.45
            101.0241    2115.8      0.92
            101.0606    15202.8     6.64
            102.2736    945.3       0.41
            108.4273    1059.7      0.46
            115.0397    25106.3     10.96
            115.0761    54740       23.90") -> M2

# merge data -- note `all = TRUE` 
result <- merge(M1,M2,by = "V1", all = TRUE)

# replace na with 0
result[is.na(result)] <- 0

result
#        V1    V2.x V3.x    V2.y  V3.y
# 1   92.67  1076.5 0.48     0.0  0.00
# 2   93.32  1110.1 0.50     0.0  0.00
# 3   95.45     0.0 0.00   974.2  0.43
# 4   96.96  1123.3 0.50     0.0  0.00
# 5   97.75   968.4 0.43     0.0  0.00
# 6   98.99  1006.1 0.45     0.0  0.00
# 7   99.01  5584.6 2.49     0.0  0.00
# 8   99.01     0.0 0.00  4721.9  2.06
# 9  100.03     0.0 0.00  1040.1  0.45
# 10 101.02     0.0 0.00  2115.8  0.92
# 11 101.02  1555.7 0.69     0.0  0.00
# 12 101.06 12821.2 5.72 15202.8  6.64
# 13 102.12   972.0 0.43     0.0  0.00
# 14 102.27     0.0 0.00   945.3  0.41
# 15 108.43     0.0 0.00  1059.7  0.46
# 16 115.04     0.0 0.00 25106.3 10.96
# 17 115.08     0.0 0.00 54740.0 23.90
Comments