Pandas: Merge two rows if row contains certain string

Question

I have a sheet that looks like this.

Fleet Risk Control	Communication	Interpersonal relationships	Demographic	Demographic
Q_21086	Q_21087	Q_21088	AGE	GENDER
1	3	4	27	Male

What I'm trying to achieve is where there is a row with 'Q_' inside of it, merge that cell with the top row and return a new dataframe.

So the existing data above would become something like this:

Fleet Risk Control - Q_21086	Communication - Q_21087	Interpersonal relationships - Q_21088
1	3	4

I honestly have no idea where to even begin with something like this.

Can there only be 1 or 0 rows with "Q_"?

Jason Cook
– Jason Cook

2020-12-17 11:15:41 +00:00
Commented Dec 17, 2020 at 11:15 — Jason Cook
– Jason Cook, Commented Dec 17, 2020 at 11:15

kenny_123 · Accepted Answer · 2020-12-17 11:21:22Z

2

You could try this one. This is for input:

import pandas as pd

df = pd.DataFrame({'Fleet Risk Control': ['Q_21086', 1],
              'Communication': ['Q_21087', 3],
              'Interpersonal relationships': ['Q_21088', 4],
              'Demographic': ['AGE', 27],
              'Demographic 2': ['Gender', 'Male']})

Now concat the header line with the first line of df:

df.columns = df.columns + ' - ' + df.iloc[0, :]

Extract every line without the first and dropping the last both columns

df = df.iloc[1:, :-2]

answered Dec 17, 2020 at 11:21

kenny_123

813 bronze badges

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

Comments

herveRilos · Accepted Answer · 2020-12-17 12:14:47Z

0

# rename columns
df.columns = [x + ' - ' + y if y.startswith('Q_') else x for x, y in zip(df.columns, df.iloc[0])]

#drop not matching columns
to_drop = [c for c, _ in df.iloc[0].apply(lambda x: not x.startswith('Q_')).items() if _]
df.drop(to_drop, axis=1)[1:]

answered Dec 17, 2020 at 12:14

herveRilos

1061 silver badge5 bronze badges

Collectives™ on Stack Overflow

Pandas: Merge two rows if row contains certain string

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