CODEWITHSUNDEEP
pythonpython-3.xpandaspandas-groupby
Piyush S. Wanare
Piyush S. Wanare

Reputation: 4933

Pandas combine two columns

I have following database:

df = pandas.DataFrame({'Buy':[10,np.nan,2,np.nan,np.nan,4],'Sell':[np.nan,7,np.nan,9,np.nan,np.nan]})

Out[37]: 
    Buy  Sell
0  10.0   NaN
1   NaN   7.0
2   2.0   NaN
3   NaN   9.0
4   NaN   NaN
5   4.0   NaN

I want o create two more columns called Quant and B/S

for Quant it is working fine as follows:

df['Quant'] = df['Buy'].fillna(df['Sell']) # Fetch available value from both column and if both values are Nan then output is Nan.

Output is:

df
Out[39]: 
    Buy  Sell  Quant
0  10.0   NaN   10.0
1   NaN   7.0    7.0
2   2.0   NaN    2.0
3   NaN   9.0    9.0
4   NaN   NaN    NaN
5   4.0   NaN    4.0

But I want to create B/S on the basis of "from which column they have taken value while creating Quant"

Upvotes: 1

Views: 588

Answers (1)

jpp
jpp

Reputation: 164673

You can perform an equality test and feed into numpy.where:

df['B/S'] = np.where(df['Quant'] == df['Buy'], 'B', 'S')

For the case where both values are null, you can use an additional step:

df.loc[df[['Buy', 'Sell']].isnull().all(1), 'B/S'] = np.nan

Example

from io import StringIO
import pandas as pd

mystr = StringIO("""Buy    Sell
10      nan
nan      8
4       nan
nan      5
nan      7
3       nan
2       nan
nan     nan""")

df = pd.read_csv(mystr, delim_whitespace=True)

df['Quant'] = df['Buy'].fillna(df['Sell'])
df['B/S'] = np.where(df['Quant'] == df['Buy'], 'B', 'S')
df.loc[df[['Buy', 'Sell']].isnull().all(1), 'B/S'] = np.nan

Result

print(df)

    Buy  Sell  Quant  B/S
0  10.0   NaN   10.0    B
1   NaN   8.0    8.0    S
2   4.0   NaN    4.0    B
3   NaN   5.0    5.0    S
4   NaN   7.0    7.0    S
5   3.0   NaN    3.0    B
6   2.0   NaN    2.0    B
7   NaN   NaN    NaN  NaN

Upvotes: 2

Related Questions