How to remove curly braces, apostrophes and square brackets from dictionaries in a Pandas dataframe (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,{'//PurpleCar': [115L], '//YellowCar': [403L], '//BlueCar': [16L], '//WhiteCar-XYZ': [0L]}
ABC,2016-6-10 0:00,0,{'//PurpleCar': [219L], '//YellowCar': [381L], '//BlueCar': [90L], '//WhiteCar-XYZ': [0L]}
ABC,2016-6-11 0:00,0,{'//PurpleCar': [817L], '//YellowCar': [21L], '//BlueCar': [31L], '//WhiteCar-XYZ': [0L]}
ABC,2016-6-12 0:00,0,{'//PurpleCar': [80L], '//YellowCar': [2011L], '//BlueCar': [8888L], '//WhiteCar-XYZ': [0L]}
ABC,2016-6-13 0:00,0,{'//PurpleCar': [32L], '//YellowCar': [15L], '//BlueCar': [4L], '//WhiteCar-XYZ': [0L]}
DEF,2016-6-16 0:00,0,{'//PurpleCar': [32L], '//BlackCar': [15L], '//PinkCar': [4L], '//NPO-GreenCar': [0L]}
DEF,2016-6-17 0:00,0,{'//PurpleCar': [32L], '//BlackCar': [15L], '//PinkCar': [4L], '//NPO-GreenCar': [0L]}
DEF,2016-6-18 0:00,0,{'//PurpleCar': [32L], '//BlackCar': [15L], '//PinkCar': [4L], '//NPO-GreenCar': [0L]}
DEF,2016-6-19 0:00,0,{'//PurpleCar': [32L], '//BlackCar': [15L], '//PinkCar': [4L], '//NPO-GreenCar': [0L]}
DEF,2016-6-20 0:00,0,{'//PurpleCar': [32L], '//BlackCar': [15L], '//PinkCar': [4L], '//NPO-GreenCar': [0L]}
"""
</code></pre>

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

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

<p>Then, I add a few column headers, as follows:</p>

<pre><code>df.columns = ['Company',
                    'Date',
                    'Volume',
                    'Car1',
                    'Car2',
                    'Car3',
                    'Car4']
</code></pre>

<p>I see that the data is coming through as follows:</p>

<pre><code>ABC,2016-6-9 0:00,95,{'//PurpleCar': [115L], '//YellowCar': [403L], '//BlueCar': [16L], '//WhiteCar-XYZ': [0L]
</code></pre>

<p>But, I'd like to see the data <strong>without</strong> any of the following:</p>

<p>a) the curly braces (<code>"{"</code>) at the beginning and the curly brace (<code>"}"</code>) at the end of the dictionary</p>

<p>b) the "L" after the numerical values</p>

<p>c) the square brackets (<code>"["</code> and <code>"]"</code>) surrounding the numerical values</p>

<p>d) the apostrophes surrounding the keys</p>

<p>Ideally, the data would be transformed as follows:</p>

<pre><code>ABC,2016-6-9 0:00,95,//PurpleCar: 115, //YellowCar: 403, //BlueCar: 16, //WhiteCar-XYZ: 0
</code></pre>

<p>I tried this:</p>

<pre><code>df['Car1'] = df['Car1'].str.strip(['{', '}', '[', 'L]'])
</code></pre>

<p>But, it doesn't work.  It results in the 'Car1' column becoming NaN values.</p>

<p>Is it possible to transform the data frame such that each row of the data frame reads as follows?</p>

<pre><code>ABC,2016-6-9 0:00,95,//PurpleCar: 115, //YellowCar: 403, //BlueCar: 16, //WhiteCar-XYZ: 0
</code></pre>

<p>Thanks!</p>

<p><strong>UPDATE</strong>:</p>

<p>Using the following regular expression:</p>

<pre><code>df['Car1'] = df['Car1'].str.replace(r'\D+', '').astype('int')
</code></pre>

<p>Results in this:</p>

<pre><code>ABC,2016-6-9 0:00,95, 115 , //YellowCar: 403, //BlueCar: 16, //WhiteCar-XYZ: 0
</code></pre>

<p>We lose '//PurpleCar' and are left with only the numeric value of 115.  That's a good start, but it would be great if we can see the '//PurpleCar' key, too.</p>

<p>Any ideas?</p>

<hr>

<p><strong>UPDATE 2:</strong></p>

<p>Based on the comments by piRSquared and HYRY, my goal is to be able to plot the numerical results.  So, I would like to have the data frame look as follows:</p>

<pre><code>   Company   Date            PurpleCar  YellowCar   BlueCar     WhiteCar      

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

<p><strong>* UPDATE 3: *</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><strong>Edit:</strong> The file seems to be actually an escaped CSV so we don't need a custom parsing for this part.</p>

<p><strike>As @Blckknght points out in the comment, the file is not a valid CSV. I'll make some assumptions in my answer. They are</p>

<ol>
<li>You don't control the data and thus can't properly escape the commas.</li>
<li>The first three columns won't contain any comma.</strike></li>
<li>The third column follows the syntax of a python dict.</li>
<li>There is always one value in the list which is in the dict values.</li>
</ol>

<p>First, some imports</p>

<pre><code>import ast
import pandas as pd
</code></pre>

<p><strike>We'll just split the rows by commas as we don't need to deal with any sort of CSV escaping (assumptions #1 and #2).</p>

<pre><code>rows = (line.split(",", 3) for line in the_data.splitlines() if line.strip() != "")

fixed_columns = pd.DataFrame.from_records(rows, columns=["Company", "Date", "Value", "Cars_str"])
</code></pre>

<p></strike></p>

<pre><code>fixed_columns = pd.read_csv(..., names=["Company", "Date", "Value", "Cars_str"])
</code></pre>

<p>The first three columns are fixed and we leave them as they are. The last column we can parse with <code>ast.literal_eval</code> because it's a <code>dict</code> (assumption #3). This is IMO more readable and more flexible if the format changes than regex. Also you'll detect the format change earlier.</p>

<pre><code>cars = fixed_columns["Cars_str"].apply(ast.literal_eval)
del fixed_columns["Cars_str"]
</code></pre>

<p>And this part answers rather <a href="http://stackoverflow.com/questions/39935744/wrangling-a-data-frame-in-pandas-python">your other question</a>.</p>

<p>We prepare functions to process the keys and values of the dict so they fail if our assumptions about content of the dict fail.</p>

<pre><code>def get_single_item(list_that_always_has_single_item):
    v, = list_that_always_has_single_item
    return v

def extract_car_name(car_str):
    assert car_str.startswith("//"), car_str
    return car_str[2:]
</code></pre>

<p>We apply the functions and construct <code>pd.Series</code> which allow us to...</p>

<pre><code>dynamic_columns = cars.apply(
    lambda x: pd.Series({
            extract_car_name(k): get_single_item(v) 
            for k, v in x.items()
    }))    
</code></pre>

<p>...add the columns to the dataframe</p>

<pre><code>result = pd.concat([fixed_columns, dynamic_columns], axis=1)
result
</code></pre>

<p>Finally, we get the table:</p>

<pre><code>  Company            Date Value  BlackCar  BlueCar  NPO-GreenCar  PinkCar  \
0     ABC   2016-6-9 0:00    95       NaN     16.0           NaN      NaN   
1     ABC  2016-6-10 0:00     0       NaN     90.0           NaN      NaN   
2     ABC  2016-6-11 0:00     0       NaN     31.0           NaN      NaN   
3     ABC  2016-6-12 0:00     0       NaN   8888.0           NaN      NaN   
4     ABC  2016-6-13 0:00     0       NaN      4.0           NaN      NaN   
5     DEF  2016-6-16 0:00     0      15.0      NaN           0.0      4.0   
6     DEF  2016-6-17 0:00     0      15.0      NaN           0.0      4.0   
7     DEF  2016-6-18 0:00     0      15.0      NaN           0.0      4.0   
8     DEF  2016-6-19 0:00     0      15.0      NaN           0.0      4.0   
9     DEF  2016-6-20 0:00     0      15.0      NaN           0.0      4.0   

   PurpleCar  WhiteCar-XYZ  YellowCar  
0      115.0           0.0      403.0  
1      219.0           0.0      381.0  
2      817.0           0.0       21.0  
3       80.0           0.0     2011.0  
4       32.0           0.0       15.0  
5       32.0           NaN        NaN  
6       32.0           NaN        NaN  
7       32.0           NaN        NaN  
8       32.0           NaN        NaN  
9       32.0           NaN        NaN  
</code></pre>

python regex pandas