I am struggling to restructure my dataframe in a simple and effective way.
The first dataframe 'new' is what I have and the desired dataframe is new2. I have been messing around with pivot, stack, unstack, set_index, reshape etc for a long time and still can't achieve this simple transformation. I keep getting either error messages or the wrong outcome. Could someone please help out?
What I have
data1 = [50, 60]
data2 = [100, 200]
year = [2015,2016]
new = pd.DataFrame({'product': data1, 'year':year, 'market':data2})
What I want
new2 = pd.DataFrame(columns = ['2015', '2016'])
new2.loc['market'] = data1
new2.loc['product'] = data2
You need set_index with T and last for remove column name add rename_axis:
df = df.set_index('year').T.rename_axis(None, axis=1)
print (df)
2015 2016
market 100 200
product 50 60