[FIXED] Split list in a dataframe in multiple columns using one as the header

Issue

Suppose I got a pandas dataframe with two columns containing a list (length >= 1) at the end. The first one ("mode") has a variable which should be appended to the desired header name, the second column ("res") contains the data:

>>> df = pd.DataFrame([ 
    { 'c1': 850, 'c2': 'Ex', 'c3': 300.0, 'c4': 250, 'mode': [0, 1], 'res': [1.525, 1.321] },
    { 'c1': 850, 'c2': 'Ex', 'c3': 300.0, 'c4': 250, 'mode': [0, 1], 'res': [1.526, 1.311] }
])

with the result

    c1  c2     c3   c4    mode             res
0  850  Ex  300.0  250  [0, 1]  [1.525, 1.321]
1  850  Ex  300.0  250  [0, 1]  [1.526, 1.311]

Is there a better way to split the dataframe df to get this desired result

    c1  c2  c3      c4   res_mode_0  res_mode_1
0  850  Ex  300.0   250       1.525       1.321
1  850  Ex  310.0   250       1.526       1.311

than using loops?

Solution

You can try the following code. Advantage is it works regardless of the number of elements in the list.

df = pd.DataFrame([ 
    { 'c1': 850, 'c2': 'Ex', 'c3': 300.0, 'c4': 250, 'mode': [0, 1], 'res': [1.525, 1.321] },
    { 'c1': 850, 'c2': 'Ex', 'c3': 300.0, 'c4': 250, 'mode': [0, 1], 'res': [1.526, 1.311] }
])

split_df = pd.DataFrame(df["res"].tolist()).add_prefix("res_mode_")

df = pd.concat([df, split_df], axis=1).drop(["mode", "res"], axis=1)

Output:

df

    c1      c2  c3      c4      res_mode_0  res_mode_1
0   850     Ex  300.0   250     1.525       1.321
1   850     Ex  300.0   250     1.526       1.311

Answered By - Marco_CH