3

I think this a pretty simple question. I am new to python and I am unable to find the perfect answer.

I have a dataframe :

A          B       C       D       E
203704     WkDay   00:00   0.247   2015
203704     WkDay   00:30   0.232   2015
203704     Wkend   00:00   0.102   2015
203704     Wkend   00:30   0.0907  2015
203704     WkDay   00:00   0.28    2016
203704     WkDay   00:30   0.267   2016
203704     Wkend   00:00   0.263   2016
203704     Wkend   00:30   0.252   2016

I need :

A       B      00:00   00:30    E
203704  Wkday  0.247   0.232   2015
203704  Wkend  0.102   0.0907  2015
203704  Wkday  0.28    0.267   2016
203704  Wkday  0.263   0.252   2016

I have gone through various links like this and this. However, implementing them I am getting various errors.

I was able to run this successfully

pandas.pivot_table(df,values='D',index='A',columns='C')

but it does not give what exactly I want.

Any help on this would be helpful.

Community
  • 1
  • 1
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Does this answer your question? [Parsing Data From Long to Wide Format in Python](https://stackoverflow.com/questions/18969034/parsing-data-from-long-to-wide-format-in-python) – Sven Jan 22 '20 at 15:06

2 Answers2

6

You can add multiple columns to list as argument of parameter index:

print (pd.pivot_table(df,index=['A', 'B', 'E'], columns='C',values='D').reset_index())
C       A      B     E  00:00   00:30
0  203704  WkDay  2015  0.247  0.2320
1  203704  WkDay  2016  0.280  0.2670
2  203704  Wkend  2015  0.102  0.0907
3  203704  Wkend  2016  0.263  0.2520

If need change order of columns:

#reset only last level of index
df1 = pd.pivot_table(df,index=['A', 'B', 'E'], columns='C',values='D').reset_index(level=-1)
#reorder first column to last
df1.columns = df1.columns[-1:] | df1.columns[:-1]
#reset other columns
print (df1.reset_index())
C       A      B  00:00  00:30       E
0  203704  WkDay   2015  0.247  0.2320
1  203704  WkDay   2016  0.280  0.2670
2  203704  Wkend   2015  0.102  0.0907
3  203704  Wkend   2016  0.263  0.2520
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • @piRSquared - thank you. I still wondering about pictures in your answers ;) Your style is wonderful and good looking, but not kill time? Or some script help you? – jezrael Jul 22 '16 at 07:41
  • 1
    on my mac, its a quick command+shift+ctrl+4, then drag and pic is in clipboard. Otherwise its drag over text, command+C, command+V, reformat to code. Point is, I find it quicker. – piRSquared Jul 22 '16 at 07:44
2

Use set_index and unstack

df.set_index(['A', 'B', 'E', 'C']).D.unstack().reset_index()

enter image description here

If you insist on the exact format

df.set_index(['A', 'B', 'E', 'C']) \
    .D.unstack().reset_index() \
    .rename_axis(None, 1).iloc[:, [0, 1, 3, 4, 2]]

enter image description here

piRSquared
  • 285,575
  • 57
  • 475
  • 624