Pandas: Indexes with several related lines

user_9403338 0
Viewed 40

Considering the market_ticker dataframe:

import pandas as pd
import numpy as np

df = pd.DataFrame({'Ticker': ['EWZ US 05/29/20 P27', 'HSI US 12/30/20 C24800', 'TLT US 06/19/20 C225', 'EWZ US 05/29/20 P27'],
                   'Market': ['US NYSE', 'US NYSE', 'HK HKSE', 'US NYSE']})

df['Reduced_Ticker'] = df['Ticker'].apply(lambda a :" ".join(a.split(" ", 2)[:2]))

market_ticker = df[['Market','Reduced_Ticker']].groupby(['Market']).agg(list)
market_ticker['Reduced_Ticker'] = market_ticker['Reduced_Ticker'].apply(lambda x: pd.unique(x))
market_ticker

How can I transform each item in the list of indexes into a line related to the index itself? Output expected:

Market  |  Reduced_Ticker

HK SE   |     TLT US
_________________________       
              
US NYSE |  EWZ US
        |  HSI US
pandas python
2 Answers

Edit

Actually you just need 1 more step on your original solution : explode on column Reduced_Ticker:

market_ticker = market_ticker.explode('Reduced_Ticker')

If you like, you can still refer to my solution below:

First, you create a new column called Reduced_Ticker by slicing the string from column Ticker. Then select only the columns you want (Reduced_Ticker and Market), followed by dropping duplicates.

df_out = (
    df
    .assign(Reduced_Ticker = df.Ticker.str[:6])
    [['Market','Reduced_Ticker']]
    .drop_duplicates()
)

This is the output df_out:

Market  Reduced_Ticker
US NYSE EWZ US
US NYSE HSI US
HK HKSE TLT US
user_108411640

Try explode:

market_ticker.explode('Reduce_Ticker')

    Reduced_Ticker
Market  
HK HKSE TLT US
US NYSE EWZ US
US NYSE HSI US
user_18732370
Related