Wrangling a data frame in Pandas (Python)
<p>I have the following data in a csv file:</p>

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

the_data = """
ABC,2016-6-9 0:00,95,{'//Purple': [115L], '//Yellow': [403L], '//Blue': [16L], '//White-XYZ': [0L]}
ABC,2016-6-10 0:00,0,{'//Purple': [219L], '//Yellow': [381L], '//Blue': [90L], '//White-XYZ': [0L]}
ABC,2016-6-11 0:00,0,{'//Purple': [817L], '//Yellow': [21L], '//Blue': [31L], '//White-XYZ': [0L]}
ABC,2016-6-12 0:00,0,{'//Purple': [80L], '//Yellow': [2011L], '//Blue': [8888L], '//White-XYZ': [0L]}
ABC,2016-6-13 0:00,0,{'//Purple': [32L], '//Yellow': [15L], '//Blue': [4L], '//White-XYZ': [0L]}
DEF,2016-6-16 0:00,0,{'//Purple': [32L], '//Black': [15L], '//Pink': [4L], '//NPO-Green': [3L]}
DEF,2016-6-17 0:00,0,{'//Purple': [32L], '//Black': [15L], '//Pink': [4L], '//NPO-Green': [0L]}
DEF,2016-6-18 0:00,0,{'//Purple': [32L], '//Black': [15L], '//Pink': [4L], '//NPO-Green': [7L]}
DEF,2016-6-19 0:00,0,{'//Purple': [32L], '//Black': [15L], '//Pink': [4L], '//NPO-Green': [14L]}
DEF,2016-6-20 0:00,0,{'//Purple': [32L], '//Black': [15L], '//Pink': [4L], '//NPO-Green': [21L]}
"""
</code></pre>

<p>I read the data into a Pandas data frame, as follows:</p>

<pre><code>df = pd.read_csv(StringIO(the_data), sep=',', header=None)
</code></pre>

<p>The 'Company' and 'Date' fields will never change.</p>

<p>However, the 'keys' inside the curly braces (e.g. <code>"//PurpleCar"</code>, <code>"//YellowCar"</code>, <code>"//BlueCar"</code>, <code>"//WhiteCar"</code>, <code>"//BlackCar"</code>, <code>"//BlackCar"</code>and <code>"NPO-GreenCar"</code>) are <strong>not static</strong>.  They can (and will) change frequently.</p>

<p>(note: another script that I have outputs a dictionary and 'creates' this text file, hence this data structure)</p>

<p>I'd like to get the data frame to appear as follows so that I can use Matplotlib to create visualizations:</p>

<pre><code>   Company  Date       Purple   Yellow   Blue    White-XYZ   Black  Pink   NPO-Green  

0  ABC     2016-6-9    115      403      16      0            0     0      0
1  ABC     2016-6-10   219      381      90      0            0     0      0
2  ABC     2016-6-11   817      21       31      0            0     0      0
3  ABC     2016-6-12   80       2011     8888    0            0     0      0
4  ABC     2016-6-13   32       15       4       0            0     0      0
5  DEF     2016-6-16   32       0        0       0            15    4      3
6  DEF     2016-6-17   32       0        0       0            15    4      0
7  DEF     2016-6-18   32       0        0       0            15    4      7
8  DEF     2016-6-19   32       0        0       0            15    4      14
9  DEF     2016-6-20   32       0        0       0            15    4      21
</code></pre>

<p>The problems that I'm facing are:</p>

<p>a) moving the 'key' values up to the column headers</p>

<p>b) allowing the 'key' values to be dynamic (again, they can and will change)</p>

<p>c) removing the square braces (<code>'['</code> and <code>']'</code>)</p>

<p>d) removing the double slashes (<code>'//'</code>) </p>

<p>e) removing the "L" following the numerical value</p>

<p>Points 'c', 'd' and 'e' above can be addressed with the following issue (which is related):</p>

<p><a href="http://stackoverflow.com/questions/39928273/how-to-remove-curly-braces-apostrophes-and-square-brackets-from-dictionaries-in">How to remove curly braces, apostrophes and square brackets from dictionaries in a Pandas dataframe (Python)</a></p>

<p>It's points 'a' and 'b' that are the ones I'm struggling with.</p>

<p>Does anyone see a way to address these?</p>

<p>Thanks!</p>

<p><strong>* UPDATE *</strong></p>

<p>The data originally posted had a small mistake.  Here is the data:</p>

<pre><code>the_data = """
ABC,2016-6-9 0:00,95,"{'//Purple': [115L], '//Yellow': [403L], '//Blue': [16L], '//White-XYZ': [0L]}"
ABC,2016-6-10 0:00,0,"{'//Purple': [219L], '//Yellow': [381L], '//Blue': [90L], '//White-XYZ': [0L]}"
ABC,2016-6-11 0:00,0,"{'//Purple': [817L], '//Yellow': [21L], '//Blue': [31L], '//White-XYZ': [0L]}"
ABC,2016-6-12 0:00,0,"{'//Purple': [80L], '//Yellow': [2011L], '//Blue': [8888L], '//White-XYZ': [0L]}"
ABC,2016-6-13 0:00,0,"{'//Purple': [32L], '//Yellow': [15L], '//Blue': [4L], '//White-XYZ': [0L]}"
DEF,2016-6-16 0:00,0,"{'//Purple': [32L], '//Black': [15L], '//Pink': [4L], '//NPO-Green': [3L]}"
DEF,2016-6-17 0:00,0,"{'//Purple': [32L], '//Black': [15L], '//Pink': [4L], '//NPO-Green': [0L]}"
DEF,2016-6-18 0:00,0,"{'//Purple': [32L], '//Black': [15L], '//Pink': [4L], '//NPO-Green': [7L]}"
DEF,2016-6-19 0:00,0,"{'//Purple': [32L], '//Black': [15L], '//Pink': [4L], '//NPO-Green': [14L]}"
DEF,2016-6-20 0:00,0,"{'//Purple': [32L], '//Black': [15L], '//Pink': [4L], '//NPO-Green': [21L]}"
"""
</code></pre>

<p>The difference between this data and the original data is the apostrophes <code>(")</code> before the opening curly brace (<code>"{"</code>) and after the closing curly brace (<code>"}"</code>).</p>

<p>I really don't think this pandas can do much for you here. You're data is very obtuse and seems to me to be best dealt with using regular expressions. Here's my solution: </p>

<pre><code>import re

static_cols = []
dynamic_cols = []
for line in the_data.splitlines():
    if line == '':
        continue

    # deal with static columns
    x = line.split(',')
    company, date, other = x[0:3]
    keys = ['Company', 'Date', 'Other']
    values = [company, date, other]
    d = {i: j for i, j in zip(keys, values)}
    static_cols.append(d)

    # deal with dynamic columns
    keys = re.findall(r'(?&lt;=//)[^\']*', line)
    values = re.findall(r'\d+(?=L)', line)
    d = {i: j for i, j in zip(keys, values)}
    dynamic_cols.append(d)

df1 = pd.DataFrame(static_cols)
df2 = pd.DataFrame(dynamic_cols)
df = pd.concat([df1, df2], axis=1)
</code></pre>

<p>And the output: </p>

<p><a href="http://i.stack.imgur.com/0i4f8.png" rel="nofollow"><img src="http://i.stack.imgur.com/0i4f8.png" alt="enter image description here"></a> </p>

<p>Also, your data had an extra column after the date I wasn't sure how to deal with so I just called it 'Other'. It wasn't included in your output, so you can easily remove it if you want as well. </p>

python regex pandas