CODEWITHSUNDEEP
pythonpandasdataframe
Lost1
Lost1

Reputation: 1062

Reshaping a column based on another column in a pandas dataframe

Date          Sin      Ret
01/01/1990    True    0.03  
01/02/1990    True    0.02
01/01/1990    False   0.01  
01/02/1990    False   0.05

I would like 

Date          Ret1    Ret2
01/01/1990    0.03    0.01
01/02/1990    0.02    0.05

so that I can get 

Date          Ret1-Ret2
01/01/1990    0.02
01/02/1990    -0.03

What is the best way to do this? I was thinking setting Date and sin as index and unstack sin. Is there an easier way?

Upvotes: 0

Views: 309

Answers (3)

BENY
BENY

Reputation: 323236

My solution only target the final output. 

df.Sin=df.Sin.astype(int).replace({0:-1})
df.Ret=df.Sin.mul(df.Ret)
df.groupby('Date')['Ret'].agg({'Ret1-Ret2':'sum'})
            Ret1-Ret2
Date                 
01/01/1990       0.02
01/02/1990      -0.03

Upvotes: 1

cs95
cs95

Reputation: 402493

Here's a solution with df.set_index and df.unstack:

In [516]: df.set_index(['Date', 'Sin']).unstack(0).diff().iloc[-1]
Out[516]: 
     Date      
Ret  01/01/1990    0.02
     01/02/1990   -0.03
Name: True, dtype: float64

Upvotes: 2

user2285236
user2285236

Reputation:

I think setting index and stacking is a good idea but here is an alternative with pivot:

(df.pivot(index='Date', columns='Sin', values='Ret')
   .rename(columns={True: 'Ret1', False: 'Ret2'}))

Sin         Ret1  Ret2
Date                  
01/01/1990  0.03  0.01
01/02/1990  0.02  0.05

Upvotes: 1

Related Questions