Python Pandas - Multiplying Columns Data in a Dataframe

Question

We have a sample cryptocurrencies portfolio and want to convert each coin balance to USD and get the total portfolio value in USD.

The code:

# Assets to convert
assets_to_convert =  [{'Asset': 'BTC', 'Bal': 0.5}, 
                    {'Asset': 'IOTX', 'Bal': 4}, 
                    {'Asset': 'QKC', 'Bal': 3}, 
                    {'Asset': 'AGI', 'Bal': 9}, 
                    {'Asset': 'NXS', 'Bal': 2}, 
                    {'Asset': 'DATA', 'Bal': 4}, 
                    {'Asset': 'SC', 'Bal': 5}, 
                    {'Asset': 'NPXS', 'Bal': 9}]

assets_to_convert = pd.DataFrame(assets_to_convert)

# All Pairs Prices
prices = pd.read_json("https://api.binance.com/api/v1/ticker/allPrices")
prices = pd.DataFrame(prices)
pattern = r"(\w+)(USDT|BTC|ETH|BNB)$"
prices[["Asset","Quote"]] = prices["symbol"].str.extract(pattern) 
BTCUSDT_price  = float(prices.price[prices.symbol == "BTCUSDT"])  

# Dataframes Merged
merged = pd.merge(assets_to_convert,prices, on="Asset")
merged["USD_bal"] = "??"

print(merged.head())

The merged table is returning this:

  Asset  Bal          price   symbol Quote USD_bal
0   BTC  0.5  6328.29000000  BTCUSDT  USDT      ??
1  IOTX  4.0     0.00000227  IOTXBTC   BTC      ??
2  IOTX  4.0     0.00003986  IOTXETH   ETH      ??
3   QKC  3.0     0.00000637   QKCBTC   BTC      ??
4   QKC  3.0     0.00011241   QKCETH   ETH      ??

The Asset coins can be converted to BTC by filtering by Quote == "BTC" and getting the "price" column. The USD conversion is then: "Bal" * "price" * "BTCUSDT_price"

How do we do it?

(If merging and filtering the dataframes is not the right way to do this, please suggest a proper way.)

modesitt · Accepted Answer · 2018-08-08 19:46:43Z

2

What is wrong with simply multiplying the price by the Bal by the price of bitcoin, from your formula?

merged['USD_bal'] = merged.price * merged.Bal * merged.iloc[0].price

answered Aug 8, 2018 at 19:46

modesitt

7,2492 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FrankC Over a year ago

Thanks. I filtered by BTC and used your line: merged = merged.loc[merged["Quote"] == "BTC"] merged["USD_bal"] = merged.price * merged.Bal * BTCUSDT_price But the BTC Asset is left out, as it doesn't have a pair with BTC quote. Do you know how to handle this?

modesitt Over a year ago

why not just handle it in a special case and append it seperatly since it's USD_bal is it's actual price?

Collectives™ on Stack Overflow

Python Pandas - Multiplying Columns Data in a Dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related