Dynamically merge lines that share the same key into one

Question

I have a Dataframe. I would like to make another column that combines columns whose name starts with the same value in Answer and QID.

That is, with this Dataframe exercise:

    QID     Category    Text    QType   Question    Answer0     Answer1
0   16  Automotive  Access to car   Single  Do you have access to a car?    I own a car/cars    I own a car/cars
1   16  Automotive  Access to car   Single  Do you have access to a car?    I lease/ have a company car     I lease/have a company car
2   16  Automotive  Access to car   Single  Do you have access to a car?    I have access to a car/cars     I have access to a car/cars
3   16  Automotive  Access to car   Single  Do you have access to a car?    No, I don’t have access to a car/cars   No, I don't have access to a car
4   16  Automotive  Access to car   Single  Do you have access to a car?    Prefer not to say   Prefer not to say
5   17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    Audi    Audi
6   17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    Alfa Romeo  Alfa Romeo
7   17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    BMW     BMW
8   17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    Cadillac    Cadillac
9   17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    Chevrolet   Chevrolet
10  17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    Chrysler    Chrysler
11  17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    Citroen     Citroen
12  17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    Daihatsu    Daihatsu
13  17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    Fiat    Fiat
14  17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    Ford    Ford
15  17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    Honda   Honda
16  17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    Hyundai     Hyundai
...

And I’d like to get something like:

    QID     Category    Text    QType   Question    Answer0     Answer1     Answer3     Answer4     Answer5     Answer6     Answer7     Answer8     Answer9     Answer10    Answer11     Answer12     ...      
4   16  Automotive  Access to car   Single  Do you have access to a car?    I own a car/cars    I lease/ have a company car     I have access to a car/cars     No, I don’t have access to a car/cars   Prefer not to say       
5   17  Automotive  Make of car/cars    Multiple    If you own/lease a car(s), which brand are they?    Audi    Alfa Romeo  BMW     Cadillac    Chevrolet   Chrysler    Citroen     ...

I can arrange a given/static number of columns whose name starts with the same value in Reply and QID:

df = pd.DataFrame('path/to/file')

# lazy - want first of all attributes except QID and Answer columns
agg = {col:"first" for col in list(df.columns) if col!="QID" and "Answer" not in col}
# get a list of all answers in Answer0 for a QID
agg = {**agg, **{"Answer0":lambda s: list(s)}}

# helper function for row call.  not needed but makes more readable
def ans(r, i):
    return "" if i>=len(r["AnswerT"]) else r["AnswerT"][i]

# split list from aggregation back out into columns using assign
# rename Answer0 to AnserT from aggregation so that it can be referred to.  
# AnswerT drop it when don't want it any more
dfgrouped = df.groupby("QID").agg(agg).reset_index().rename(columns={"Answer0":"AnswerT"}).assign(
    Answer0=lambda dfa: dfa.apply(lambda r: ans(r, 0), axis=1),
    Answer1=lambda dfa: dfa.apply(lambda r: ans(r, 1), axis=1),
    Answer2=lambda dfa: dfa.apply(lambda r: ans(r, 2), axis=1),
    Answer3=lambda dfa: dfa.apply(lambda r: ans(r, 3), axis=1),
    Answer4=lambda dfa: dfa.apply(lambda r: ans(r, 4), axis=1),
    Answer5=lambda dfa: dfa.apply(lambda r: ans(r, 5), axis=1),
    Answer6=lambda dfa: dfa.apply(lambda r: ans(r, 6), axis=1),
).drop("AnswerT", axis=1)

print(dfgrouped.to_string(index=False))

How can I arrange a dynamical number of columns whose name starts with the same value in Answer and QID?

Answer 1

Opa,

I believe that with the example below you can unroll.

Specifically answering part of your question

How can I combine a dynamical number of columns whose name starts with the same value in Answer and QID?

The difficulty is a dynamic number of columns, right?

So see below:

>>> df = pd.DataFrame({"A": [1,2,3,4], "B": [[1,2,3], ["A","B","C","D"], ["Z"], [8,8,8]]})

>>> df

   A             B
0  1     [1, 2, 3]
1  2  [A, B, C, D]
2  3           [Z]
3  4     [8, 8, 8]

>>> new_df = pd.DataFrame(df["B"].tolist(), index=df["A"])

>>> new_df

   0     1     2     3
A
1  1     2     3  None
2  A     B     C     D
3  Z  None  None  None
4  8     8     8  None

In your case, create the lists with the answers and then associate to a column of your dataframe.

I hope it helps.