Merge two tables based on value in first column
1
I have 1 table like this (csv file):
p1 p10 p16 p19 p25 p3 p5 p6 p8 p9
con1 567 0 3 0 18 17 8 4 6 7
con3 490 7 6 2 23 26 20 14 12 29
con4 737 1 4 1 6 4 1 4 8 5
con5 145 6 4 0 11 17 5 9 22 11
con10 68 0 0 34 4 0 0 0 0 0
con30 46 0 0 8 0 0 0 0 0 0
con2 72 0 0 8 0 1 0 0 0 0
And the second table (csv file):
name superkingdom phylum class order family genus species
con1 Viruses Pox Alphaen Ano
con30 Viruses Her Allo Bat Ran
con4 Viruses Hud
con5 Viruses Mimi Cafe Caf
con10 Viruses Hud
con2 Viruses Pico Picorn Entero En
con3 Viruses Phyco Chloro
I want to copy to the first table columns (2:8) from second table, everything based on the same value in first column.
Example output
p1 p10 p16 p19 p25 p3 p5 p6 p8 p9 superkingdom phylum class order family genus species
con1 567 0 3 0 18 17 8 4 6 7 Viruses Pox Alphaen Ano
con3 490 7 6 2 23 26 20 14 12 29 Viruses Phyco Chloro
con4 737 1 4 1 6 4 1 4 8 5 Viruses Hud
con5 145 6 4 0 11 17 5 9 22 11 Viruses Mimi Cafe Caf
con10 68 0 0 34 4 0 0 0 0 0 Viruses Hud
con30 46 0 0 8 0 0 0 0 0 0 Viruses Her Allo Bat Ran
con2 72 0 0 8 0 1 0 0 0 0 Viruses Pico Picorn Entero En
awk csv r
edited Dec 16 at 21:42
Rui F Ribeiro
38.9k1479129
asked Jun 28 at 13:49
k_a_r_o_l
122
add a comment |
1
I have 1 table like this (csv file):
p1 p10 p16 p19 p25 p3 p5 p6 p8 p9
con1 567 0 3 0 18 17 8 4 6 7
con3 490 7 6 2 23 26 20 14 12 29
con4 737 1 4 1 6 4 1 4 8 5
con5 145 6 4 0 11 17 5 9 22 11
con10 68 0 0 34 4 0 0 0 0 0
con30 46 0 0 8 0 0 0 0 0 0
con2 72 0 0 8 0 1 0 0 0 0
And the second table (csv file):
name superkingdom phylum class order family genus species
con1 Viruses Pox Alphaen Ano
con30 Viruses Her Allo Bat Ran
con4 Viruses Hud
con5 Viruses Mimi Cafe Caf
con10 Viruses Hud
con2 Viruses Pico Picorn Entero En
con3 Viruses Phyco Chloro
I want to copy to the first table columns (2:8) from second table, everything based on the same value in first column.
Example output
p1 p10 p16 p19 p25 p3 p5 p6 p8 p9 superkingdom phylum class order family genus species
con1 567 0 3 0 18 17 8 4 6 7 Viruses Pox Alphaen Ano
con3 490 7 6 2 23 26 20 14 12 29 Viruses Phyco Chloro
con4 737 1 4 1 6 4 1 4 8 5 Viruses Hud
con5 145 6 4 0 11 17 5 9 22 11 Viruses Mimi Cafe Caf
con10 68 0 0 34 4 0 0 0 0 0 Viruses Hud
con30 46 0 0 8 0 0 0 0 0 0 Viruses Her Allo Bat Ran
con2 72 0 0 8 0 1 0 0 0 0 Viruses Pico Picorn Entero En
awk csv r
edited Dec 16 at 21:42
Rui F Ribeiro
38.9k1479129
asked Jun 28 at 13:49
k_a_r_o_l
122
2
Just in case you are impatient, look at thejoincommand before someone else is so kind to provide a good answer with an example
– Tagwint
Jun 28 at 14:09
2
The 2nd file is most certainly not a CSV file. You have no programmable way to indicate empty fields, other than using fixed width method.
– glenn jackman
Jun 28 at 14:09
add a comment |
1
1
1
I have 1 table like this (csv file):
p1 p10 p16 p19 p25 p3 p5 p6 p8 p9
con1 567 0 3 0 18 17 8 4 6 7
con3 490 7 6 2 23 26 20 14 12 29
con4 737 1 4 1 6 4 1 4 8 5
con5 145 6 4 0 11 17 5 9 22 11
con10 68 0 0 34 4 0 0 0 0 0
con30 46 0 0 8 0 0 0 0 0 0
con2 72 0 0 8 0 1 0 0 0 0
And the second table (csv file):
name superkingdom phylum class order family genus species
con1 Viruses Pox Alphaen Ano
con30 Viruses Her Allo Bat Ran
con4 Viruses Hud
con5 Viruses Mimi Cafe Caf
con10 Viruses Hud
con2 Viruses Pico Picorn Entero En
con3 Viruses Phyco Chloro
I want to copy to the first table columns (2:8) from second table, everything based on the same value in first column.
Example output
p1 p10 p16 p19 p25 p3 p5 p6 p8 p9 superkingdom phylum class order family genus species
con1 567 0 3 0 18 17 8 4 6 7 Viruses Pox Alphaen Ano
con3 490 7 6 2 23 26 20 14 12 29 Viruses Phyco Chloro
con4 737 1 4 1 6 4 1 4 8 5 Viruses Hud
con5 145 6 4 0 11 17 5 9 22 11 Viruses Mimi Cafe Caf
con10 68 0 0 34 4 0 0 0 0 0 Viruses Hud
con30 46 0 0 8 0 0 0 0 0 0 Viruses Her Allo Bat Ran
con2 72 0 0 8 0 1 0 0 0 0 Viruses Pico Picorn Entero En
awk csv r
edited Dec 16 at 21:42
Rui F Ribeiro
38.9k1479129
asked Jun 28 at 13:49
k_a_r_o_l
122
I have 1 table like this (csv file):
p1 p10 p16 p19 p25 p3 p5 p6 p8 p9
con1 567 0 3 0 18 17 8 4 6 7
con3 490 7 6 2 23 26 20 14 12 29
con4 737 1 4 1 6 4 1 4 8 5
con5 145 6 4 0 11 17 5 9 22 11
con10 68 0 0 34 4 0 0 0 0 0
con30 46 0 0 8 0 0 0 0 0 0
con2 72 0 0 8 0 1 0 0 0 0
And the second table (csv file):
name superkingdom phylum class order family genus species
con1 Viruses Pox Alphaen Ano
con30 Viruses Her Allo Bat Ran
con4 Viruses Hud
con5 Viruses Mimi Cafe Caf
con10 Viruses Hud
con2 Viruses Pico Picorn Entero En
con3 Viruses Phyco Chloro
I want to copy to the first table columns (2:8) from second table, everything based on the same value in first column.
Example output
p1 p10 p16 p19 p25 p3 p5 p6 p8 p9 superkingdom phylum class order family genus species
con1 567 0 3 0 18 17 8 4 6 7 Viruses Pox Alphaen Ano
con3 490 7 6 2 23 26 20 14 12 29 Viruses Phyco Chloro
con4 737 1 4 1 6 4 1 4 8 5 Viruses Hud
con5 145 6 4 0 11 17 5 9 22 11 Viruses Mimi Cafe Caf
con10 68 0 0 34 4 0 0 0 0 0 Viruses Hud
con30 46 0 0 8 0 0 0 0 0 0 Viruses Her Allo Bat Ran
con2 72 0 0 8 0 1 0 0 0 0 Viruses Pico Picorn Entero En
awk csv r
awk csv r
edited Dec 16 at 21:42
Rui F Ribeiro
38.9k1479129
asked Jun 28 at 13:49
k_a_r_o_l
122
edited Dec 16 at 21:42
Rui F Ribeiro
38.9k1479129
asked Jun 28 at 13:49
k_a_r_o_l
122
edited Dec 16 at 21:42
Rui F Ribeiro
38.9k1479129
edited Dec 16 at 21:42
Rui F Ribeiro
38.9k1479129
edited Dec 16 at 21:42
Rui F Ribeiro
38.9k1479129
38.9k1479129
asked Jun 28 at 13:49
k_a_r_o_l
122
asked Jun 28 at 13:49
k_a_r_o_l
122
asked Jun 28 at 13:49
k_a_r_o_l
122
122
2
Just in case you are impatient, look at thejoincommand before someone else is so kind to provide a good answer with an example
– Tagwint
Jun 28 at 14:09
2
The 2nd file is most certainly not a CSV file. You have no programmable way to indicate empty fields, other than using fixed width method.
– glenn jackman
Jun 28 at 14:09
add a comment |
2
Just in case you are impatient, look at thejoincommand before someone else is so kind to provide a good answer with an example
– Tagwint
Jun 28 at 14:09
2
The 2nd file is most certainly not a CSV file. You have no programmable way to indicate empty fields, other than using fixed width method.
– glenn jackman
Jun 28 at 14:09
2
2
Just in case you are impatient, look at the
join command before someone else is so kind to provide a good answer with an example– Tagwint
Jun 28 at 14:09
Just in case you are impatient, look at the
join command before someone else is so kind to provide a good answer with an example– Tagwint
Jun 28 at 14:09
2
2
The 2nd file is most certainly not a CSV file. You have no programmable way to indicate empty fields, other than using fixed width method.
– glenn jackman
Jun 28 at 14:09
The 2nd file is most certainly not a CSV file. You have no programmable way to indicate empty fields, other than using fixed width method.
– glenn jackman
Jun 28 at 14:09
add a comment |
1 Answer
1
active
oldest
votes
0
In base R, using merge (package base):
df1 <- read.csv(text="p1,p10,p16,p19,p25,p3,p5,p6,p8,p9
con1,567,0,3,0,18,17,8,4,6,7
con3,490,7,6,2,23,26,20,14,12,29
con4,737,1,4,1,6,4,1,4,8,5
con5,145,6,4,0,11,17,5,9,22,11
con10,68,0,0,34,4,0,0,0,0,0
con30,46,0,0,8,0,0,0,0,0,0
con2,72,0,0,8,0,1,0,0,0,0")
df2 <- read.csv(text="name,superkingdom,phylum,class,order,family,genus,species
con1,Viruses,,,,Pox,Alphaen,Ano
con30,Viruses,,,Her,Allo,Bat,Ran
con4,Viruses,,,,,,Hud
con5,Viruses,,,,Mimi,Cafe,Caf
con10,Viruses,,,,,,Hud
con2,Viruses,,,Pico,Picorn,Entero,En
con3,Viruses,,,,,Phyco,Chloro")
# by.x=0 joins df1 by rownames
merge(df1, df2, by.x=0, by.y="name")
# Row.names p1 p10 p16 p19 p25 p3 p5 p6 p8 p9 superkingdom phylum class order family genus species
# 1 con1 567 0 3 0 18 17 8 4 6 7 Viruses NA NA Pox Alphaen Ano
# 2 con10 68 0 0 34 4 0 0 0 0 0 Viruses NA NA Hud
# 3 con2 72 0 0 8 0 1 0 0 0 0 Viruses NA NA Pico Picorn Entero En
# 4 con3 490 7 6 2 23 26 20 14 12 29 Viruses NA NA Phyco Chloro
# 5 con30 46 0 0 8 0 0 0 0 0 0 Viruses NA NA Her Allo Bat Ran
# 6 con4 737 1 4 1 6 4 1 4 8 5 Viruses NA NA Hud
# 7 con5 145 6 4 0 11 17 5 9 22 11 Viruses NA NA Mimi Cafe Caf
answered Jun 29 at 8:21
rcs
482139
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "106"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f452441%2fmerge-two-tables-based-on-value-in-first-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
In base R, using merge (package base):
df1 <- read.csv(text="p1,p10,p16,p19,p25,p3,p5,p6,p8,p9
con1,567,0,3,0,18,17,8,4,6,7
con3,490,7,6,2,23,26,20,14,12,29
con4,737,1,4,1,6,4,1,4,8,5
con5,145,6,4,0,11,17,5,9,22,11
con10,68,0,0,34,4,0,0,0,0,0
con30,46,0,0,8,0,0,0,0,0,0
con2,72,0,0,8,0,1,0,0,0,0")
df2 <- read.csv(text="name,superkingdom,phylum,class,order,family,genus,species
con1,Viruses,,,,Pox,Alphaen,Ano
con30,Viruses,,,Her,Allo,Bat,Ran
con4,Viruses,,,,,,Hud
con5,Viruses,,,,Mimi,Cafe,Caf
con10,Viruses,,,,,,Hud
con2,Viruses,,,Pico,Picorn,Entero,En
con3,Viruses,,,,,Phyco,Chloro")
# by.x=0 joins df1 by rownames
merge(df1, df2, by.x=0, by.y="name")
# Row.names p1 p10 p16 p19 p25 p3 p5 p6 p8 p9 superkingdom phylum class order family genus species
# 1 con1 567 0 3 0 18 17 8 4 6 7 Viruses NA NA Pox Alphaen Ano
# 2 con10 68 0 0 34 4 0 0 0 0 0 Viruses NA NA Hud
# 3 con2 72 0 0 8 0 1 0 0 0 0 Viruses NA NA Pico Picorn Entero En
# 4 con3 490 7 6 2 23 26 20 14 12 29 Viruses NA NA Phyco Chloro
# 5 con30 46 0 0 8 0 0 0 0 0 0 Viruses NA NA Her Allo Bat Ran
# 6 con4 737 1 4 1 6 4 1 4 8 5 Viruses NA NA Hud
# 7 con5 145 6 4 0 11 17 5 9 22 11 Viruses NA NA Mimi Cafe Caf
answered Jun 29 at 8:21
rcs
482139
add a comment |
0
In base R, using merge (package base):
df1 <- read.csv(text="p1,p10,p16,p19,p25,p3,p5,p6,p8,p9
con1,567,0,3,0,18,17,8,4,6,7
con3,490,7,6,2,23,26,20,14,12,29
con4,737,1,4,1,6,4,1,4,8,5
con5,145,6,4,0,11,17,5,9,22,11
con10,68,0,0,34,4,0,0,0,0,0
con30,46,0,0,8,0,0,0,0,0,0
con2,72,0,0,8,0,1,0,0,0,0")
df2 <- read.csv(text="name,superkingdom,phylum,class,order,family,genus,species
con1,Viruses,,,,Pox,Alphaen,Ano
con30,Viruses,,,Her,Allo,Bat,Ran
con4,Viruses,,,,,,Hud
con5,Viruses,,,,Mimi,Cafe,Caf
con10,Viruses,,,,,,Hud
con2,Viruses,,,Pico,Picorn,Entero,En
con3,Viruses,,,,,Phyco,Chloro")
# by.x=0 joins df1 by rownames
merge(df1, df2, by.x=0, by.y="name")
# Row.names p1 p10 p16 p19 p25 p3 p5 p6 p8 p9 superkingdom phylum class order family genus species
# 1 con1 567 0 3 0 18 17 8 4 6 7 Viruses NA NA Pox Alphaen Ano
# 2 con10 68 0 0 34 4 0 0 0 0 0 Viruses NA NA Hud
# 3 con2 72 0 0 8 0 1 0 0 0 0 Viruses NA NA Pico Picorn Entero En
# 4 con3 490 7 6 2 23 26 20 14 12 29 Viruses NA NA Phyco Chloro
# 5 con30 46 0 0 8 0 0 0 0 0 0 Viruses NA NA Her Allo Bat Ran
# 6 con4 737 1 4 1 6 4 1 4 8 5 Viruses NA NA Hud
# 7 con5 145 6 4 0 11 17 5 9 22 11 Viruses NA NA Mimi Cafe Caf
answered Jun 29 at 8:21
rcs
482139
add a comment |
0
0
0
In base R, using merge (package base):
df1 <- read.csv(text="p1,p10,p16,p19,p25,p3,p5,p6,p8,p9
con1,567,0,3,0,18,17,8,4,6,7
con3,490,7,6,2,23,26,20,14,12,29
con4,737,1,4,1,6,4,1,4,8,5
con5,145,6,4,0,11,17,5,9,22,11
con10,68,0,0,34,4,0,0,0,0,0
con30,46,0,0,8,0,0,0,0,0,0
con2,72,0,0,8,0,1,0,0,0,0")
df2 <- read.csv(text="name,superkingdom,phylum,class,order,family,genus,species
con1,Viruses,,,,Pox,Alphaen,Ano
con30,Viruses,,,Her,Allo,Bat,Ran
con4,Viruses,,,,,,Hud
con5,Viruses,,,,Mimi,Cafe,Caf
con10,Viruses,,,,,,Hud
con2,Viruses,,,Pico,Picorn,Entero,En
con3,Viruses,,,,,Phyco,Chloro")
# by.x=0 joins df1 by rownames
merge(df1, df2, by.x=0, by.y="name")
# Row.names p1 p10 p16 p19 p25 p3 p5 p6 p8 p9 superkingdom phylum class order family genus species
# 1 con1 567 0 3 0 18 17 8 4 6 7 Viruses NA NA Pox Alphaen Ano
# 2 con10 68 0 0 34 4 0 0 0 0 0 Viruses NA NA Hud
# 3 con2 72 0 0 8 0 1 0 0 0 0 Viruses NA NA Pico Picorn Entero En
# 4 con3 490 7 6 2 23 26 20 14 12 29 Viruses NA NA Phyco Chloro
# 5 con30 46 0 0 8 0 0 0 0 0 0 Viruses NA NA Her Allo Bat Ran
# 6 con4 737 1 4 1 6 4 1 4 8 5 Viruses NA NA Hud
# 7 con5 145 6 4 0 11 17 5 9 22 11 Viruses NA NA Mimi Cafe Caf
answered Jun 29 at 8:21
rcs
482139
In base R, using merge (package base):
df1 <- read.csv(text="p1,p10,p16,p19,p25,p3,p5,p6,p8,p9
con1,567,0,3,0,18,17,8,4,6,7
con3,490,7,6,2,23,26,20,14,12,29
con4,737,1,4,1,6,4,1,4,8,5
con5,145,6,4,0,11,17,5,9,22,11
con10,68,0,0,34,4,0,0,0,0,0
con30,46,0,0,8,0,0,0,0,0,0
con2,72,0,0,8,0,1,0,0,0,0")
df2 <- read.csv(text="name,superkingdom,phylum,class,order,family,genus,species
con1,Viruses,,,,Pox,Alphaen,Ano
con30,Viruses,,,Her,Allo,Bat,Ran
con4,Viruses,,,,,,Hud
con5,Viruses,,,,Mimi,Cafe,Caf
con10,Viruses,,,,,,Hud
con2,Viruses,,,Pico,Picorn,Entero,En
con3,Viruses,,,,,Phyco,Chloro")
# by.x=0 joins df1 by rownames
merge(df1, df2, by.x=0, by.y="name")
# Row.names p1 p10 p16 p19 p25 p3 p5 p6 p8 p9 superkingdom phylum class order family genus species
# 1 con1 567 0 3 0 18 17 8 4 6 7 Viruses NA NA Pox Alphaen Ano
# 2 con10 68 0 0 34 4 0 0 0 0 0 Viruses NA NA Hud
# 3 con2 72 0 0 8 0 1 0 0 0 0 Viruses NA NA Pico Picorn Entero En
# 4 con3 490 7 6 2 23 26 20 14 12 29 Viruses NA NA Phyco Chloro
# 5 con30 46 0 0 8 0 0 0 0 0 0 Viruses NA NA Her Allo Bat Ran
# 6 con4 737 1 4 1 6 4 1 4 8 5 Viruses NA NA Hud
# 7 con5 145 6 4 0 11 17 5 9 22 11 Viruses NA NA Mimi Cafe Caf
answered Jun 29 at 8:21
rcs
482139
answered Jun 29 at 8:21
rcs
482139
answered Jun 29 at 8:21
rcs
482139
answered Jun 29 at 8:21
rcs
482139
482139
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Unix & Linux Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f452441%2fmerge-two-tables-based-on-value-in-first-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Just in case you are impatient, look at the
joincommand before someone else is so kind to provide a good answer with an example– Tagwint
Jun 28 at 14:09
2
The 2nd file is most certainly not a CSV file. You have no programmable way to indicate empty fields, other than using fixed width method.
– glenn jackman
Jun 28 at 14:09