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?
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