0

I would like to transform this dataframe:

enter image description here

into this dataframe:

enter image description here

As can't get an idea to convert this. Would you guys please suggest me something? or just give an example so that I could get an idea.

Thank you very much

Zephyr
  • 11,891
  • 53
  • 45
  • 80
Shax
  • 117
  • 9
  • 3
    guess you need `pivot()`....Does this answer your question [how-can-i-pivot-a-dataframe](https://stackoverflow.com/questions/47152691/how-can-i-pivot-a-dataframe)? see this answer(specially question 10) https://stackoverflow.com/a/47152692/14289892 also have a look at [how-to-make-good-reproducible-pandas-examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – Anurag Dabas Aug 22 '21 at 16:32

1 Answers1

1

You could use pandas.DataFrame.pivot, as already suggested by Anurag Dabas in the comment.

Fake dataframe generation:

df = pd.DataFrame({'date': pd.date_range(start = '2020-01-01', end = '2020-12-31', freq = 'H')})
df['dtDate'] = df['date'].dt.date
df['intHour'] = df['date'].dt.time
df['dblPrice'] = 45 + 10*np.random.rand(len(df))
df = df.drop('date', axis = 1)

       dtDate   intHour   dblPrice
0  2020-01-01  00:00:00  45.718848
1  2020-01-01  01:00:00  53.263963
2  2020-01-01  02:00:00  50.052291
3  2020-01-01  03:00:00  50.529949
4  2020-01-01  04:00:00  50.047817
5  2020-01-01  05:00:00  53.697682
6  2020-01-01  06:00:00  51.529444
7  2020-01-01  07:00:00  52.491254
8  2020-01-01  08:00:00  46.385390
9  2020-01-01  09:00:00  49.693818

Dataframe re-shaping:

pivot = df.pivot(index = 'dtDate', columns = 'intHour', values = 'dblPrice')

intHour      00:00:00   01:00:00   02:00:00   03:00:00   04:00:00   05:00:00   06:00:00   07:00:00   08:00:00   09:00:00   10:00:00   11:00:00   12:00:00   13:00:00   14:00:00   15:00:00   16:00:00   17:00:00   18:00:00   19:00:00   20:00:00   21:00:00   22:00:00   23:00:00
dtDate                                                                                                                                                                                                                                                                            
2020-01-01  45.718848  53.263963  50.052291  50.529949  50.047817  53.697682  51.529444  52.491254  46.385390  49.693818  45.360069  52.438855  46.121786  47.362026  49.518245  47.409405  50.045261  45.088922  45.838770  48.852895  48.771692  54.614658  53.027552  46.069222
2020-01-02  52.774808  47.482666  49.162553  54.619955  52.532569  46.514277  47.616911  48.752172  51.677121  49.132804  52.299008  51.722785  48.034956  52.865316  45.020614  53.077154  53.053721  46.574998  53.698860  54.013042  50.116996  47.273836  48.851538  54.646906
2020-01-03  46.181314  54.138609  45.506831  48.773422  48.949266  47.507932  51.976893  45.112487  52.317956  50.341193  45.406580  49.407413  49.301970  53.041705  51.880567  47.127299  54.666001  52.496856  50.869778  52.419076  50.995989  50.280920  46.112861  50.130860
2020-01-04  51.113309  54.041695  47.907112  52.687296  46.317464  47.114933  54.130554  46.394585  52.649151  53.926256  52.586092  47.100354  53.002020  53.479808  54.749047  54.556096  54.289927  51.419977  48.235973  45.398855  45.732135  50.247591  54.971369  52.750146
2020-01-05  47.200667  50.321729  45.292155  53.899454  47.277091  49.783811  50.375889  46.135633  52.069637  50.866783  45.392834  45.119652  48.632398  45.170602  45.360432  53.367277  51.244329  54.685162  51.763552  48.116138  54.487315  48.386047  48.803590  52.970647
2020-01-06  47.840619  50.312405  50.337254  54.423867  46.266559  50.475936  49.181458  47.944260  47.324905  50.735209  51.074228  52.033799  54.681283  48.680414  50.469888  52.701592  48.967748  45.960513  47.052134  45.109800  45.443353  52.370265  53.654452  45.472438
2020-01-07  45.128212  50.516360  50.695361  48.564615  50.909228  51.525838  51.858014  46.047718  53.582947  45.873945  51.138986  50.027207  53.746702  48.026874  49.608405  53.293533  47.220813  52.138184  54.960185  46.814746  52.499729  52.920501  52.078320  52.628939
2020-01-08  49.748170  50.894821  46.181366  48.297795  53.511553  45.649108  53.776943  47.607423  53.855729  45.231116  52.114776  50.961731  46.493839  50.303245  46.113842  54.736249  51.150236  54.569241  52.072222  49.109150  46.889599  48.710836  54.904266  49.440243
2020-01-09  52.810293  46.014310  54.264264  53.431890  52.286309  54.211923  45.614166  48.802290  47.156604  54.763775  50.186372  53.741367  52.088079  51.072206  50.760230  48.868039  48.711484  45.725199  54.043615  48.735622  51.992469  45.259647  45.661195  45.561431
2020-01-10  54.621446  48.658543  45.680074  48.974074  50.671488  53.473446  48.988861  46.535157  53.964884  50.390561  47.869435  50.174431  52.649430  46.128354  50.570063  47.731459  54.586399  47.043045  49.557339  48.791510  48.334697  46.057200  51.793002  52.415360
Zephyr
  • 11,891
  • 53
  • 45
  • 80
  • I have done for the other dataframe but it shows: ValueError: Index contains duplicate entries, cannot reshape --------------------------------------------------------------------------- ValueError Traceback (most recent call last) – Shax Aug 23 '21 at 19:20
  • Thanks But a question only left: In your generated fake dataframe: "dtDate", there are also duplicate list of dates. But why the error didn't happened here. – Shax Aug 24 '21 at 09:14
  • Yes. In my fake dataframe `'dtDate'` has duplicate dates, but it a simple column, not the dataframe's index. On the contrary, if you check the fake dataframe index (first column on the left), there are only unique values: `0`, `1`, `2` and so on. You should provide a sample of your data, in place of images you posted in your question, if you want I work on the original dataframe – Zephyr Aug 24 '21 at 09:26
  • Check this please: https://github.com/sjshtura/test.git If you're interested. – Shax Aug 24 '21 at 13:24