CODEWITHSUNDEEP
pythonpandasnumpy
user27976
user27976

Reputation: 903

looping through multiple values in a column

Can someone please advise me on how to loop through multiple values in a data frame column.

Example:

col1    col2
High street qwe.723,qwe.2,qwe.17,qwe.1000,qwe.23
Must street qwe.34,qwe.17,qwe.1000,qwe.23

I want to have the following output:

High street
qwe.723
High street
qwe.2
High street
qwe.17
High street
qwe.1000
High street
qwe.23

Must street
qwe.34
Must street
qwe.17
Must street
qwe.1000
Must street
qwe.23

My attempt:

lines = open('file.txt','r')
for line in lines:
    line=line.strip().split('\t')
    vals=line[1].split(',')
    for val in vals:
        print(line[0],'\n',val)

Upvotes: 3

Views: 201

Answers (3)

piRSquared
piRSquared

Reputation: 294338

Because I was playing around having fun with cytoolz and numpy
Super Fast!

import cytoolz

c2 = np.core.defchararray.split(df.col2.values.astype('str'), ',')
col1 = df.col1.values.repeat([len(c) for c in c2.tolist()])
col2 = list(cytoolz.concat(c2))
np.stack([col1, col2]).ravel('F')

array(['High street', 'qwe.723', 'High street', 'qwe.2', 'High street',
       'qwe.17', 'High street', 'qwe.1000', 'High street', 'qwe.23',
       'Must street', 'qwe.34', 'Must street', 'qwe.17', 'Must street',
       'qwe.1000', 'Must street', 'qwe.23'], dtype=object)

time testing

enter image description here

Upvotes: 3

Zeugma
Zeugma

Reputation: 32095

Another one:

(df.set_index('col1')
   .col2.str.split(',', expand=True)
   .stack()
   .reset_index(level=-1, drop=True)
   .to_csv('output.txt',sep='\n')

Upvotes: 4

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210852

Try this:

In [136]: df
Out[136]:
          col1                                  col2
0  High street  qwe.723,qwe.2,qwe.17,qwe.1000,qwe.23
1  Must street         qwe.34,qwe.17,qwe.1000,qwe.23

In [137]: df.set_index('col1').col2.str.split(',', expand=True).stack().reset_index(level=1, drop=1).to_frame('col2').reset_index().stack()
     ...:
Out[137]:
0  col1    High street
   col2        qwe.723
1  col1    High street
   col2          qwe.2
2  col1    High street
   col2         qwe.17
3  col1    High street
   col2       qwe.1000
4  col1    High street
   col2         qwe.23
5  col1    Must street
   col2         qwe.34
6  col1    Must street
   col2         qwe.17
7  col1    Must street
   col2       qwe.1000
8  col1    Must street
   col2         qwe.23
dtype: object

I'm sure there must be a better way to do this...

Upvotes: 4

Related Questions