How To Add Attribute/tag Column To Dataframe?

I have the following DataFrame: security ticker $amount Apple AAPL 5000 Coors TAP 2000 Microsoft MSFT 3000 US Dollar 10000 Alumina AWC.AU

Solution 1:

Not the most elegant solution, but maybe give this a try?

First I recreated your DataFrame:

df = pd.DataFrame({'security': ['Apple', 'Coors', 'Microsoft', 'US Dollar', 'Alumina', 'Telstra', 'AU Dollar'],
                   'ticker': ['AAPL', 'TAP', 'MSFT', "", 'AWC.AU', 'TLS.AU', ""],
                   '$amount': [5000, 2000, 3000, 10000, 1000, 1000, 2000]})

Then I used np.where to extract AU Dollar and US Dollar from the security column

df['Extra Column'] = np.where(df['ticker'] == "", df['security'], np.nan)
df['Extra Column'] = df['Extra Column'].fillna(method='bfill')
df['Extra Amount'] = np.where(df['ticker'] == "", df['$amount'], np.nan)
df['Extra Amount'] = df['Extra Amount'].fillna(method='bfill')
result = df[df['ticker']!='']

Output:

   $amount   security  ticker Extra Column  Extra Amount
0     5000      Apple    AAPL    US Dollar       10000.0
1     2000      Coors     TAP    US Dollar       10000.0
2     3000  Microsoft    MSFT    US Dollar       10000.0
4     1000    Alumina  AWC.AU    AU Dollar        2000.0
5     1000    Telstra  TLS.AU    AU Dollar        2000.0