Split pandas list to different column and calculate the counts

Question

I've a pandas dataframe with a column name ids that contains list elements. So I want to split the list column to different columns.

id          partner_id                       ids
1                12             ["1","4","187275","187358","946475"]
2                12             ["1","191","28925","31441"]
3                16             ["1","2","293915","1573130","293918"]
4                11             ["1","13","294064","1238496"]
5                16             ["1","153339","155025","155029"]

Desired output:

id          partner_id          id1   id2     id3      id4        id5 
1                12             1     4      187275    187358    946475     
2                12             1     191    28925     31441     NaN     
3                16             1     2      293915    1573130   293918              
4                11             1     13     294064    1238496   NaN       
5                16             1     153339 155025    155029    NaN

What I've tried:

df2 = pd.DataFrame(df.parent_path.values.tolist(), index=df.index)

Full Code:

import pandas as pd
import numpy as np
pd.set_option('display.max_columns', 85)
pd.set_option('display.max_rows', 85)
df = pd.read_csv('../dataset/property_location_count.csv',low_memory=False)
df2 = pd.DataFrame(df.ids.values.tolist(), index=df.index)

But it doesn't split the columns as it does here :https://stackoverflow.com/a/35491399/1138192

https://stackoverflow.com/questions/35491274/pandas-split-column-of-lists-into-multiple-columns — Tom Ron, Apr 12 '20 at 05:58

jezrael · Accepted Answer · 2020-04-12T06:00:35.400

3

I think you are close, only is used DataFrame.join for append to original, DataFrame.pop for extract column, then convert strings to numeric if necessary and last renamed columns names:

Also is necessary string repr of lists to lists by json.loads:

import json

df = (df.join(pd.DataFrame([json.loads(x) for x in df.pop('ids')], index=df.index)
        .astype(float)
        .astype('Int64')
        .rename(columns=lambda x: f'id{x+1}')))
print (df)
   id  partner_id  id1     id2     id3      id4     id5
0   1          12    1       4  187275   187358  946475
1   2          12    1     191   28925    31441     NaN
2   3          16    1       2  293915  1573130  293918
3   4          11    1      13  294064  1238496     NaN
4   5          16    1  153339  155025   155029     NaN

edited Apr 12 '20 at 06:00

answered Apr 12 '20 at 05:57

jezrael

822,522
95
1,334
1,252

1

Thanks for the answer. It is now showing `ValueError: could not convert string to float: '[1, 13, 294266, 294276, 312828, 1007738]'` – A l w a y s S u n n y Apr 12 '20 at 05:59
1

@AlwaysSunny - Can you test `pd.DataFrame([json.loads(x) for x in df.pop('ids')], index=df.index)` ? – jezrael Apr 12 '20 at 06:01

Split pandas list to different column and calculate the counts

1 Answers1