How to combine three pandas series into one dataframe by date?

user_15530206 0
Viewed 999

I trying to calculate ADX indicator using using library called ta - link

I am using yahoo finance API to get the data.

this is my code

import numpy as np
import yfinance as yf
import ta

def fetchOHLC(ticker,duration):
    """extracts historical data and outputs in the form of dataframe"""
    tickerdata = yf.Ticker(ticker)
    data = tickerdata.history(period=duration)
    data.columns=["open","high","low","close","volume","dividends","stock splits"]
    return data

ticker="MSFT"
ohlc_1day = fetchOHLC(ticker,"3mo") 
df=ta.trend.ADXIndicator(ohlc_1day['high'],ohlc_1day['low'],ohlc_1day['close'])
df1=df.adx()
df2=df.adx_neg()
df3=df.adx_pos()
print(df1)
print(df2)
print(df3)

this gives the output like this

Date
2021-06-07    20.912307
2021-06-08    19.820280
2021-06-09    18.502489
2021-06-10    17.278827
2021-06-11    16.142569
2021-06-14    15.874234
Name: adx, dtype: float64
Date
2021-06-07    18.092438
2021-06-08    19.098579
2021-06-09    19.629949
2021-06-10    18.663566
2021-06-11    18.026429
2021-06-14    16.649220
Name: adx_neg, dtype: float64
Date
2021-06-07    22.070724
2021-06-08    21.374768
2021-06-09    20.175771
2021-06-10    19.182517
2021-06-11    18.527663
2021-06-14    21.356572
Name: adx_pos, dtype: float64

NOTE-I have removed a lot of rows from the output for the length of the question.

I want the output to be like this, with these specific column names as I am using them in functions

Date            DX          DIsum      DIdiff
2021-06-07    20.912307   22.070724   18.092438  
2021-06-08    19.820280   21.374768   19.098579
2021-06-09    18.502489   20.175771   19.629949
2021-06-10    17.278827   19.182517   18.663566
2021-06-11    16.142569   18.527663   18.026429
2021-06-14    15.874234   21.356572   16.649220

DIsum is adx_pos() and DIdiff is adx_neg()

How can I merge these three series based on the date to have output like this?

pandas dataframe python
2 Answers

You can concat them:

pd.concat([adx, adx_neg, adx_pos], axis=1)

Full example:

import pandas as pd
df = pd.DataFrame({'Date': {0: '2021-06-07',
  1: '2021-06-08',
  2: '2021-06-09',
  3: '2021-06-10',
  4: '2021-06-11',
  5: '2021-06-14'},
 'price': {0: 20.912307000000002,
  1: 19.82028,
  2: 18.502489,
  3: 17.278827,
  4: 16.142569,
  5: 15.874234}})
df = df.set_index('Date')
df1, df2, df3 = df.copy(), df.copy(), df.copy()

# Code
pd.concat([df1, df2, df3], axis=1)


# Output
                price      price      price
Date                                       
2021-06-07  20.912307  20.912307  20.912307
2021-06-08  19.820280  19.820280  19.820280
2021-06-09  18.502489  18.502489  18.502489
2021-06-10  17.278827  17.278827  17.278827
2021-06-11  16.142569  16.142569  16.142569
2021-06-14  15.874234  15.874234  15.874234
user_119717850

The best way to do this is to do a double outer join on the Date columns

df1 = df1.merge(df2, how='outer', on='Date')
df = df1.merge(df3, how='outer', on='Date')

This ensures that you won't lose data if one dataframe has more Dates than the others.

user_46399950
Related