Combine two files with Pandas
<p>I am having trouble using pandas to combine two input files as shown in the data sample below. They start out as CSV files exported from WordPress. I load them into data frames. My idea was to create an empty output data frame and fill it by looping through each <code>id</code> in the first input file, but that seems cumbersome and not taking advantage of Pandas' strengths. And because I'm new to Pandas I can't figure out how to convert the list-type second file into my desired output format.</p>

<p><strong>input_file_1:</strong></p>

<pre><code>id postDate
23 2016-10-03
24 2016-02-15
25 2016-07-22
</code></pre>

<p><strong>input_file_2:</strong></p>

<pre><code>id key    value
23 name   smith
23 age    24
23 city   boston
24 name   jones
24 age    35
24 city   chicago
25 name   williams
25 age    21
25 city   dallas
</code></pre>

<p><strong>desired_output_file:</strong></p>

<pre><code>id postDate   name     age city
23 2016-10-03 smith    24  boston
24 2016-02-15 jones    35  chicago
25 2016-07-22 williams 21  dallas
</code></pre>

<p>you can use <code>pivot</code> in conjunction with <code>join</code>:</p>

<pre><code>In [126]: df1.set_index('id').join(df2.pivot(index='id', columns='key', values='value'))
Out[126]:
      postDate age     city      name
id
23  2016-10-03  24   boston     smith
24  2016-02-15  35  chicago     jones
25  2016-07-22  21   dallas  williams
</code></pre>

<p>explanation:</p>

<pre><code>In [127]: df2.pivot(index='id', columns='key', values='value')
Out[127]:
key age     city      name
id
23   24   boston     smith
24   35  chicago     jones
25   21   dallas  williams
</code></pre>

python pandas