How does using python Pandas unstack in plt?

Question

I reference to the information on the Internet.

I would like to painting my data.

        id        time           w
5630    39  2018-03-01  841.704067
5631    39  2018-03-02  605.119444
5632    39  2018-03-03  651.337735
5633    39  2018-03-04  595.974252
5634    39  2018-03-05  656.031315
5635    39  2018-03-06  972.101045
5636    39  2018-03-07  660.659986
5637    39  2018-03-08  1225.648122
        .
        .
5685    39  2018-04-25  699.347653
5686    39  2018-04-26  516.267288
5687    39  2018-04-27  621.807574
5688    39  2018-04-28  488.903030
5689    39  2018-04-29  790.637525
5690    39  2018-04-30  457.569811
5691    40  2018-03-01  157.636364
5692    40  2018-03-02  138.759563
5693    40  2018-03-03  209.213996
5694    40  2018-03-04  177.255571
5695    40  2018-03-05  155.696106
        .
        .
5748    40  2018-04-27  133.512968
5749    40  2018-04-28  157.630623
5750    40  2018-04-29  210.996613
5751    40  2018-04-30  120.643963

My reference URL

I execute this:

fig, ax = plt.subplots(figsize=(15,7))
df2.groupby(['time','id']).count()['w'].unstack().plot(ax=ax)

But my result is strange.

I would like to painting like this:

I just cut out my sample, but I want to draw it all at once. Overlap or alone.

How to achieve similar results below.

I really need your help.Many thanks.

jezrael · Accepted Answer · 2018-06-09 15:06:04Z

0

All working nice, but count is 1 for your data sample, because groupby by columns time and id and for ech pair get size of groups 1:

print (df2.groupby(['time','id']).count()['w'])
time                 id
2017-04-03 18:09:04  39    1
2017-04-03 18:16:04  39    1
2017-04-03 18:23:04  39    1
2017-04-03 18:30:04  39    1
2017-04-03 18:37:04  39    1
2017-04-03 18:44:04  40    1
2017-04-03 18:51:04  40    1
2017-04-03 18:58:04  40    1
2017-04-03 19:05:04  40    1
2017-04-03 19:12:04  40    1
Name: w, dtype: int64

Then reshaped output is DataFrame with 2 columns filled by 1 and NaNs for values which not exist in pairs:

df = df2.groupby(['time','id']).count()['w'].unstack()
print (df)
id                    39   40
time                         
2017-04-03 18:09:04  1.0  NaN
2017-04-03 18:16:04  1.0  NaN
2017-04-03 18:23:04  1.0  NaN
2017-04-03 18:30:04  1.0  NaN
2017-04-03 18:37:04  1.0  NaN
2017-04-03 18:44:04  NaN  1.0
2017-04-03 18:51:04  NaN  1.0
2017-04-03 18:58:04  NaN  1.0
2017-04-03 19:05:04  NaN  1.0
2017-04-03 19:12:04  NaN  1.0

If values are dates only, so need Grouper by 7d for week or MS for start of month instead time columns (also added parameter fill_value=0 for replace NaNs to 0):

df = df2.groupby([pd.Grouper(freq='7d', key='time'),'id'])['w'].count().unstack(fill_value=0)
print (df)
buildingid  39  40
reporttime        
2018-03-01   7   5
2018-03-08   1   0
2018-04-19   1   0
2018-04-26   5   4

So finally your code looks like:

fig, ax = plt.subplots(figsize=(15,7))
df2.groupby([pd.Grouper(freq='7d', key='time'),'id'])['w'].count().unstack(fill_value=0).plot(ax=ax)

edited Jun 9, 2018 at 15:06

answered Jun 9, 2018 at 13:10

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Linminxiang Over a year ago

@@jezrael, Can suggest me what I can do to achieve similar functionality on the reference site? I created a sample of a similar reference site and executed it and I got the same result.

jezrael Over a year ago

I think yes, give me some time.

jezrael Over a year ago

@Linminxiang - I dont see your real data, so my solution suggest count values per days. But it plot nothing if data only for one day, then neecd change freq=d to freq=1h or similar.

Linminxiang Over a year ago

@@jezrael, I have update my data. My data like this. By the way, the code just mentioned can't draw things. Can you help me?

jezrael Over a year ago

@Linminxiang - Yes, I think need this, only change grouped = df.groupby('a') rowlength = grouped.ngroups/2 to grouped = df2.groupby('id') rowlength = grouped.ngroups//2

|

Collectives™ on Stack Overflow

How does using python Pandas unstack in plt?

1 Answer 1

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related