Python/Pandas - Combine multiple columns matching string name as single string col

Question

My data set comes from jira and the Labels data is separated into multiple columns, one for each label in the row. This number of rows can vary from 1 to more than 5 depending on how many label tags were used on a given entry.

The csv could look like:

Issue Type  Issue key   Labels    Labels    Labels    Labels    Labels
Story       123         #label1,  #label2,  #label6,  #label7,  #label9,
Story       124                 
Story       125         #label3,  #label1,          
Bug         126                 
Story       127         #label5,

The number of columns can vary getting a new Labels column for every tag in the row. There seems to be no way to correct the export to enclose the values as a single string.

What I'm needing to do is concatenate these into a single column "Tags", and I don't care about cleaning up the trailing comma.

I've tried

df['Tags'] = [col for col in df.columns if 'Label' in col]

But this throws an error "Length of values does not match length of index"

Is there a simple way to accomplish this when reading the CSV into the dataframe?

kindly share ur expected output. dataframe form

sammywemmy
– sammywemmy

2020-05-05 22:36:50 +00:00
Commented May 5, 2020 at 22:36 — sammywemmy
– sammywemmy, Commented May 5, 2020 at 22:36

Cristian Contrera · Accepted Answer · 2020-05-06 02:59:51Z

1

You can use agg function:

colums = [col for col in df.columns if col.startswith('value')]
df[columns].agg(lambda x : '-'.join(x.astype(str)), axis=1)

edited May 6, 2020 at 2:59

answered May 5, 2020 at 19:23

Cristian Contrera

7233 silver badges18 bronze badges

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

3 Comments

Igor Rivin Over a year ago

Nice, I have never aggregated on axis = 1 :)

J.P. Over a year ago

This throws the error: TypeError: ('sequence item 1: expected str instance, float found', 'occurred at index 0')

Cristian Contrera Over a year ago

@J.P. I edited the answer for not object type columns

Igor Rivin · Accepted Answer · 2020-05-05 22:14:15Z

0

I think what you are really trying to do is:

tags = [col for col in df.columns if 'Label' in col]
nontags = [col for col in df.columns if 'Label' non in col]
tagdf = df[tags]
tagcol = tagdf.apply(" ".join, axis=1)
newdf = pd.concat([df[nontags], tagcol], axis = 1)

edited May 5, 2020 at 22:14

answered May 5, 2020 at 19:24

Igor Rivin

4,8742 gold badges30 silver badges45 bronze badges

2 Comments

J.P. Over a year ago

NameError: name 'join' is not defined - and .join is invalid syntax..

Igor Rivin Over a year ago

@J.P. It was a typo, which you should be able to fix in about 20 seconds.

Collectives™ on Stack Overflow

Python/Pandas - Combine multiple columns matching string name as single string col

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related