I have a list of strings which looks like this:
["Name: Alice, Department: HR, Salary: 60000", "Name: Bob, Department: Engineering, Salary: 45000"]
I would like to convert this list into a DataFrame that looks like this:
Name | Department | Salary
--------------------------
Alice | HR | 60000
Bob | Engineering | 45000
What would be the easiest way to go about this? My gut says throw the data into a CSV and separate titles with regex "^.*:", but there must be a simpler way
With some string processing you can get a list of dicts and pass that to the DataFrame constructor:
lst = ["Name: Alice, Department: HR, Salary: 60000",
"Name: Bob, Department: Engineering, Salary: 45000"]
pd.DataFrame([dict([kv.split(': ') for kv in record.split(', ')]) for record in lst])
Out:
Department Name Salary
0 HR Alice 60000
1 Engineering Bob 45000
you can do it this way:
In [271]: s
Out[271]:
['Name: Alice, Department: HR, Salary: 60000',
'Name: Bob, Department: Engineering, Salary: 45000']
In [272]: pd.read_csv(io.StringIO(re.sub(r'\s*(Name|Department|Salary):\s*', r'', '~'.join(s))),
...: names=['Name','Department','Salary'],
...: header=None,
...: lineterminator=r'~'
...: )
...:
Out[272]:
Name Department Salary
0 Alice HR 60000
1 Bob Engineering 45000
a little creative
s.str.extractall(r'(?P<key>[^,]+)\s*:(?P<value>[^,]+)') \
.reset_index('match', drop=True) \
.set_index('key', append=True).value.unstack()
setup
l = ["Name: Alice, Department: HR, Salary: 60000",
"Name: Bob, Department: Engineering, Salary: 45000"]
s = pd.Series(l)
:)