0

I have a dataframe, df2

     date        ID     count
 0  2017-08-01   40      12
 1  2017-09-03   40      5
 2  2017-09-22   50      6
 3  2017-10-23   75      5
 4  2017-10-24   100     10

It should now be: where the count number of column ID is represented each month

  Index         40       50     75   100
  August        12       0      0    0
  September     5        6      0    0
  October       0        0      5    10

Here is the code:

df3= pd.DataFrame(df2, columns= ['date','ID','count'])
df3=df.pivot(index='date', columns='ID',values='count').reset_index().fillna(0)
print(df3)
df3.set_index('date').plot()
pyplot.show()

But I get this error: ValueError: Index contains duplicate entries, cannot reshape matplotlib.figure.Figure at 0x1c0521496a0

Bode
  • 527
  • 2
  • 9
  • 19
  • Here's an alt: `df.set_index([df.date.astype(str).str.rsplit('-', 1).str[0], 'ID'])['count'].unstack(fill_value=0)` – cs95 Dec 20 '17 at 16:18
  • I tried this but I got this error: AttributeError: 'DataFrame' object has no attribute 'date' – Bode Dec 20 '17 at 22:17
  • I have edited the question with the 2nd dataframe index changing to months in words – Bode Dec 20 '17 at 23:20

0 Answers0