CODEWITHSUNDEEP
pythonpandas
Blackdynomite
Blackdynomite

Reputation: 431

Combine pandas data column into a single column

I have 3 columns of ID's that I want to combine into a single column like the example below. The goal here is to simply replace all 0's in the main column with the values in either ID1 or ID2 AND maintain the score column to the far right.

Note, The Main ID column also has cases where there is already a value as shown in row 3, in that case, nothing needs to be done. Ultimately trying to get a single column as shown in the desired output. Tried using some iterative loop but it was not a pythonic approach.

Data Table

Main ID ID_1    ID_2   SCORE
0         0    121231   212 
0       54453   0       199
12123   12123   0       185
343434    0    343434   34
2121      0     0       66
0         0     11      565

Desired output:

MAIN ID  SCORE
121231    212 
54453     199
12123     185
343434    34
2121      66
11        565

Update, applying the bfill method changed all the 'MAIN_ID' numbers into scientific notation like: 3.43559e+06

Upvotes: 0

Views: 60

Answers (2)

supe345
supe345

Reputation: 141

This one works for me, It's simple but functional :D

import pandas as pd

d = {'MAIN ID' : [0,0,12123,343434,2121,0], 'ID_1': [0,54453,12123,0,0,0],'ID_2':[121231,0,0,343434,0,11]}
df = pd.DataFrame(data=d)

for i in range(len(df)):
    if df.iloc[i]['MAIN ID'] == 0:
        if df.iloc[i]['ID_1'] != 0:
            df.iloc[i]['MAIN ID'] = df.iloc[i]['ID_1']
        else:
            df.iloc[i]['MAIN ID'] = df.iloc[i]['ID_2']

df = df.drop(['ID_1', 'ID_2'], axis=1)

Upvotes: 1

BENY
BENY

Reputation: 323316

Try bfill with mask

out = df.mask(df.eq(0)).bfill(1)[['Main ID']]

Upvotes: 1

Related Questions