Pandas dataframe - convert time series with multiple elements, to a flattened dataframe with elements as columns

Question

I have a time series dataset stored in a dataframe, with multiple elements, for example stocks with their price, p/e ratio, and p/b ratio - so I have 3 rows per ticker/date. I'm wondering if there is a way to convert this, so I have one row for each ticker/date, and the price,p/e, and p/b as columns.

Sample dataframe:

import pandas as pd

dfts = pd.DataFrame({
    'date': ['2020-01-01','2020-01-01','2020-01-01',
             '2020-01-01','2020-01-01','2020-01-01',
             '2020-01-02','2020-01-02','2020-01-02',
             '2020-01-02','2020-01-02','2020-01-02'],
    'ticker': ['AAPL','AAPL','AAPL',
               'GOOGL','GOOGL','GOOGL',
               'AAPL', 'AAPL', 'AAPL',
               'GOOGL', 'GOOGL', 'GOOGL'],
    'type': ['price','p/e','p/b',
             'price','p/e','p/b',
             'price','p/e','p/b',
             'price','p/e','p/b'],
    'value': [300,20,2,
              1000,25,3,
              310,21,2.1,
              1110,26,2.9]
})

print(dfts)

I'm looking to convert this and get a result such as:

Date        Ticker     Price     P/E     P/B
2020-01-01  AAPL       300       20      2
2020-01-02  AAPL       310       21      2.1
2020-01-01  GOOGL      1000      25      3
2020-01-02  GOOGL      1110      26      2.6

Thanks

This is called pivot. Please read [this question](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) and come back with your attempt. — Quang Hoang, May 20 '20 at 20:13

score 0 · Accepted Answer · answered May 21 '20 at 06:24

Check below lines if help you

import pandas as pd

dfts = pd.DataFrame({
    'date': ['2020-01-01','2020-01-01','2020-01-01',
             '2020-01-01','2020-01-01','2020-01-01',
             '2020-01-02','2020-01-02','2020-01-02',
             '2020-01-02','2020-01-02','2020-01-02'],
    'ticker': ['AAPL','AAPL','AAPL',
               'GOOGL','GOOGL','GOOGL',
               'AAPL', 'AAPL', 'AAPL',
               'GOOGL', 'GOOGL', 'GOOGL'],
    'type': ['price','p/e','p/b',
             'price','p/e','p/b',
             'price','p/e','p/b',
             'price','p/e','p/b'],
    'value': [300,20,2,
              1000,25,3,
              310,21,2.1,
              1110,26,2.9]
})
df = dfts.set_index(['date','ticker','type'])['value'].unstack().reset_index()
print(df)

output frame is like this--

your welcome, @Brian196 is this the excepted answer , as you dint mark it — Hietsh Kumar, May 24 '20 at 11:22

Pandas dataframe - convert time series with multiple elements, to a flattened dataframe with elements as columns

1 Answers1

Linked