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>You can loop through rows with <code>apply</code> function(<code>axis = 1</code>) and construct a pandas series for each row based on the key value pairs after the splitting, and the newly constructed series will be automatically aligned by their index, just notice here there is no <code>F</code> column but an extra <code>H</code>, not sure if it is what you need. But removing the <code>H</code> and adding an extra NaN <code>F</code> column should be straight forward: </p>

<pre><code>df.apply(lambda r: pd.Series({x[0]: x[1] for x in r.str.split(' ') 
                                    if isinstance(x, list) and len(x) == 2}), axis = 1)


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

python csv pandas