pandas dataframe with list elements: split, pad

Question

I have a pandas dataframe (NROWS x 1) where each row is a list , such as

    y 
0   [[aa, bb], 0000001]   
1   [[uz, mk], 0000011]

I want to flatten the list and split into (in this case three) columns like so:

    1  2  3
0   aa bb 0000001
1   uz mk 0000011

Further, different rows have unequal lengths:

    y
0   [[aa, bb], 0000001]
1   [[mk], 0000011]

What I really want to end up with is, detect the max length over all rows and pad the rest to empty string ''. In this example,

    1  2  3
0   aa bb 0000001
1   '' mk 0000011

I've toyed around with doing .values.tolist() but it doesn't do what I need.

Edit- the answers below are super neat and much appreciated. I'm editing to include a solution for a similar but simpler problem, for completeness.

Read data, use the trim() fn from Strip / trim all strings of a dataframe to make sure there is no left/right whitespace

df = pd.read_csv('data.csv',sep=',',dtype=str)
df = trim_all_columns(df)

Keep categorical/nominal ID and CODE columns, remove all NA

df.dropna(subset=['dg_cd'] , inplace=True) # drop dg_cd is NaN rows from df 

df2 = df[['id','dg_cd']]

Turn CODE into sentences by ID keeping all repeated instances

x = df2.groupby('id').apply(lambda x: x['dg_cd'].values.tolist()).apply(pd.Series).replace(np.nan, '', regex=True)

The reason for doing all that is because that feeds into a k-modes cluster search, https://pypi.org/project/kmodes/. NA is not an acceptable input but empty strings

''

allow rows of same length while there is no spurious similarity. For example,

km = KModes(n_clusters=4, init='Cao', n_init=1, verbose=1)

clusters = km.fit_predict( x )

how would the code know that if the list has only 2 elements, then does that put the first item second in the list? — Umar.H, Jun 10 '19 at 21:30

score 4 · Answer 1 · answered Jun 10 '19 at 21:30

Setup

df = pd.DataFrame(dict(y=[
    [['aa', 'bb'], '0000001'],
    [['uz', 'mk'], '0000011'],
    [['mk'], '0000111']
]))

df

                     y
0  [[aa, bb], 0000001]
1  [[uz, mk], 0000011]
2      [[mk], 0000111]

`flatten`

From @wim

def flatten(x):
    try:
        it = iter(x)
    except TypeError:
        yield x
        return
    if isinstance(x, str):
        yield x
        return
    for elem in it:
        yield from flatten(elem)

d = dict(zip(df.index, [dict(enumerate([*flatten(x)][::-1])) for x in df.y]))

d = pd.DataFrame.from_dict(d, 'index').fillna('')
d.iloc[:, ::-1].rename(columns=lambda x: d.shape[1] - x)

    1   2        3
0  aa  bb  0000001
1  uz  mk  0000011
2      mk  0000111

score 2 · Answer 2 · answered Jun 10 '19 at 22:30

After using the same function flatten the list

pd.DataFrame(list(map(lambda x : list(flatten(x)),df.y.tolist()))).apply(lambda x : pd.Series(sorted(x,key=pd.notna)),1)
Out[85]: 
      0   1        2
0    aa  bb  0000001
1    uz  mk  0000011
2  None  mk  0000111

ayorgo · Answer 3 · 2019-06-11T11:42:26.113

In case you want to have control over which side to pad the sublists from:

max_len = df['y'].apply(lambda row: len(row[0])).max()

pd.DataFrame([*df['y'].apply(lambda row: ['']*(max_len - len(row[0])) + row[0] + row[1:])])

Which, using @piRSquared's setup gives

    0   1        2
0  aa  bb  0000001
1  uz  mk  0000011
2      mk  0000111

Or, alternatively

pd.DataFrame([*df['y'].apply(lambda row: row[0] + ['']*(max_len - len(row[0])) + row[1:])])

giving you

    0   1        2
0  aa  bb  0000001
1  uz  mk  0000011
2  mk      0000111

pandas dataframe with list elements: split, pad

3 Answers3

Setup

flatten

`flatten`