Reputation: 1992
I have a series that looks like this;
Name: TOR, Length: 162, dtype: object,
['TOR'],
0 [W, 9-7]
1 [W, 5-1]
2 [W, 8-2]
3 [L, 1-2]
4 [L, 2-6]
5 [W, 2-1]
etc.,
The data comes from a pandas data frame where each team has a column with the above data in it.How can I take this series and turn it into a data frame with one column for the W or L and the other for the score, 9-7? I would even be OK with just dropping everything after and including the ",".
Edit,
Sample from dictionary
WinLoss
{'NYY': [[u'L', u'2-6'], [u'L', u'1-3'],....
'MIN': [[u'L', u'3-5'], [u'L', u'6-7'], [u'W', u'10-9']....
'CIN': [[u'L', u'0-1'], [u'W', u'1-0'], [u'L', u'6-7'],
If i just
Wintable = pd.DataFrame(WinLoss)
I end up with the original problem
snippet of data frame;
>>> Wintable
ARI ATL BAL BOS CHC CHW \
0 [L, 1-3] [L, 0-2] [W, 2-1] [L, 1-2] [L, 0-1] [W, 5-3]
1 [L, 5-7] [W, 5-2] [L, 2-6] [W, 6-2] [L, 3-4] [W, 7-6]
2 [L, 8-9] [W, 1-0] [L, 3-4] [W, 4-3] [W, 3-2] [L, 9-10]
3 [W, 5-4] [W, 2-1] [L, 4-10] [L, 2-6] [L, 2-7] [L, 5-7]
4 [L, 0-2] [W, 6-2] [L, 6-7] [L, 6-7] [L, 0-2] [L, 3-4]
5 [L, 5-8] [L, 1-2] [W, 3-1] [L, 0-4] [W, 8-3] [W, 5-1]
6 [L, 2-12] [L, 0-4] [L, 2-4] [W, 5-1] [L, 6-7] [L, 1-8]
7 [L, 4-9] [W, 4-3] [W, 14-5] [L, 7-10] [W, 7-5] [W, 15-3]
Upvotes: 0
Views: 79
Reputation: 20583
As OP has changed the requirements, I have updated this answer to have a more straight forward approach.
Here is a working solution, probably not the best, but for one-off it does the work. Rather than converting the Series to workable DataFrame, you should tackle the source and read into DataFrame, something like this:
import pandas as pd
# remember the length of each 'NYC' ... 'CIN' has to be the same as you said, 162
WinLoss = {'NYY': [[u'L', u'2-6'], [u'L', u'1-3'], [u'W', u'2-1']],
'MIN': [[u'L', u'3-5'], [u'L', u'6-7'], [u'W', u'10-9']],
'CIN': [[u'L', u'0-1'], [u'W', u'1-0'], [u'L', u'6-7']]}
# construct an empty DataFrame here
df = pd.DataFrame()
# just loop through the dictionary you have, and write into DataFrame
# another update to shorten the syntax
df = pd.DataFrame()
for k,v in WinLoss.items():
# name the columns to whatever you want
df['{} {}'.format(k, 'Win/Loss')] = [r[0] for r in v]
df['{} {}'.format(k, 'Scores')] = [r[1] for r in v]
Sample Results:
df
NYY Win/Loss NYY Scores CIN Win/Loss CIN Scores MIN Win/Loss MIN Scores
0 L 2-6 L 0-1 L 3-5
1 L 1-3 W 1-0 L 6-7
2 W 2-1 L 6-7 W 10-9
Upvotes: 1