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.

Improve this question
1
  • I'm working on Python3 Commented Oct 25, 2019 at 19:54

1 Answer 1

Reset to default
7 
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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

It has to be a n-banks solution. So the reshape wouldn't work.
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.
@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
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))
@SyedAhmed i changed the answer to reflect fitting any data size instead of just the one presented in the question

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.