Pandas Even Wider Data

Question

I'm looking to make data even wider than what pd.pivot can provide, and am looking to even change the names of the columns to fit my needs.

I have 3 items with 3 stats that perform differently in 2 regions:

na = pd.DataFrame({
    'item':['a','b','c'],
    'stat1':[3,9,4],
    'stat2':[84,23,55],
    'stat3':[131,293,201]
})
''' na dataframe
  item  stat1  stat2  stat3
0    a      3     84    131
1    b      9     23    293
2    c      4     55    201
'''

eu = pd.DataFrame({
    'item':['a','b','c'],
    'stat1':[5,1,7],
    'stat2':[34,61,29],
    'stat3':[839,531,339]
})
''' eu dataframe
  item  stat1  stat2  stat3
0    a      5     34    839
1    b      1     61    531
2    c      7     29    339
'''

I am hoping to reshape them in a way to have all my stats per region on one line:

final = pd.DataFrame({
    'region': ['na','eu'],
    'a.stat1': [3,5],
    'a.stat2': [84,34],
    'a.stat3': [131,839],
    'b.stat1': [9,1],
    'b.stat2': [23,61],
    'b.stat3': [293,531],
    'c.stat1': [4,7],
    'c.stat2': [55,29],
    'c.stat3': [201,339]
})
''' resultant dataframe
  region  a.stat1  a.stat2  a.stat3  b.stat1  b.stat2  b.stat3  c.stat1  c.stat2  c.stat3
0     na        3       84      131        9       23      293        4       55      201
1     eu        5       34      839        1       61      531        7       29      339
'''

It seems like this is not possible using pd.pivot/pivot_table? How might I be able to do this?

Answer 1

Create a mapping of dataframe name -> value pairs. Assign the name column then concat all the dataframes. Then pivot to reshape and optionally flatten the multiindex columns

d = {'na': na, 'eu': eu}
out = pd.concat({k: v.assign(name=k) for k, v in d.items()})
out = out.pivot(index='name', columns='item')
out.columns = out.columns.map('{0[1]}.{0[0]}'.format)

---

    a.stat1  b.stat1  c.stat1  a.stat2  b.stat2  c.stat2  a.stat3  b.stat3  c.stat3
na        3        9        4       84       23       55      131      293      201
eu        5        1        7       34       61       29      839      531      339