NaNs when subtracting the same column dataframe

user_355048 0
Viewed 67

I have a dataframe like this:

Date,Close
1987-01-02,2540.100098
1987-01-05,2552.399902
1987-01-06,2583.899902
1987-01-07,2607.100098
1987-01-08,2603.300049

I'm trying to subtracting each row to all other rows but the result returns all NaN and I don't know why:

import pandas as pd
import numpy as np
df = pd.read_csv('test.csv')
df1 = pd.DataFrame(np.subtract(df['Close'], df['Close']), df.Date, df.Date)
print(df1)

Result:

Date       1987-01-02 1987-01-05 1987-01-06 1987-01-07 1987-01-08
Date
1987-01-02        NaN        NaN        NaN        NaN        NaN
1987-01-05        NaN        NaN        NaN        NaN        NaN
1987-01-06        NaN        NaN        NaN        NaN        NaN
1987-01-07        NaN        NaN        NaN        NaN        NaN
1987-01-08        NaN        NaN        NaN        NaN        NaN
pandas dataframe numpy python
1 Answers

You can use broadcasting:

df1 = pd.DataFrame(np.subtract(df['Close'].to_numpy(), df['Close'].to_numpy()[:, None]), df.Date, df.Date)
print(df1)
Date        1987-01-02  1987-01-05  1987-01-06  1987-01-07  1987-01-08
Date                                                                  
1987-01-02    0.000000   12.299804   43.799804   67.000000   63.199951
1987-01-05  -12.299804    0.000000   31.500000   54.700196   50.900147
1987-01-06  -43.799804  -31.500000    0.000000   23.200196   19.400147
1987-01-07  -67.000000  -54.700196  -23.200196    0.000000   -3.800049
1987-01-08  -63.199951  -50.900147  -19.400147    3.800049    0.000000

Or subtract.outer:

df1 = pd.DataFrame(np.subtract.outer(df['Close'].to_numpy(), df['Close'].to_numpy()), df.Date, df.Date)
print(df1)
Date        1987-01-02  1987-01-05  1987-01-06  1987-01-07  1987-01-08
Date                                                                  
1987-01-02    0.000000  -12.299804  -43.799804  -67.000000  -63.199951
1987-01-05   12.299804    0.000000  -31.500000  -54.700196  -50.900147
1987-01-06   43.799804   31.500000    0.000000  -23.200196  -19.400147
1987-01-07   67.000000   54.700196   23.200196    0.000000    3.800049
1987-01-08   63.199951   50.900147   19.400147   -3.800049    0.000000
user_29010020
Related