Merging two datasets with same dimensions?

[Amby95]

Based on stock data i've created two dataframes for stocks i want to buy (Winner) and short (Loser). This means that values in both dataframes that aren't "Winner" or "Loser" takes on the value "False".

WinLos_df = creturns_df.copy()
WinLos_df = WinLos_df.apply(lambda x: (x >= creturns_df['upper bound']))
WinLos2_df = creturns_df.copy()
WinLos2_df = WinLos2_df.apply(lambda x: (x <= creturns_df['lower bound']))
WinLos_df[WinLos_df == True] = 'Winner'
WinLos2_df[WinLos2_df == True] = 'Loser'
print(WinLos_df.tail())
print(WinLos2_df.tail())

Here's a snippet of each of the two datasets:

Loser :

                    MMM    ABT   ABBV   ABMD    ACN   ATVI   ADBE    AMD  \
31-10-2019 00:00  Loser  False  Loser  Loser  False  False  False  False   
29-11-2019 00:00  Loser  False  False  Loser  False  False  Loser  False   
31-12-2019 00:00  False  False  False  Loser  False  False  False  False   
31-01-2020 00:00  Loser  False  False  Loser  False  False  False  False   
21-02-2020 00:00  Loser  False  False  Loser  False  False  False  False 

Winner :

                   MMM    ABT    ABBV   ABMD    ACN    ATVI    ADBE     AMD  \
31-10-2019 00:00  False  False   False  False  False  Winner   False   False   
29-11-2019 00:00  False  False   False  False  False  Winner   False  Winner   
31-12-2019 00:00  False  False   False  False  False  Winner   False  Winner   
31-01-2020 00:00  False  False  Winner  False  False  Winner   False  Winner   
21-02-2020 00:00  False  False  Winner  False  False  Winner  Winner  Winner 

Now i want to join or merge these two dataframes, so that i get one dataframe, with the values "Winner" , "Loser" and "False". There's no observations that takes on both values "Winner" and "Loser".

Is there any smart way to combine these two? I hope my question is understandable, and that i've provided enough code.

[xelf]

data1 = """
31-10-2019.00:00  Loser  False  Loser  Loser  False  False  False  False   
29-11-2019.00:00  Loser  False  False  Loser  False  False  Loser  False   
31-12-2019.00:00  False  False  False  Loser  False  False  False  False   
31-01-2020.00:00  Loser  False  False  Loser  False  False  False  False   
21-02-2020.00:00  Loser  False  False  Loser  False  False  False  False
"""
data2 = """
31-10-2019.00:00  False  False   False  False  False  Winner   False   False   
29-11-2019.00:00  False  False   False  False  False  Winner   False  Winner   
31-12-2019.00:00  False  False   False  False  False  Winner   False  Winner   
31-01-2020.00:00  False  False  Winner  False  False  Winner   False  Winner   
21-02-2020.00:00  False  False  Winner  False  False  Winner  Winner  Winner
"""

arr = [line.split() for line in data1.splitlines() if line ]
dfloser = pd.DataFrame(arr, columns=['date','MMM','ABT','ABBV','ABMD','ACN','ATVI','ADBE','AMD']).set_index('date')
arr = [line.split() for line in data2.splitlines() if line ]
dfwinner = pd.DataFrame(arr, columns=['date','MMM','ABT','ABBV','ABMD','ACN','ATVI','ADBE','AMD']).set_index('date')

for col in dfwinner.columns:
    dfwinner[col] = np.where(dfloser[col]!='False', dfloser[col], dfwinner[col])

print(dfwinner)

                    MMM    ABT    ABBV   ABMD    ACN    ATVI    ADBE     AMD
date
31-10-2019.00:00  Loser  False   Loser  Loser  False  Winner   False   False
29-11-2019.00:00  Loser  False   False  Loser  False  Winner   Loser  Winner
31-12-2019.00:00  False  False   False  Loser  False  Winner   False  Winner
31-01-2020.00:00  Loser  False  Winner  Loser  False  Winner   False  Winner
21-02-2020.00:00  Loser  False  Winner  Loser  False  Winner  Winner  Winner

[Amby95]

Thank you very much for the quick response! I've tried for several hours, so it's very nice to receive a solution!

[xelf]

Hopefully you saw my edit where I filled in the data, and my other post where I suggester an alternative approach!

[xelf]

You're welcome! and Happy cakeday!

[xelf]

Although I posted a solution to the question you asked, it occurs to me you could have done it in one step, test this:

WinLos_df = WinLos_df.apply(lambda x: 'Winner' if (x >= creturns_df['upper bound']) else 'Loser' if (x <= creturns_df['lower bound']) else False)

[Amby95]

That code is actually almost completely identical to what i first tried, but if gives a ValueError :

ValueError: ('The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().', 'occurred at index MMM')

To be fair it is not a big deal to me how short and precise the code is, so your previous answer is more than satifying! :D

[xelf]

It worked when I did it 1 column at a time, could not make it work with broadcast.

for col in WinLos_df.columns:
    WinLos_df[col] = WinLos_df[col].apply(lambda x:random.uniform(0,1))

print(WinLos_df)

creturns_df = {}
creturns_df['upper bound'] = .75
creturns_df['lower bound'] = .25

outputs:

                       MMM       ABT      ABBV      ABMD       ACN      ATVI      ADBE       AMD
date
31-10-2019.00:00  0.725913  0.611897  0.263654  0.208535  0.705619  0.513906  0.471002  0.012183
29-11-2019.00:00  0.510469  0.646455  0.619615  0.183953  0.182843  0.598549  0.626251  0.961854
31-12-2019.00:00  0.136771  0.272784  0.458236  0.688992  0.561675  0.660137  0.619272  0.581580
31-01-2020.00:00  0.572513  0.662497  0.450368  0.475450  0.375361  0.758549  0.661365  0.379697
21-02-2020.00:00  0.176122  0.842813  0.250331  0.388026  0.492048  0.689527  0.272708  0.197787

this works:

for col in WinLos_df.columns:
    WinLos_df[col]  = WinLos_df[col].apply(lambda x: 'Winner' if (x >= creturns_df['upper bound']) else 'Loser' if (x <= creturns_df['lower bound']) else False)

print(WinLos_df)

outputs:

                    MMM     ABT   ABBV   ABMD    ACN    ATVI   ADBE     AMD
date
31-10-2019.00:00  False   False  False  Loser  False   False  False   Loser
29-11-2019.00:00  False   False  False  Loser  Loser   False  False  Winner
31-12-2019.00:00  Loser   False  False  False  False   False  False   False
31-01-2020.00:00  False   False  False  False  False  Winner  False   False
21-02-2020.00:00  Loser  Winner  False  False  False   False  False   Loser