CODEWITHSUNDEEP
python-3.xpandasmulti-level
puppet
puppet

Reputation: 727

Create frame wih both single and multi-level columns and add data to it

I have 2 dataframes like this

frame1=pd.DataFrame(columns=['A','B','C'])


a=['d1','d2','d3']
b=['d4','d5']
tups=([('T1',x) for x in a]+
  [('T2',x) for x in b])

cols=pd.MultiIndex.from_tuples(tups,names=['Trial','Data'])
frame2=pd.DataFrame(columns=cols)

My goal is to have both DataFrames in one, and then add some rows of data. The resulting DataFrame would be like

  Trial    A   B   C        T1         T2
  Data                  d1  d2  d3   d4  d5
0          1   2   3    4   5   6    7   8
1           ...     
...

That could somehow be achieved if I did 

frame2['A']=1
frame2['B']=2
frame2['C']=3

But this is not a clean solution, and I can't create the frame and then add data, for I would be required to at least insert manually the first row.

I tried

frame3=frame1.join(frame2)

>>  A1   A2   A3 (T1, d1) (T1, d2) (T1, d3) (T2, d4) (T2, d5)

This I think is not multi column level. My second trial

tup2=([('A1',),('A2',),('A3',)]+[('T1',x) for x in a]+
  [('T2',x) for x in b])
cols2=pd.MultiIndex.from_tuples(tup2,names=['Trial','Data'])
data=[1,2,3,4,5,6,7,8]
frame20=pd.DataFrame(data,index=cols2).T 

 Trial  A1  A2  A3 T1       T2    
 Data   NaN NaN NaN d1 d2 d3 d4 d5      
0        1   2   3  4  5  6  7  8

This one works fine when trying to query it frame20.loc[0,'A1'][0] but if for example I do

frame20['Peter']=1234
>Trial  A1  A2  A3 T1       T2      Peter
 Data   NaN NaN NaN d1 d2 d3 d4 d5      
0        1   2   3  4  5  6  7  8   1234

being the column 'Peter' what I desire as opposed to for example A1, which is what I get. My third trial

tup3=(['A','B','C']+[('T1',x) for x in a]+
  [('T2',x) for x in b])
cols3=pd.MultiIndex.from_tuples(tup3,names=['Trial','Data'])
frame21=pd.DataFrame(data,index=cols3).T

returned exactly the same as the second one.

So, what I'm looking for, is a way to do

pd.DataFrame(rows_of_data,index=alfa).T  #or
pd.DataFrame(rows_of_data,columns=beta)

where either alfa or beta are in a correct format.

Also, as a bonus, let's say I finally came up with a way to do

finalframe=pd.DataFrame(columns=beta)

How do I have to use concat,append or join so I can add a random row of data such as data=[1,2,3,4,5,6,7,8] to my empty but perfectly created finalframe?

Thank you, best regards

Upvotes: 1

Views: 32

Answers (1)

piRSquared
piRSquared

Reputation: 294488

You want to add a level to frame1 with empty strings

pandas.MultiIndex.from_tuples

idx = pd.MultiIndex.from_tuples([(c, '') for c in frame1])
f1 = frame1.set_axis(idx, axis=1, inplace=False)
frame3 = pd.concat([f1, frame2], axis=1)

frame3.reindex([0, 1])

     A    B    C   T1             T2     
                   d1   d2   d3   d4   d5
0  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN
1  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN

pandas.concat

frame3 = pd.concat([
    pd.concat([frame1], keys=[''], axis=1).swaplevel(0, 1, 1),
    frame2], axis=1)

frame3.reindex([0, 1])

     A    B    C   T1             T2     
                   d1   d2   d3   d4   d5
0  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN
1  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN

Upvotes: 1

Related Questions