Python Pandas Expand a Column of List of Lists to Two New Column

Question

I have a DF which looks like this.

name    id  apps
john    1   [[app1, v1], [app2, v2], [app3,v3]]
smith   2   [[app1, v1], [app4, v4]]

I want to expand the apps column such that it looks like this.

name    id  app_name    app_version
john    1   app1        v1
john    1   app2        v2
john    1   app3        v3
smith   2   app1        v1
smith   2   app4        v4

Any help is appreciated

score 7 · Accepted Answer · answered May 12 '19 at 01:06

7

You can .apply(pd.Series) twice to get what you need as an intermediate step, then merge back to the original dataframe.

import pandas as pd

df = pd.DataFrame({
    'name': ['john', 'smith'],
    'id': [1, 2],
    'apps': [[['app1', 'v1'], ['app2', 'v2'], ['app3','v3']], 
             [['app1', 'v1'], ['app4', 'v4']]]
})

dftmp = df.apps.apply(pd.Series).T.melt().dropna()
dfapp = (dftmp.value
              .apply(pd.Series)
              .set_index(dftmp.variable)
              .rename(columns={0:'app_name', 1:'app_version'})
        )

df[['name', 'id']].merge(dfapp, left_index=True, right_index=True)
# returns:
    name  id app_name app_version
0   john   1     app1          v1
0   john   1     app2          v2
0   john   1     app3          v3
1  smith   2     app1          v1
1  smith   2     app4          v4

answered May 12 '19 at 01:06

James

32,991
4
47
70

Instead of `.apply(pd.Series)` (which is awfully slow), use `pd.DataFrame(df.apps.tolist())` – rafaelc May 12 '19 at 01:13
Either way you are pulling it out of the C-backed API into Python. `.apply` hides a `for` loop, while `tolist` pushes the encapsulated object back to Python. I have not done any tests to see which is faster. – James May 12 '19 at 01:21
1

I have, that's why I commented. – rafaelc May 12 '19 at 02:19
Can also refer [here](https://stackoverflow.com/a/35491399) for details – rafaelc May 12 '19 at 02:20
1

@James it's 1.1s vs 900 microseconds, so its like 1000 times faster, which is amazing. – Quang Hoang May 12 '19 at 02:35

BENY · Answer 2 · 2019-05-12T04:43:49.520

Chain of pd.Series easy to understand, also if you would like know more methods ,check unnesting

df.set_index(['name','id']).apps.apply(pd.Series).\
         stack().apply(pd.Series).\
            reset_index(level=[0,1]).\
                rename(columns={0:'app_name',1:'app_version'})
Out[541]: 
    name  id app_name app_version
0   john   1     app1          v1
1   john   1     app2          v2
2   john   1     app3          v3
0  smith   2     app1          v1
1  smith   2     app4          v4

Method two slightly modify the function I write

def unnesting(df, explode):
    idx = df.index.repeat(df[explode[0]].str.len())
    df1 = pd.concat([
        pd.DataFrame({x: sum(df[x].tolist(),[])}) for x in explode], axis=1)
    df1.index = idx
    return df1.join(df.drop(explode, 1), how='left')

Then

yourdf=unnesting(df,['apps'])

yourdf['app_name'],yourdf['app_version']=yourdf.apps.str[0],yourdf.apps.str[1]
yourdf
Out[548]: 
         apps  id   name app_name app_version
0  [app1, v1]   1   john     app1          v1
0  [app2, v2]   1   john     app2          v2
0  [app3, v3]   1   john     app3          v3
1  [app1, v1]   2  smith     app1          v1
1  [app4, v4]   2  smith     app4          v4

Or

yourdf=unnesting(df,['apps']).reindex(columns=df.columns.tolist()+['app_name','app_version'])
yourdf[['app_name','app_version']]=yourdf.apps.tolist()
yourdf
Out[567]: 
         apps  id   name app_name app_version
0  [app1, v1]   1   john     app1          v1
0  [app2, v2]   1   john     app2          v2
0  [app3, v3]   1   john     app3          v3
1  [app1, v1]   2  smith     app1          v1
1  [app4, v4]   2  smith     app4          v4

MaPy · Answer 3 · 2019-05-12T01:14:29.523

3

You can always have a brute force solution. Something like:

name, id, app_name, app_version = [], [], [], []
for i in range(len(df)):
    for v in df.loc[i,'apps']:
        app_name.append(v[0])
        app_version.append(v[1])
        name.append(df.loc[i, 'name'])
        id.append(df.loc[i, 'id'])
df = pd.DataFrame({'name': name, 'id': id, 'app_name': app_name, 'app_version': app_version})

will do the work.

Note that I assumed df['apps'] is lists of strings if df['apps'] is strings then you need: eval(df.loc[i,'apps']) instead of df.loc[i,'apps']

edited May 12 '19 at 01:14

answered May 12 '19 at 01:05

MaPy

505
1
6
9

3

Even though this works, it is probably unfeasible for large data frames. In pandas, one for loop is already bad enough, so imagine two nested for loops ;} Always try to avoid direct iteration ! – rafaelc May 12 '19 at 01:15

anky · Answer 4 · 2019-05-12T04:25:30.533

Another approach would be (should be quite fast too):

#Repeat the columns without the list by the str length of the list
m=df.drop('apps',1).loc[df.index.repeat(df.apps.str.len())].reset_index(drop=True)
#creating a df exploding the list to 2 columns
n=pd.DataFrame(np.concatenate(df.apps.values),columns=['app_name','app_version'])
#concat them together
df_new=pd.concat([m,n],axis=1)

    name id app_name app_version
0   john  1     app1          v1
1   john  1     app2          v2
2   john  1     app3          v3
3  smith  2     app1          v1
4  smith  2     app4          v4

araraonline · Answer 5 · 2019-05-12T13:11:28.923

My suggestion (there may be easier ways) is using DataFrame.apply alongside pd.concat:

def expand_row(row):
    return pd.DataFrame({
        'name': row['name'], # row.name is the name of the series
        'id': row['id'],
        'app_name': [app[0] for app in row.apps],
        'app_version': [app[1] for app in row.apps]
    })

temp_dfs = df.apply(expand_row, axis=1).tolist()
expanded = pd.concat(temp_dfs)
expanded = expanded.reset_index() # put index in the correct order

print(expanded)

#     name  id app_name app_version
# 0   john   1     app1          v1
# 1   john   1     app2          v2
# 2   john   1     app3          v3
# 3  smith   2     app1          v1
# 4  smith   2     app4          v4

Also, here is a solution using python only, which, if my intuition is correct, should be fast:

rows = df.values.tolist()
expanded = [[row[0], row[1], app[0], app[1]]
            for row in rows
            for app in row[2]]
df = pd.DataFrame(
    expanded, columns=['name', 'id', 'app_name', 'app_version'])

#     name  id app_name app_version
# 0   john   1     app1          v1
# 1   john   1     app2          v2
# 2   john   1     app3          v3
# 3  smith   2     app1          v1
# 4  smith   2     app4          v4

score 0 · Answer 6 · answered Nov 18 '21 at 11:45

I have taken James' answer and amended as per rafaelc's suggestion about using pd.Dataframe.apps.tolist()) to speed it up:

import pandas as pd

df_melted = pd.DataFrame(df.apps.tolist()).T.melt().dropna()
df_tmp = pd.DataFrame(df_melted.value.tolist(), 
             columns = ['app_name', 'app_version'],
             index = df_melted.variable)

df = df_prices.join(df_tmp)
df.drop(columns=['prices'], inplace = True)

Python Pandas Expand a Column of List of Lists to Two New Column

6 Answers6