Need to create a Pandas dataframe by reading csv file with random columns
<p>I have the following csv file with records:</p>

<ul>
<li>A 1, B 2, C 10, D 15</li>
<li>A 5, D 10, G 2</li>
<li>D 6, E 7</li>
<li>H 7, G 8</li>
</ul>

<p>My column headers/names are:  A, B, C, D, E, F, G</p>

<p>So my initial dataframe after using "read_csv" becomes: </p>

<pre><code>A     B     C      D       E      F      G   
A 1   B 2   C 10   D 15   NaN    NaN    NaN
A 5   D 10  G 2    NaN    NaN    NaN    NaN
D 6   E 7   NaN    NaN    NaN    NaN    NaN
H 7   G 8   NaN    NaN    NaN    NaN    Nan
</code></pre>

<p>The value can be separate into [column name][column value], so A 1 means col=A and value=1, and D 15 means col=D and value=15, etc...</p>

<p>What I want is to assign the numeric value to the appropriate column based on the 
and have a dataframe that looks like this:</p>

<pre><code>A     B     C      D       E      F      G   
A 1   B 2   C 10   D 15   NaN    NaN    NaN
A 5   Nan   NaN    D 10   NaN    NaN    G 2
NaN   NaN   NaN    D 6    E 7    NaN    NaN
NaN   NaN   NaN    NaN    NaN    NaN    G 8
</code></pre>

<p>And even better, just the values alone:</p>

<pre><code>A     B     C      D       E      F      G   
1     2     10     15      NaN    NaN    NaN
5     Nan   NaN    10      NaN    NaN    2
NaN   NaN   NaN    6       7      NaN    NaN
NaN   NaN   NaN    NaN     NaN    NaN    8
</code></pre>

<p><strong>Apply</strong> solution:</p>

<p>Use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.split.html" rel="nofollow"><code>split</code></a> by whitespace, remove <code>NaN</code> rows by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.dropna.html" rel="nofollow"><code>dropna</code></a>, <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.set_index.html" rel="nofollow"><code>set_index</code></a> and convert one column <code>DataFrame</code> to <code>Series</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.squeeze.html" rel="nofollow"><code>DataFrame.squeeze</code></a>. Last <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html" rel="nofollow"><code>reindex</code></a> by new column names:</p>

<pre><code>print (df.apply(lambda x: x.str.split(expand=True)
                               .dropna()
                               .set_index(0)
                               .squeeze(), axis=1)
         .reindex(columns=list('ABCDEFGH')))

     A    B    C    D    E   F    G    H
0    1    2   10   15  NaN NaN  NaN  NaN
1    5  NaN  NaN   10  NaN NaN    2  NaN
2  NaN  NaN  NaN    6    7 NaN  NaN  NaN
3  NaN  NaN  NaN  NaN  NaN NaN    8    7
</code></pre>

<p><strong>Stack</strong> solution:</p>

<p>Use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.stack.html" rel="nofollow"><code>stack</code></a> for creating <code>Series</code>, <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.split.html" rel="nofollow"><code>split</code></a> by whitespace and create new columns, append column with new column names (<code>A</code>, <code>B</code>...) to <code>index</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.set_index.html" rel="nofollow"><code>set_index</code></a>, convert one column <code>DataFrame</code> to <code>Series</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.squeeze.html" rel="nofollow"><code>DataFrame.squeeze</code></a>, remove index values with old column names by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html" rel="nofollow"><code>reset_index</code></a>, <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.unstack.html" rel="nofollow"><code>unstack</code></a>, <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html" rel="nofollow"><code>reindex</code></a> by new column names (it add missing columns filled by <code>NaN</code>),convert values to <code>float</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.astype.html" rel="nofollow"><code>astype</code></a> and last remove column name by <a href="http://pandas.pydata.org/pandas-docs/stable/whatsnew.html#changes-to-rename" rel="nofollow"><code>rename_axis</code></a> (new in <code>pandas</code> <code>0.18.0</code>):</p>

<pre><code>print (df.stack()
         .str.split(expand=True)
         .set_index(0, append=True)
         .squeeze()
         .reset_index(level=1, drop=True)
         .unstack()
         .reindex(columns=list('ABCDEFGH'))
         .astype(float)
         .rename_axis(None, axis=1))

     A    B     C     D    E   F    G    H
0  1.0  2.0  10.0  15.0  NaN NaN  NaN  NaN
1  5.0  NaN   NaN  10.0  NaN NaN  2.0  NaN
2  NaN  NaN   NaN   6.0  7.0 NaN  NaN  NaN
3  NaN  NaN   NaN   NaN  NaN NaN  8.0  7.0
</code></pre>

python csv pandas