Combining two columns with related data into a single column (python, pandas)

Question

I am looking for the correct logic to combine two columns with related data from an .xlsx file using pandas in python. It is similar to the post: Merge 2 columns in pandas into one columns that have data in python, except that I also want to transform the data as I combine the columns so it's not really a true merge of the two columns. I want to be able to say "if column wbc_na has the value "checked" in row x, place "Not available" in row x under column wbc". Once combined, I want to drop the column" wbc_na" since "wbc" now contains all the information I need. For example:

input:  
ID,wbc, wbc_na  
1,9.0,-  
2,NaN,checked  
3,10.2,-  
4,8.8,-  
5,0,checked

output:

ID,wbc  
1,9.0  
2,Not available  
3,10.2  
4,8.8  
5,Not available

Thanks for your suggestions.

EdChum · Accepted Answer · 2014-06-03 21:53:40Z

2

You can use loc to find where column 'wbc_na' is 'checked' and for those rows assign column 'wbc' value:

In [18]:
df.loc[df['wbc_na'] == 'checked', 'wbc'] = 'Not available'
df
Out[18]:
   ID            wbc   wbc_na
0   1              9      -  
1   2  Not available  checked
2   3           10.2      -  
3   4            8.8      -  
4   5  Not available  checked

[5 rows x 3 columns]
In [19]:
# now drop the extra column
df.drop(labels='wbc_na', axis=1, inplace=True)
df
Out[19]:
   ID            wbc
0   1              9
1   2  Not available
2   3           10.2
3   4            8.8
4   5  Not available

[5 rows x 2 columns]

answered Jun 3, 2014 at 21:53

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

3 Comments

ferrios25 Over a year ago

Thank you, this answer seems to give the result I was looking for. So the loc method is basically returning the row # correct?

EdChum Over a year ago

loc performs label based selection, the online docs are worth looking at to understand the semantic differences between loc, ix and iloc, by the way you should have enough reputation to upvote now ;)

ferrios25 Over a year ago

I was looking at (pandas.pydata.org/pandas-docs/stable/10min.html) but it wasn't giving me enough info about the methods. How exciting that I can upvote things now, I was just waiting to be able to do that. Thanks for your help!

user3649071 · Accepted Answer · 2014-06-04 02:20:08Z

1

You could also a list comprehension to reassign the values in column wbc:

data = pd.DataFrame({'ID': [1,2,3,4,5], 'wbc': [9, np.nan, 10, 8, 0], 'wbc_nan': ['-', 'checked', '-', '-', 'checked']})
data['wbc'] = [(item if data['wbc_nan'][x] != 'checked' else 'Not available') for x, item in enumerate(data['wbc'])]
data = data.drop('wbc_nan', axis=1)

answered Jun 4, 2014 at 2:20

user3649071

661 silver badge7 bronze badges

1 Comment

ferrios25 Over a year ago

Tried this out, it also works with my real data. Thank you for your solution.

Collectives™ on Stack Overflow

Combining two columns with related data into a single column (python, pandas)

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related