0

I have a pd.DataFrame that looks like the following below:

df

   id      date     price                        
0   1   31-08-2000  118.0 
1   2   31-08-2000  115.0 
2   1   01-09-2000  114.0  
3   2   01-09-2000  113.0  
4   3   01-09-2000  114.0  
5   4   01-09-2000  118.0  
6   1   02-09-2000  115.0  
7   2   02-09-2000  114.0  
8   3   02-09-2000  113.0  
9   4   02-09-2000  114.0

However, I want the pd.DataFrame to look like the following:

df
               1       2      3      4  
date
31-08-2000   118.0   115.0   NaN    NaN
01-09-2000   114.0   113.0  114.0  118.0 
02-09-2000   115.0   114.0  113.0  114.0

I have tried using pd.pivot_table but I cannot seem to get it to look like how I want it. Any help would be awesome!

oceanbeach96
  • 604
  • 9
  • 19
  • 2
    `df.pivot_table(index='date', columns='id', values='price')` not working? – jezrael Mar 16 '20 at 07:23
  • Can you be more specific why not working? – jezrael Mar 16 '20 at 07:24
  • When I pivot, it does not start from the correct date. For example, my dataset starts with the date column ranging from 31-08-2000 until 31-12-2019, but when I do the pivot, all values before 20-01-2002 are not included – oceanbeach96 Mar 16 '20 at 07:33
  • 1
    hmmm, are datetime like strings? If convert them to datetimes by `df['date'] = pd.to_datetime(df['date'], dayfirst=True)` before pivot, it working? – jezrael Mar 16 '20 at 07:34
  • 1
    Ahhh, yes. That was the issue. Thanks for that! – oceanbeach96 Mar 16 '20 at 07:38

0 Answers0