Josh
Josh

Reputation: 1992

Split items in a series

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

Answers (1)

Anzel
Anzel

Reputation: 20583

Update

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

Related Questions