Add a new column to Dataframe and set MultiIndex

Question

I would like to add a new column app_vendor_id and set it as MultIndex with currency but I receive an error.

My code :

currency = np.array(['BTC','ETH','BCH'])
u = np.array([5000,10000,1046])
cl_bal = pd.DataFrame(np.repeat(u, len(created_at)), index= 
pd.MultiIndex.from_product([currency, created_at], names= 
['currency', 'created_at']), dtype= int)
cl_bal = cl_bal.pivot_table(index='currency', columns= 'created_at')
cl_bal.columns = cl_bal.columns.droplevel(0)
cl_bal['app_vendor_id'] = 3
cl_bal.set_index(['app_vendor_id', 'currency'])

The error :

File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/indexes/base.py", line 3078, in get_loc return self._engine.get_loc(key) File "pandas/_libs/index.pyx", line 140, in pandas._libs.index.IndexEngine.get_loc File "pandas/_libs/index.pyx", line 162, in pandas._libs.index.IndexEngine.get_loc File "pandas/_libs/hashtable_class_helper.pxi", line 1492, in pandas._libs.hashtable.PyObjectHashTable.get_item File "pandas/_libs/hashtable_class_helper.pxi", line 1500, in pandas._libs.hashtable.PyObjectHashTable.get_item KeyError: 'currency'

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "histo_var.py", line 202, in cl_bal.set_index(['app_vendor_id', 'currency']) File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/frame

line 3909, in set_index level = frame[col]._values File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/frame.py", line 2688, in getitem return self._getitem_column(key) File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/frame.py", line 2695, in _getitem_column return self._get_item_cache(key) File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/generic.py", line 2489, in _get_item_cache values = self._data.get(item) File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/internals.py", line 4115, in get loc = self.items.get_loc(item) File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/indexes/base.py", line 3080, in get_loc return self._engine.get_loc(self._maybe_cast_indexer(key)) File "pandas/_libs/index.pyx", line 140, in pandas._libs.index.IndexEngine.get_loc File "pandas/_libs/index.pyx", line 162, in pandas._libs.index.IndexEngine.get_loc File "pandas/_libs/hashtable_class_helper.pxi", line 1492, in pandas._libs.hashtable.PyObjectHashTable.get_item pandas._libs.hashtable.PyObjectHashTable.get_item KeyError: 'currency'

you can't set currency as index, I think it's already an index so it's not available anymore, you could use reset_index before doing so — Ayoub ZAROU

jezrael jezrael · Accepted Answer · 2019-07-23T08:15:37

Use parameter append=True in DataFrame.set_index, because currency is already in index:

created_at = [1,2,3,4]
currency = np.array(['BTC','ETH','BCH'])
u = np.array([5000,10000,1046])
cl_bal = pd.DataFrame(np.repeat(u, len(created_at)), 
                      index=pd.MultiIndex.from_product([currency, created_at], 
                                                       names= ['currency', 'created_at']), 
                                                       dtype= int)
cl_bal = cl_bal.pivot_table(index='currency', columns= 'created_at')
cl_bal.columns = cl_bal.columns.droplevel(0)
cl_bal['app_vendor_id'] = 3
df = cl_bal.set_index(['app_vendor_id'], append=True)
print (df)
created_at                  1      2      3      4
currency app_vendor_id                            
BCH      3               1046   1046   1046   1046
BTC      3               5000   5000   5000   5000
ETH      3              10000  10000  10000  10000

Add a new column to Dataframe and set MultiIndex

1 Answers