Pandas grouping and inverting df manipulating of hist.price data

Question

For the following code the output returned is such:

The desired arrangement is however with the tickers raised and grouped/aggregated like:

All suggestions and feedback welcome.

Code sample

def prepare_data(symbol, look_back_period):
    start_date = date.today() - timedelta(days=look_back_period)
    end_date = date.today()

    prices_df = get_symbol_prices(symbol=symbol, start_date=start_date, end_date=end_date)
    prices_df = prices_df[['close']]

    df = pd.DataFrame(prices_df)
    df.index.name = 'datetime'
    df['symbol'] = symbol
    return df


def get_final_df(tickers, look_back_period):
    df = pd.DataFrame()

    for symbol in symbol_list:
        df = df.append(prepare_data(symbol=symbol, look_back_period=look_back_period))
    return df


def main():

    historical_df = get_final_df(tickers=TICKERS, look_back_period=LOOK_BACK_PERIOD)
    output_folder = 'E:/'
    file_name = 'HISTORICALPORTFOLIO.csv'
    historical_df.to_csv(os.path.join(output_folder, file_name))

do you need the multiindex where `'close'` is over all columns? — Rabinzel, Jun 12 '22 at 20:58
I've tried pivot and it's a little too unspecific, i.e. doesn't group tickers. [multi-index isn't critical and actually probably unwanted for further aggregating] — steeltoaster, Jun 12 '22 at 21:18
see my answer. isn't that exactly what you are searching for ? or what do you mean with 'unspecific' — Rabinzel, Jun 12 '22 at 21:56
pivot_table solved my issue in conjunction with rename_axis as provided in your example. — steeltoaster, Jun 13 '22 at 21:58

Rabinzel · Answer 1 · 2022-06-13T07:14:37.780

0

Here is the pivoted df:

df = pd.DataFrame({
    'datetime': ['2022-06-08', '2022-06-09', '2022-06-10', '2022-06-08', '2022-06-09', '2022-06-10', '2022-06-08', '2022-06-09', '2022-06-10', '2022-06-08', '2022-06-09', '2022-06-10', '2022-06-08', '2022-06-09', '2022-06-10'], 
    'value': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14], 
    'name': ['AAPL', 'AAPL', 'AAPL', 'ABNB', 'ABNB', 'ABNB', 'ADBE', 'ADBE', 'ADBE', 'AMD', 'AMD', 'AMD', 'AMZN', 'AMZN', 'AMZN']})

#if 'datetime' is your index, just add this before pivoting:
df = df.reset_index()

res = df.pivot(index='datetime', columns='name', values='value').reset_index().rename_axis(columns=None)

print(res)

     datetime  AAPL  ABNB  ADBE  AMD  AMZN
0  2022-06-08     0     3     6    9    12
1  2022-06-09     1     4     7   10    13
2  2022-06-10     2     5     8   11    14

edited Jun 13 '22 at 07:14

answered Jun 12 '22 at 21:54

Rabinzel

7,757
3
10
30

For "res = df.pivot(index='datetime', columns='symbol', values='close').reset_index().rename_axis(columns=None)" that is placed after "get_final_df" in main(), there is following Key Error... "in get_loc raise KeyError(key) from err KeyError: 'datetime'" – steeltoaster Jun 13 '22 at 05:45
see my input dataframe and how I named the columns. You need to adjust the column names to the data you have – Rabinzel Jun 13 '22 at 05:47
Yes that is ‘’datetime,’ ‘symbol’ and ‘close’ respectively. – steeltoaster Jun 13 '22 at 06:20
Ok, hm. Is 'datetime' maybe your index and not a column ? – Rabinzel Jun 13 '22 at 06:33
I believe so, yes. – steeltoaster Jun 13 '22 at 06:52
updated my answer. Just add `df=df.reset_index()` before pivoting. – Rabinzel Jun 13 '22 at 07:15

Pandas grouping and inverting df manipulating of hist.price data

1 Answers1