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:

$amountsecuritytickerExtraColumnExtraAmount05000      AppleAAPLUSDollar10000.012000      CoorsTAPUSDollar10000.023000  MicrosoftMSFTUSDollar10000.041000    AluminaAWC.AUAUDollar2000.051000    TelstraTLS.AUAUDollar2000.0