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, Nov 20 '19 at 06:12
AttributeError: 'DataFrame' object has no attribute 'explode' — Jack, Nov 20 '19 at 06:19
it's an excel output from my java program. I think it's string — Jack, Nov 20 '19 at 06:21
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/202694/discussion-between-jack-and-jezrael). — Jack, Nov 20 '19 at 06:23

jezrael · Accepted Answer · 2019-11-20T06:46:18.647

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

@Jack - If problem with upgrade, added another solution. – jezrael Nov 20 '19 at 06:43 — jezrael, Nov 20 '19 at 06:43

Python Pandas - convert list into series

1 Answers1