Python combines multiple column in one

Question

I have a pandas dataframe as below:

data = {'A'      :[1,2,3], 
        'B':[2,17,17], 
        'C1'     :["C1",np.nan,np.nan],
       'C2'     :[np.nan,"C2",np.nan]} 

# Create DataFrame 
df = pd.DataFrame(data) 
df


    A   B   C1  C2
0   1   2   C1  NaN
1   2   17  NaN C2
2   3   17  NaN NaN

I want to create a variable "C" based on "C1" and"C2"(there could be "C4", "C5". If any of C's has the value "C"= value from C's(C1, C2, C3....). My output in this case should look like below:

    A   B   C1  C2   C
0   1   2   C1  NaN  C1
1   2   17  NaN C2   C2
2   3   17  NaN NaN  NaN

will the C columns have one value or many? if there is more than one what is your intended behavior? — Umar.H
– Umar.H, Commented Feb 7, 2020 at 22:13

Andy L. · Accepted Answer · 2020-02-07 22:49:54Z

3

Try this

df1 = df.filter(regex='^C\d+')
df['C'] = df1[df1.isin(df1.columns)].bfill(1).iloc[:,0]

Out[117]:
   A   B   C1   C2    C
0  1   2   C1  NaN   C1
1  2  17  NaN   C2   C2
2  3  17  NaN  NaN  NaN

If you want to strictly compare values matching to its own column name, Use eq instead of isin as follows

df['C'] = df1[df1.eq(df1.columns, axis=1)].bfill(1).iloc[:,0]

edited Feb 7, 2020 at 22:49

answered Feb 7, 2020 at 22:40

Andy L.

25.3k4 gold badges20 silver badges30 bronze badges

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

Comments

BENY · Accepted Answer · 2020-02-07 22:58:06Z

1

IIUC

df['C']=df.filter(like='C').bfill(axis=1).iloc[:,0]
df
   A   B   C1   C2    C
0  1   2   C1  NaN   C1
1  2  17  NaN   C2   C2
2  3  17  NaN  NaN  NaN

answered Feb 7, 2020 at 22:58

BENY

324k22 gold badges176 silver badges250 bronze badges

1 Comment

Umar.H Over a year ago

see comment in my post (:

Umar.H · Accepted Answer · 2020-02-07 22:52:42Z

0

IIUC,

we can filter your columns by the word C then aggregate the values with an agg call:

df['C'] = df.filter(regex='C\d+').stack().groupby(level=0).agg(','.join)

print(df)

   A   B   C1   C2    C
0  1   2   C1  NaN   C1
1  2  17  NaN   C2   C2
2  3  17  NaN  NaN  NaN

edited Feb 7, 2020 at 22:52

answered Feb 7, 2020 at 22:15

Umar.H

23.1k7 gold badges50 silver badges94 bronze badges

1 Comment

Shanoo Over a year ago

But if I have a variable say "ABC", the above code will consider that too, which I don't want

Collectives™ on Stack Overflow

Python combines multiple column in one

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related