1
$\begingroup$

I have a dataframe in the following format

     symbol     name        date   close      market
0    BTC  Bitcoin  2013-04-28  134.21  1500520000
1    BTC  Bitcoin  2013-04-29  144.54  1491160000
2    BTC  Bitcoin  2013-04-30  139.00  1597780000
3    BTC  Bitcoin  2013-05-01  116.99  1542820000
4    BTC  Bitcoin  2013-05-02  105.21  1292190000
...
          symbol    name        date     close  market
752109    W3C  W3Coin  2018-04-07  0.000277       0
752110    W3C  W3Coin  2018-04-08  0.000286       0
752111    W3C  W3Coin  2018-04-09  0.000270       0
752112    W3C  W3Coin  2018-04-10  0.000286       0
752113    W3C  W3Coin  2018-04-11  0.000300       0

I want to create a matrix with prices and a matrix with marketcaps. Because I'm interested in re-creating the S&P500 index. I have done this with a for loop below. Question: How do I do this without a for loop?

edit: Note that some symbols have different number of dates, for example BTC might have 1000 days but W3Coin only 50 days. I think that is what the error message is telling me.

import data:

df = pd.read_csv('data-coins-scrape/CryptoData.csv', parse_dates=True)
cols = ['symbol', 'name', 'date', 'close', 'market']
df = df[cols]
symbols = df.symbol.unique()
nr_coins = df.symbol.nunique()

for loop to get prices and makretcaps

prices = []
marketscaps = []
shapes = []
for s in symbols:
    # slice out one coin
    dfc = df.loc[df.symbol == s]
    shapes.append(dfc.shape)
    prices.append(dfc['close'])
    marketcaps.append(df['market'])
del dfc

check the output

len(prices), len(marketcaps) 
# (1531, 3065) is the output 

type(prices[0]), prices[0].shape, prices[0].name 
# output: (pandas.core.series.Series, (1810,), 'close')

df.loc[df.symbol == 'BTC', 'close'].head() == prices[0].head() 
# returns True 
$\endgroup$
2
$\begingroup$

You can do the following:

df1 = df.set_index(['name', 'date'])
prices = df1['close'].unstack()
marketcaps = df1['marketcaps'].unstack()
$\endgroup$
  • $\begingroup$ ValueError: Index contains duplicate entries, cannot reshape $\endgroup$ – jacob Apr 14 '18 at 8:25
  • $\begingroup$ I can't download the file, 404 error. Without it i can't test it, since this code works fine for me in the example you gave. Edit the question with the full data, since that error only should happen if there is a duplicated (name, date) pair. $\endgroup$ – skd Apr 15 '18 at 12:02
0
$\begingroup$

create a price matrix with pd.concat, some works and some give a type error

pd.concat([prices[0], prices[1], prices[2], prices[3]], axis=1, join='outer').tail() 
# works!

pricemat = pd.concat(prices, keys=[btc.index], axis=1) 
# TypeError: unhashable type: 'DatetimeIndex'

pricemat = pd.concat(prices, axis=1) 
#TypeError: unhashable type: 'DatetimeIndex'

create a price matrix with for loop, give a value error:

priced_df = prices[0]
K = len(prices)
for i in range(1, K):
    priced_df = pd.concat([priced_df, prices[i]], axis=1)
    print(priced_df.shape[1])
# at 25 it stops and says:

# ValueError: Shape of passed values is (25, 2057), indices imply (25, 1810)
$\endgroup$
  • $\begingroup$ Add this to your question also..(if you have asked it) $\endgroup$ – Aditya Apr 14 '18 at 11:46
  • $\begingroup$ @Aditya no this is my attempt at an answer $\endgroup$ – jacob Apr 14 '18 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.