CODEWITHSUNDEEP
pythonpandasnumpy
Lynn
Lynn

Reputation: 4398

Match certain column values with other multiple

I have a data frame, df, where I would like to take certain values from multiple columns and append them to other columns given certain criteria.

Data

id  value type_a         pos date   stat    type_b       id   date2
aaa 10    aaa_q2.25_1    30  q1.22  aaa               
aaa 20    aaa_q3.25_2    30  q1.22  aaa               
aaa 500   aaa_q1.22_3    30  q1.22  aaa     aaa_q1.22_3  aaa
bbb 20    bbb_q1.22_1    20  q1.22  bbb     bbb_q1.22_1  bbb
bbb 10    bbb_q3.25_4    20  q2.22  bbb               
aaa 5     aaa_q2.22_3    30  q2.22  aaa     aaa_q2.22_3  aaa
ccc 15    ccc_q3.22_1    50  q3.22  ccc     ccc_q3.22_1  ccc  
                                    ccc     ccc_q4.26_2  ccc q1.22            
                                    aaa     ccc_q2.22_2  aaa q1.22
                                    ccc     ccc_q2.22_3  ccc q1.22

Desired

Logic, for the empty column spaces of 'id', 'typea' and 'date', take the values from the columns, 'stat', 'typeb' and 'date2' and apply them to 'id', 'typea', and 'date'

id  value type_a         pos date   stat    type_b       id    date2
aaa 10    aaa_q2.25_1    30  q1.22  aaa               
aaa 20    aaa_q3.25_2    30  q1.22  aaa               
aaa 500   aaa_q1.22_3    30  q1.22  aaa     aaa_q1.22_3  aaa
bbb 20    bbb_q1.22_1    20  q1.22  bbb     bbb_q1.22_1  bbb
bbb 10    bbb_q3.25_4    20  q2.22  bbb              
aaa 5     aaa_q2.22_3    30  q2.22  aaa     aaa_q2.22_3  aaa
ccc 15    ccc_q3.22_1    50  q3.22  ccc     ccc_q3.22_1  ccc
ccc       ccc_q4.26_2    50  q1.22  ccc     ccc_q4.26_2  ccc  q1.22           
aaa       aaa_q2.22_2    30  q1.22  aaa     aaa_q2.22_2  aaa  q1.22
ccc       ccc_q2.22_3    50  q1.22  ccc     ccc_q2.22_3  ccc  q1.22

Doing

A SO member has pointed me in the right direction and the code works well, however, I am wanting to manipulate more than one column on this particular problem.

df['id'] = df['id'].fillna(df['stat'].str[:3])
df['typea'] = df['typea'].fillna(df['typeb'].str[:11])
df['date'] = df['date'].fillna(df['date2'].str[:5])

Any suggestion is appreciated

Upvotes: 0

Views: 44

Answers (1)

Anurag Dabas
Anurag Dabas

Reputation: 24314

Try via fillna():

df.iloc[:,0]=df.iloc[:,0].fillna(df['stat'])
#you have to use iloc since you have 2 columns of name 'id'
df['type_a']=df['type_a'].fillna(df['type_b'])
df['date']=df['date'].fillna(df['date2'])

Upvotes: 1

Related Questions