0

I have the below table which shows company tickers along the x-axis and dates on the y-axis with share price information in the table. The table has Nan values for certain company tickers until their IPO date where their share price information is then put on the table. I would like to pivot this table so the tickers will then be on the y-axis with counting figures up to 180 (180 days) on the x-axis. These counting figures would represent each day from IPO day (day 1) for each individual company ticker and therefore show the relevant share pricing information for that company's day 1, day 2 -> day 180 etc.

AC ACQRW ADVWW API ... VRAX VRRM VSAC VYGR Date ...
2015-01-02 NaN NaN NaN NaN ... NaN NaN NaN NaN 2015-01-05 NaN NaN NaN NaN ... NaN NaN NaN NaN 2015-01-06 NaN NaN NaN NaN ... NaN NaN NaN NaN 2015-01-07 NaN NaN NaN NaN ... NaN NaN NaN NaN 2015-01-08 NaN NaN NaN NaN ... NaN NaN NaN NaN ... ... ... ... ... ... ... ... ... ... 2022-11-25 40.494820 NaN NaN 2.62 ... 1.360 15.86 10.140 5.51 2022-11-28 40.265415 NaN NaN 2.50 ... 1.350 15.66 10.140 5.57 2022-11-29 38.500000 NaN NaN 2.65 ... 1.320 15.51 10.140 5.54 2022-11-30 40.450001 NaN NaN 3.14 ... 1.565 15.85 10.240 5.94 2022-12-01 40.299999 NaN NaN 2.94 ... 1.610 15.82 10.176 6.04

I have spent hours with CHATGPT and tried many different pivots but can't seem to easily get the relevant days' pricing information for each company.

Roman G
  • 11
  • 1
    What's the appropriate GPT prompt for "[create](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_dict.html) a [reproducible](https://stackoverflow.com/a/52413247) example df"? – J_H Feb 24 '23 at 20:29
  • Welcome to Stack Overflow! Please take the [tour](https://stackoverflow.com/tour), read [what's on-topic here](https://stackoverflow.com/help/on-topic), [How to Ask](https://stackoverflow.com/help/how-to-ask), and the [question checklist](https://meta.stackoverflow.com/questions/260648/stack-overflow-question-checklist), and provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example "Minimal Reproducible Example"). – itprorh66 Feb 24 '23 at 20:40
  • Hi Roman. Bring a tiny pandas dataframe as an example, and add a few trials that you did so we can take it from there. Show what you get and ask relevant questions (why the output is not so and so, etc.) – Oren Feb 24 '23 at 20:41

1 Answers1

0

IIUC, you can use pivot_table after melt your dataframe:

out = (df.melt(var_name='ticker', value_name='close').dropna()
         .assign(day=lambda x: x.groupby('ticker').cumcount().add(1))
         .pivot_table(index='day', columns='ticker', values='close'))
print(out)

# Output
ticker         AC        API       VRAX  VRRM  VSAC       VYGR
day                                                           
1       26.522747  50.500000  18.200001  10.0  9.87  17.750000  # 1st day after IPO
2       26.522747  47.000000  10.340000  10.0  9.89  17.500000  # 2nd day after IPO
3       26.522747  44.169998  16.799999  10.0  9.89  18.100000  # 3rd day after IPO
4       26.522747  48.790001  18.440001  10.0  9.88  17.799999  # and so on.
5       25.651733  56.490002  18.690001  10.0  9.90  19.150000
...           ...        ...        ...   ...   ...        ...
1831    36.849998        NaN        NaN   NaN   NaN   7.130000
1832    35.540001        NaN        NaN   NaN   NaN   7.080000
1833    36.000000        NaN        NaN   NaN   NaN   6.920000
1834    36.150002        NaN        NaN   NaN   NaN   7.090000
1835    36.160000        NaN        NaN   NaN   NaN        NaN

[1835 rows x 6 columns]

If you want the first 180 days, just slice the output: out.loc[:180].

Minimal Reproducible Example:

import yfinance as yf

tickers = ['AC', 'API', 'VRAX', 'VRRM', 'VSAC', 'VYGR']
df = yf.Tickers(tickers).history('10y')['Close']
Corralien
  • 109,409
  • 8
  • 28
  • 52