CODEWITHSUNDEEP
pythonpandas
Vero
Vero

Reputation: 459

Get the column index as a value when value exists

I need do create some additional columns to my table or separate table based on following:

I have a table

enter image description here

and I need to create additional columns where the column indexes (names of columns) will be inserted as values. Like this:

enter image description here

How to do it in pandas? Any ideas? Thank you

Upvotes: 1

Views: 305

Answers (1)

jezrael
jezrael

Reputation: 862641

If need matched columns only for 1 values:

df = (df.set_index('name')
        .eq(1)
        .dot(df.columns[1:].astype(str) + ',')
        .str.rstrip(',')
        .str.split(',', expand=True)
        .add_prefix('c')
        .reset_index())
print (df)

Explanation:

Idea is create boolean mask with True for values which are replaced by columns names - so compare by DataFrame.eq by 1 and used matrix multiplication by DataFrame.dot by all columns without first with added separator. Then remove last traling separator by Series.str.rstrip and use Series.str.split for new column, changed columns names by DataFrame.add_prefix.

Another solution:

df1 = df.set_index('name').eq(1).apply(lambda x: x.index[x].tolist(), 1)
df = pd.DataFrame(df1.values.tolist(), index=df1.index).add_prefix('c').reset_index()

Upvotes: 3

Related Questions