Optimal way to add small lists to Pandas DataFrame
<p>I'm parsing some logs that contain HTTP transactions into  a Pandas DataFrame. Each row is one trasaction, so one column has the IP address, the other has the hostname, etc. For each row (log entry) I'd like to extract the header parameters into a list, and store that list with the rest of the info for that row.</p>

<p>The question is: how to store the list of parameters so that it can be easily cross-referenced with the rest of the data from the log?</p>

<p>For the sake of example, imagine I have this dataframe, where a user's list of pets is stored as a string, and we want to parse out the individual animals and store it as a list. The idea is to store the result of parsing the string so that the actual parse routine only has to run once.</p>

<pre><code># Original Dataframe
User   | PetsString
----------------------
Mary   | 'dog/cat/rat'
John   | 'dog/lizard'
</code></pre>

<p><strong>Method 1)</strong> I can add a column to the dataframe and store the list in this column. </p>

<pre><code>User   | PetsString    | PetsList
--------------------------------------------
Mary   | 'dog/cat/rat' | ['dog','cat','rat']
John   | 'dog/lizard'  | ['dog','lizard']
</code></pre>

<p><strong>Method 2)</strong> I can create another dataframe that has the list entries, with a column indicating the position of the log in the original dataframe for corss-referencing. I'd like to avoid this since I assume that iterating over two dataframes simultaneously is slower than iterating over a single large list. E.g.</p>

<pre><code>User    | PetsString
-----------------------
Mary    | 'dog/cat/rat'
John    | 'dog/lizard'

#Separate DataFrame for cross-reference

User  | Pet
-----------------
Mary  | 'dog'
Mary  | 'cat'
Mary  | 'rat'
John  | 'dog'
John  | 'lizard'
</code></pre>

<p><strong>Method 3)</strong> Someone suggested adding, say, 50 columns to my existing dataframe and storing each list item in one of the columns. I don't expect to have more than 50 header parameters. This seems speed-optimal but has the nasty limitation in the number of columns. E.g.</p>

<pre><code>User    | PetsString     | Pet0    | Pet1     | Pet2
------------------------------------------------------
Mary    | 'dog/cat/rat'  | 'dog'   | 'cat'    | 'rat'
John    | 'dog/lizard'   | 'dog'   | 'lizard' |
</code></pre>

<p>I have two questions:</p>

<p><strong>(i)</strong> Assuming I need to compute a function that will read a row and all parameters from the corresponding list, which of the three layouts is speed-optimal?</p>

<p><strong>(ii)</strong> Which of these is space-optimal? I'm not sure how Pandas works with objects, but I believe that if I use method 1, Pandas will create a column that is as wide as the longest list. Similarly, Method 3 will have to allocate space for a full 'Pet2' column, even if John doesn't have one</p>

<p>I know a lot of these things may be specific to my particular processor, cache size, use-case, etc. but even a general understanding of the tradeoffs would be very useful to me</p>

<p>thanks in advance for your help!</p>

<p>The values in the columns of a Pandas DataFrame are stored in homogeneous numpy arrays. Consider the following:</p>

<pre><code>In [95]: pd.DataFrame({'a': ['foo', 'bar/baz']}).a.dtype
Out[95]: dtype('O')

In [96]: pd.DataFrame({'a': [['foo'], ['bar', 'baz']]}).a.dtype
Out[96]: dtype('O')
</code></pre>

<p>This shows that:</p>

<ol>
<li><p>When you store strings of different lengths, Pandas uses a numpy array of objects (numpy also has string arrays for equally fixed-size strings, but Pandas doesn't use them).</p></li>
<li><p>When you store lists, Pandas also uses a numpy object array.</p></li>
</ol>

<p>Based on this, I think that your first option will have good memory and speed performance. Pandas and numpy have an advantage over regular Python data structures, in things like huge numerical sequences, where the Python overhead of a single number object is huge. A Pyton <code>list</code> of strings is pretty efficient, and a numpy array of (non fixed-size) strings doesn't really have an advantage over it.</p>

<p>In fact, you might consider if Pandas offers any advantages here at all over plain vanilla Python. Why not a <code>dict</code> mapping strings to <code>list</code>s of strings, for example?</p>

python pandas