regex - How to remove curly braces, apostrophes and square brackets from dictionaries in a Pandas dataframe (Python)

Question

I have the following data in a csv file:

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]}
"""

I read the file into a Pandas data frame, as follows:

df = pd.read_csv(StringIO(the_data), sep=',')

Then, I add a few column headers, as follows:

df.columns = ['Company',
                    'Date',
                    'Volume',
                    'Car1',
                    'Car2',
                    'Car3',
                    'Car4']

I see that the data is coming through as follows:

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

But, I'd like to see the data without any of the following:

a) the curly braces ("{") at the beginning and the curly brace ("}") at the end of the dictionary

b) the "L" after the numerical values

c) the square brackets ("[" and "]") surrounding the numerical values

d) the apostrophes surrounding the keys

Ideally, the data would be transformed as follows:

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

I tried this:

df['Car1'] = df['Car1'].str.strip(['{', '}', '[', 'L]'])

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

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

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

Thanks!

UPDATE:

Using the following regular expression:

df['Car1'] = df['Car1'].str.replace(r'D+', '').astype('int')

Results in this:

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

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.

Any ideas?

---

UPDATE 2:

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:

   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

* UPDATE 3: *

The data originally posted had a small mistake. Here is the data:

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]}"
"""

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

Answer 1

Edit: The file seems to be actually an escaped CSV so we don't need a custom parsing for this part.

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

1. You don't control the data and thus can't properly escape the commas.
2. The first three columns won't contain any comma.
3. The third column follows the syntax of a python dict.
4. There is always one value in the list which is in the dict values.

First, some imports

import ast
import pandas as pd

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).

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"])

fixed_columns = pd.read_csv(..., names=["Company", "Date", "Value", "Cars_str"])

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

cars = fixed_columns["Cars_str"].apply(ast.literal_eval)
del fixed_columns["Cars_str"]

And this part answers rather your other question.

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

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:]

We apply the functions and construct pd.Series which allow us to...

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

...add the columns to the dataframe

result = pd.concat([fixed_columns, dynamic_columns], axis=1)
result

Finally, we get the table:

  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