CSV joining based on keys
<p>This may be a simple/repeat question, but I could find/figure out yet how to do it. </p>

<p>I have two csv files:</p>

<p><strong>info.csv:</strong></p>

<pre><code>"Last Name", First Name, ID, phone, adress, age X [Total age: 100] |009076

abc, xyz, 1234, 982-128-0000, pqt,

bcd, uvw, 3124, 813-222-1111, tre, 

poi, ccc, 9087, 123-45607890, weq,
</code></pre>

<p>and then </p>

<p><strong>age.csv:</strong></p>

<pre><code>student_id,age_1

3124,20

9087,21

1234,45
</code></pre>

<p>I want to compare the two csv files, based on the columns "<code>id</code>" from <strong>info.csv</strong> and "<code>student_id</code>" from <strong>age.csv</strong> and take the corresponding "<code>age_1</code>" data and put it into the  "<code>age</code>" column in <strong>info.csv</strong>.</p>

<p>So the final output should be:</p>

<p><strong>info.csv:</strong></p>

<pre><code>"Last Name", First Name, ID, phone, adress, age X [Total age: 100] |009076
 abc, xyz, 1234, 982-128-0000, pqt,45
 bcd, uvw, 3124, 813-222-1111, tre,20
 poi, ccc, 9087, 123-45607890, weq,21
</code></pre>

<p>I am able to simply join the tables based on the keys into a <strong>new.csv</strong>, but can't put the data in the columns titles "<code>age</code>". I used "<code>csvkit</code>" to do that. </p>

<p>Here is what I used:</p>

<pre><code>csvjoin -c 3,1 info.csv age.csv &gt; new.csv
</code></pre>

<p>You can use <code>Pandas</code> and update the <code>info dataframe</code> using the <code>age</code> data. You do it by setting the index of both data frames to  <code>ID</code> and <code>student_id</code> respectively, then update the age column in the <code>info dataframe</code>. After that you reset the index so <code>ID</code> becomes a column again.</p>

<pre><code>from StringIO import StringIO
import pandas as pd

info = StringIO("""Last Name,First Name,ID,phone,adress,age X [Total age: 100] |009076
abc, xyz, 1234, 982-128-0000, pqt,
bcd, uvw, 3124, 813-222-1111, tre, 
poi, ccc, 9087, 123-45607890, weq,""")


age = StringIO("""student_id,age_1
3124,20
9087,21
1234,45""")

info_df = pd.read_csv(info, sep=",", engine='python')
age_df = pd.read_csv(age, sep=",", engine='python')

info_df = info_df.set_index('ID')
age_df = age_df.set_index('student_id')
info_df['age X [Total age: 100] |009076'].update(age_df.age_1)
info_df.reset_index(level=0, inplace=True)
info_df
</code></pre>

<p>outputs:</p>

<pre><code>    ID      Last Name   First Name      phone           adress  age X [Total age: 100] |009076
0   1234    abc         xyz              982-128-0000   pqt     45
1   3124    bcd         uvw              813-222-1111   tre     20
2   9087    poi         ccc              123-45607890   weq     21
</code></pre>

python csv inner-join csvkit