Python Pandas - convert list into series

Question

I have an excel data set looks like this:

for copy purpose:

ID  buffer
LocalHub@3c183d50   [intraCity_Simulator.Parcel@55078545, intraCity_Simulator.Parcel@75b895dd, intraCity_Simulator.Parcel@44227899, intraCity_Simulator.Parcel@696b0129, intraCity_Simulator.Parcel@86ec871, intraCity_Simulator.Parcel@7a0d8542, intraCity_Simulator.Parcel@67a58fba]
LocalHub@d3a0fbe    [intraCity_Simulator.Parcel@61b9a28c, intraCity_Simulator.Parcel@1b5d2e8b, intraCity_Simulator.Parcel@65911201, intraCity_Simulator.Parcel@2e53ab95, intraCity_Simulator.Parcel@464b73fa, intraCity_Simulator.Parcel@640ff28a, intraCity_Simulator.Parcel@77fc8d6c, intraCity_Simulator.Parcel@609051b0, intraCity_Simulator.Parcel@25e0c299, intraCity_Simulator.Parcel@436af74b, intraCity_Simulator.Parcel@24c3fb2, intraCity_Simulator.Parcel@130592c8, intraCity_Simulator.Parcel@444d20b1, intraCity_Simulator.Parcel@6d59d5b2, intraCity_Simulator.Parcel@764a25d3, intraCity_Simulator.Parcel@4bdd2c62]

I would like to re-arrange and display the list value as a column corresponding to the ID, e.g.

ID                        buffer
LocalHub@3c183d50       intraCity_Simulator.Parcel@55078545
LocalHub@3c183d50       intraCity_Simulator.Parcel@75b895dd
...                     ...

why is it a duplicated question? is there an existing answer for this? — Jack
– Jack, Commented Nov 20, 2019 at 6:12
AttributeError: 'DataFrame' object has no attribute 'explode' — Jack
– Jack, Commented Nov 20, 2019 at 6:19
it's an excel output from my java program. I think it's string — Jack
– Jack, Commented Nov 20, 2019 at 6:21

jezrael · Accepted Answer · 2019-11-20 06:46:18Z

Solution for pandas 0.25+ is remove [] by Series.str.strip with Series.str.split values for lists and then DataFrame.explode, last DataFrame.reset_index with drop=True is for default RangeIndex:

df = (df.assign(buffer = df['buffer'].str.strip('[]').str.split(','))
        .explode('buffer')
        .reset_index(drop=True))
print (df)

                   ID                                buffer
0   LocalHub@3c183d50   intraCity_Simulator.Parcel@55078545
1   LocalHub@3c183d50   intraCity_Simulator.Parcel@75b895dd
2   LocalHub@3c183d50   intraCity_Simulator.Parcel@44227899
3   LocalHub@3c183d50   intraCity_Simulator.Parcel@696b0129
4   LocalHub@3c183d50    intraCity_Simulator.Parcel@86ec871
5   LocalHub@3c183d50   intraCity_Simulator.Parcel@7a0d8542
6   LocalHub@3c183d50   intraCity_Simulator.Parcel@67a58fba
7    LocalHub@d3a0fbe    inraCity_Simulator.Parcel@61b9a28c
8    LocalHub@d3a0fbe   intraCity_Simulator.Parcel@1b5d2e8b
9    LocalHub@d3a0fbe   intraCity_Simulator.Parcel@65911201
10   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@2e53ab95
11   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@464b73fa
12   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@640ff28a
13   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@77fc8d6c
14   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@609051b0
15   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@25e0c299
16   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@436af74b
17   LocalHub@d3a0fbe    intraCity_Simulator.Parcel@24c3fb2
18   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@130592c8
19   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@444d20b1
20   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@6d59d5b2
21   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@764a25d3
22   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@4bdd2c62

Solution for below pandas versions is use repeat by lengths of lists by Series.str.len:

from itertools import chain

splitted = df['buffer'].str.strip('[]').str.split(',')
df = pd.DataFrame({
    'ID' : df['ID'].values.repeat(splitted.str.len()),
    'buffer' : list(chain.from_iterable(splitted.tolist()))
})

print (df)
                   ID                                buffer
0   LocalHub@3c183d50   intraCity_Simulator.Parcel@55078545
1   LocalHub@3c183d50   intraCity_Simulator.Parcel@75b895dd
2   LocalHub@3c183d50   intraCity_Simulator.Parcel@44227899
3   LocalHub@3c183d50   intraCity_Simulator.Parcel@696b0129
4   LocalHub@3c183d50    intraCity_Simulator.Parcel@86ec871
5   LocalHub@3c183d50   intraCity_Simulator.Parcel@7a0d8542
6   LocalHub@3c183d50   intraCity_Simulator.Parcel@67a58fba
7    LocalHub@d3a0fbe    inraCity_Simulator.Parcel@61b9a28c
8    LocalHub@d3a0fbe   intraCity_Simulator.Parcel@1b5d2e8b
9    LocalHub@d3a0fbe   intraCity_Simulator.Parcel@65911201
10   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@2e53ab95
11   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@464b73fa
12   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@640ff28a
13   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@77fc8d6c
14   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@609051b0
15   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@25e0c299
16   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@436af74b
17   LocalHub@d3a0fbe    intraCity_Simulator.Parcel@24c3fb2
18   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@130592c8
19   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@444d20b1
20   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@6d59d5b2
21   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@764a25d3
22   LocalHub@d3a0fbe   intraCity_Simulator.Parcel@4bdd2c62

Collectives™ on Stack Overflow

Python Pandas - convert list into series

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related