5

I have a problem creating a Pandas Dataframe with multi indexing issue. In the data below, you will see that its the data for 2 banks, and each bank has 2 assets and each asset has 3 features. My data is similarly structured and I want to create a dataframe out of this. 

Data = [[[2,4,5],[3,4,5]],[[6,7,8],[9,10,11]]]

Banks = ['Bank1', 'Bank2']

Assets = ['Asset1', 'Asset2']

Asset_feature = ['private','public','classified']

I have tried various ways to do this but I've always failed to create an accurate dataframe. The result should look something like this: 

      Asset1                      Asset2
      private public classified   private public classified
Bank1   2       4       5           3       4       5
Bank2   6       7       8           9       10      11

Any help would be much appreciated.

Syed Ahmed
  • 199
  • 1
  • 10

1 Answers1

6
import pandas as pd
import numpy as np
assets = ['Asset1', 'Asset2']
Asset_feature = ['private','public','classified']
Banks = ['Bank1', 'Bank2']
Data = [[[2,4,5],[3,4,5]],[[6,7,8],[9,10,11]]]
Data = np.array(Data).reshape(len(Banks),len(Asset_feature) * len(assets))


midx = pd.MultiIndex.from_product([assets, Asset_feature])
test = pd.DataFrame(Data, index=Banks, columns=midx)
test

which gives this output

       Asset1                    Asset2                  
      private public classified private public classified
Bank1       2      4          5       3      4          5
Bank2       6      7          8       9     10         11
Matthew Barlowe
  • 2,229
  • 1
  • 14
  • 24
  • It has to be a n-banks solution. So the reshape wouldn't work. – Syed Ahmed Oct 25 '19 at 20:58
  • Hi Matthew. The very first solution that you posted works like a charm. I forgot to mention that the solution also needs to be a n-Asset_feature and n-assets solution. Your first solution was perfect. But now I'm getting the issue to convert this dataframe into a dictionary so that I can send it to my local host in a JSON format. – Syed Ahmed Oct 25 '19 at 21:08
  • @SyedAhmed the multiindex should work no matter what size the assets and asset features as long as the data arrives in the shape you posted in your example – Matthew Barlowe Oct 25 '19 at 21:09
  • I changed some code that you wrote above and it works similarly. Instead of: Data = np.array(Data).reshape(len(Banks),6). I wrote something like this: Data = np.array(Data).reshape(len(Banks),len(assets)*len(Asset_feature)) – Syed Ahmed Oct 25 '19 at 21:18
  • 1
    @SyedAhmed i changed the answer to reflect fitting any data size instead of just the one presented in the question – Matthew Barlowe Oct 25 '19 at 21:25