How to extract values from a column of list string pandas dataframe

Question

I want to extract the values from the list so that I can perform some pandas operation on the string.

Distance                           TGR Grade                  TGR1
[342m, 342m, 530m, 342m]         [M, M, RW, RW]            [1, 1, 7, 1]
[390m, 390m, 390m, 390m,450]    [M, 7, 6G, X45, X67]       [1, 2, 4, 5, 5]
[]                                    []                      []

I need a clean df of this form.

    Distance                     TGRGrade             TGR1
342m,342m,530m,342m          M,M,RW,RW            1,1,7,1
390m,390m,390m,390m,450      M,7,6G,X45,X67       1,2,4,5,5

I have tried the below functions:

df.columns = [''.join(i.split()) for i in df.columns]
df = df.applymap(lambda x: ''.join(x.strip('\[').strip('\]').split()))

and

df = df.replace('[', '')
df = df.replace(']', '')

My first attempt lead to this error.

AttributeError: 'list' object has no attribute 'strip'

checking the values in the individual column resulted in this.

df['TGR1']

    0           [1, 1, 7, 1, 1, 8, 8, 1, 1, 8]
    1           [1, 2, 4, 5, 5, 1, 2, 7, 6, 8]
    2     [6, 1, 4, 4, 7, 1, 7, 1, 8, 3, 4, 5]
    3     [1, 7, 4, 4, 3, 2, 1, 1, 2, 2, 2, 1]
    4     [3, 4, 5, 2, 1, 8, 5, 2, 3, 6, 5, 3]

Your data is list, not string: df.applymap(lamba x: ','.join(map(str,x))) — Quang Hoang
– Quang Hoang, Commented Oct 2, 2021 at 8:37

Daniel Lenz · Accepted Answer · 2021-10-02 08:36:51Z

2

You should check out pandas.explode:

df.explode(['Distance', 'TGR Grade', 'TGR1'])

answered Oct 2, 2021 at 8:36

Daniel Lenz

3,9371 gold badge22 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Muhammad Hassan · Accepted Answer · 2021-10-02 09:03:48Z

1

Try:

df = df.apply(lambda x: x.apply(lambda x: ','.join(map(str, x))))

OUTPUT:

                  Distance       TGR Grade       TGR1
0      342m,342m,530m,342m       M,M,RW,RW    1,1,7,1
1  390m,390m,390m,390m,450  M,7,6G,X45,X67  1,2,4,5,5
2

edited Oct 2, 2021 at 9:03

answered Oct 2, 2021 at 8:55

Muhammad Hassan

4,2492 gold badges16 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to extract values from a column of list string pandas dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related