CODEWITHSUNDEEP
pythonpandasdataframe
Andrew
Andrew

Reputation: 33

Dividing each row by the previous row

I have a dataframe of stock pricings.

Date CSCO INTC MSFT WBA
2003-01-03 10 9 8 6
2003-01-04 9 9 6 4
2003-01-05 8 9 5 3

How do I create a new Dataframe that takes today's price for one stock, and divide it by yesterdays price to return a ratio. Example output

Date CSCO INTC MSFT WBA
2003-01-03 NaN NaN NaN NaN
2003-01-04 0.9 1.0 0.75 0.666
2003-01-05 0.88889 1.0 0.83333 0.75

Upvotes: 3

Views: 483

Answers (2)

Utsav
Utsav

Reputation: 5918

In the solution we are dividing current row by previous row using shift

Code

df.Date = pd.to_datetime(df.Date) # If required
df = df.set_index('Date')
df = (df/df.shift(1)).reset_index()
df

Output

    Date        CSCO        INTC    MSFT        WBA
0   2003-01-03  NaN         NaN     NaN         NaN
1   2003-01-04  0.900000    1.0     0.750000    0.666667
2   2003-01-05  0.888889    1.0     0.833333    0.750000

Upvotes: 1

RavinderSingh13
RavinderSingh13

Reputation: 133458

With your shown samples, please try following. Simple explanation would be, creating 2 variables m1 and m2. Where m1 has everything from CSCO column to rest of the columns with shift shifting rows with 1. In m2 having everything from CSCO column to till last column value, finally dividing them to get values.

m1 = df.loc[:,'CSCO':].shift()
m2 = df.loc[:,'CSCO':]
df.loc[:,'CSCO':] = m2/m1

Output of DataFrame will be as follows:

         Date      CSCO  INTC      MSFT       WBA
0  2003-01-03       NaN   NaN       NaN       NaN
1  2003-01-04  0.900000   1.0  0.750000  0.666667
2  2003-01-05  0.888889   1.0  0.833333  0.750000

Upvotes: 5

Related Questions