Python Django: Merge Dataframe performing Sum on overlapping columns

Question

I want to merge two DataFrames with exact the same column names. The overlapping columns can be added togheter. I'm having a bit of troubles because the grouping should be happening on the "index" called "Date" but I can't this index through using the 'Date' name.

Actually, I just need the index (Date) and the sum of all the stocks their 'Adj Close'.

I tried:

data.join(temp, how='outer')

Returns: "ValueError: columns overlap but no suffix specified: Index(['Open', 'High', 'Low', 'Close', 'Adj Close', 'Volume'], dtype='object')"

data = pd.concat([data, temp]).groupby([data.index, temp.index], as_index=True).sum(axis=1)

Returns: "Grouper and axis must be same length

data = pd.merge(data, temp, left_index=True, right_index=True)['Adj Close'].sum(axis=1, skipna=True).astype(np.int64)

Returns: "KeyError: 'Adj Close'"

Code

def overview(request):
    stocks = Stock.objects.all()
    data = None

    for stock in stocks:
        if data is None:
            data = yf.download(stock.ticker, start=stock.trade_date, period="ytd")
        else:
            temp = yf.download(stock.ticker, start=stock.trade_date, period="ytd")
            data.join(temp, how='outer')

DataFrame Output 1

[*********************100%***********************]  1 of 1 completed
                  Open        High  ...   Adj Close     Volume
Date                                ...                       
2019-09-19   55.502499   55.939999  ...   54.697304   88242400
2019-09-20   55.345001   55.639999  ...   53.897728  221652400
2019-09-23   54.737499   54.959999  ...   54.142803   76662000
2019-09-24   55.257500   55.622501  ...   53.885353  124763200
2019-09-25   54.637501   55.375000  ...   54.714626   87613600
...                ...         ...  ...         ...        ...
2020-09-10  120.360001  120.500000  ...  113.489998  182274400
2020-09-11  114.570000  115.230003  ...  112.000000  180860300
2020-09-14  114.720001  115.930000  ...  115.360001  140150100
2020-09-15  118.330002  118.830002  ...  115.540001  184642000
2020-09-16  115.230003  116.000000  ...  112.129997  154679000

[251 rows x 6 columns]

Dataframe Output 2

[*********************100%***********************]  1 of 1 completed
                   Open         High  ...    Adj Close   Volume
Date                                  ...                      
2020-09-03  1699.520020  1700.000000  ...  1629.510010  3186300
2020-09-04  1609.000000  1634.989990  ...  1581.209961  2792500
2020-09-08  1525.000000  1555.550049  ...  1523.599976  2701600
2020-09-09  1548.900024  1558.719971  ...  1547.229980  1962100
2020-09-10  1550.180054  1573.660034  ...  1526.050049  1651200
2020-09-11  1528.150024  1538.699951  ...  1515.760010  1535300
2020-09-14  1531.650024  1557.000000  ...  1508.829956  2133000
2020-09-15  1527.890015  1550.989990  ...  1535.119995  1152100
2020-09-16  1542.479980  1554.369995  ...  1512.089966  1106400

user_11557974 · Answer

Let say you have 2 df like this:

df1 = pd.DataFrame({'Adj Close':[1, 2]}, index=['2019-09-19','2019-09-20'])
df2 = pd.DataFrame({'Adj Close':[3, 4, 5]}, index=['2019-09-19','2019-09-20','2019-09-21'])

df1

            Adj Close
2019-09-19          1
2019-09-20          2

df2

            Adj Close
2019-09-19          3
2019-09-20          4
2019-09-21          5

Then you can concat into one df:

df = pd.concat([df1, df2])


            Adj Close
2019-09-19          1
2019-09-20          2
2019-09-19          3
2019-09-20          4
2019-09-21          5

and make groupby with sum:

result = df.groupby(df.index).sum()

            Adj Close
2019-09-19          4
2019-09-20          6
2019-09-21          5

Python Django: Merge Dataframe performing Sum on overlapping columns

1 Answers