Saturday, December 23, 2023

[FIXED] plotting timeseries line graph for unique values in a column

December 23, 2023 line-plot, matplotlib, pandas, python, time-series No comments

Issue

I am trying to plot a timeseries graph for all the unique keys in my dataset, below is my dataset, I have 7 unique keys and I am trying to plot event_date on x-axis and line graph for count on y axis.

I am looping through each unique key and trying to plot date vs count however getting below error. Based on the error I am not able to understand why shape for y is (1,0)

import matplotlib.pyplot as plt
import pandas as pd


df_pandas = df.toPandas()

df_pandas.event_date = pd.to_datetime(df_pandas.event_date) # converting object to pandas datetime

colors = ['r', 'g', 'b', 'y', 'k', 'c', 'm']
for i, k in enumerate(df_pandas.key.unique()):
    plt.plot(df_pandas[df_pandas.key == k].event_date, df_pandas[df_pandas.key == k].count, '-o', label=k, c=colors[i])
plt.gcf().autofmt_xdate() ## Rotate X-axis so you can see dates clearly without overlap
plt.legend() ## Show legend

Traceback (most recent call last):
  File "/tmp/1697561012681-0/zeppelin_python.py", line 153, in <module>
    exec(code, _zcUserQueryNameSpace)
  File "<stdin>", line 13, in <module>
  File "/usr/local/lib64/python3.7/site-packages/matplotlib/pyplot.py", line 2813, in plot
    is not None else {}), **kwargs)
  File "/usr/local/lib64/python3.7/site-packages/matplotlib/__init__.py", line 1805, in inner
    return func(ax, *args, **kwargs)
  File "/usr/local/lib64/python3.7/site-packages/matplotlib/axes/_axes.py", line 1603, in plot
    for line in self._get_lines(*args, **kwargs):
  File "/usr/local/lib64/python3.7/site-packages/matplotlib/axes/_base.py", line 393, in _grab_next_args
    yield from self._plot_args(this, kwargs)
  File "/usr/local/lib64/python3.7/site-packages/matplotlib/axes/_base.py", line 370, in _plot_args
    x, y = self._xy_from_xy(x, y)
  File "/usr/local/lib64/python3.7/site-packages/matplotlib/axes/_base.py", line 231, in _xy_from_xy
    "have shapes {} and {}".format(x.shape, y.shape))
ValueError: x and y must have same first dimension, but have shapes (21,) and (1,)

event_date  key count
7/23/23 0:00    389628-135052   74858
7/28/23 0:00    389628-135052   75139
7/12/23 0:00    389631-135055   60910
7/18/23 0:00    389632-135056   68850
7/26/23 0:00    389632-135056   33704
7/27/23 0:00    389630-135054   119679
7/20/23 0:00    389632-135056   71281
7/15/23 0:00    389632-135056   68854
7/23/23 0:00    389634-135058   69020
7/20/23 0:00    389629-135053   59536
7/21/23 0:00    389631-135055   71065
7/25/23 0:00    389629-135053   66887
7/15/23 0:00    389629-135053   66150
7/12/23 0:00    389633-135057   53096
7/14/23 0:00    389634-135058   62948
7/25/23 0:00    389628-135052   74872
7/15/23 0:00    389631-135055   73870
7/18/23 0:00    389631-135055   74548
7/17/23 0:00    389632-135056   68402
7/20/23 0:00    389633-135057   54665
7/15/23 0:00    389633-135057   64637
7/30/23 0:00    389630-135054   123113
7/21/23 0:00    389630-135054   67368
7/12/23 0:00    389632-135056   55618
7/19/23 0:00    389633-135057   70942
7/21/23 0:00    389633-135057   68221
7/18/23 0:00    389628-135052   76602
8/1/23 0:00 389631-135055   13252
7/17/23 0:00    389629-135053   64287
7/25/23 0:00    389634-135058   68104
7/17/23 0:00    389634-135058   66301
7/12/23 0:00    389628-135052   61841
7/18/23 0:00    389630-135054   71472
7/24/23 0:00    389629-135053   68495
7/30/23 0:00    389629-135053   122907
7/26/23 0:00    389630-135054   26650
7/30/23 0:00    389632-135056   134425
7/22/23 0:00    389634-135058   62225
7/18/23 0:00    389633-135057   61047
7/22/23 0:00    389633-135057   60926
7/18/23 0:00    389634-135058   67725
7/16/23 0:00    389633-135057   64254
7/14/23 0:00    389633-135057   61383
7/24/23 0:00    389633-135057   66471
7/16/23 0:00    389629-135053   66548
7/19/23 0:00    389628-135052   75846
7/17/23 0:00    389631-135055   73452
7/13/23 0:00    389631-135055   82725
7/31/23 0:00    389634-135058   41786
7/26/23 0:00    389629-135053   68862
8/1/23 0:00 389633-135057   12333
7/21/23 0:00    389628-135052   72381
7/30/23 0:00    389628-135052   77991
7/19/23 0:00    389630-135054   68765
8/1/23 0:00 389630-135054   12798
7/21/23 0:00    389632-135056   66499
7/29/23 0:00    389633-135057   16644
7/20/23 0:00    389631-135055   74593
7/24/23 0:00    389630-135054   72015
7/27/23 0:00    389632-135056   98245
7/31/23 0:00    389630-135054   56117
7/22/23 0:00    389629-135053   62669
7/23/23 0:00    389631-135055   74936
7/25/23 0:00    389632-135056   69935
7/29/23 0:00    389630-135054   23579
7/13/23 0:00    389632-135056   71917
7/13/23 0:00    389633-135057   67979
7/19/23 0:00    389631-135055   74154
7/23/23 0:00    389632-135056   71347
7/27/23 0:00    389634-135058   57570
7/26/23 0:00    389633-135057   60073
7/17/23 0:00    389633-135057   66860
7/15/23 0:00    389628-135052   75962
7/29/23 0:00    389628-135052   69251
7/27/23 0:00    389631-135055   69051
7/28/23 0:00    389631-135055   74231
7/23/23 0:00    389633-135057   66237
7/30/23 0:00    389634-135058   130063
7/25/23 0:00    389631-135055   73097
7/31/23 0:00    389628-135052   75428
7/24/23 0:00    389634-135058   69958
7/13/23 0:00    389634-135058   72563
7/27/23 0:00    389629-135053   63235
8/1/23 0:00 389629-135053   12444
7/26/23 0:00    389628-135052   80514
7/14/23 0:00    389628-135052   72334
7/30/23 0:00    389631-135055   130862
7/29/23 0:00    389634-135058   21234
7/14/23 0:00    389629-135053   62070
7/30/23 0:00    389633-135057   117653
7/17/23 0:00    389628-135052   74903
7/24/23 0:00    389628-135052   76074
7/28/23 0:00    389630-135054   58201
7/25/23 0:00    389630-135054   70594
7/21/23 0:00    389629-135053   70020
7/20/23 0:00    389634-135058   59776
7/22/23 0:00    389631-135055   73678
7/19/23 0:00    389632-135056   68493
7/28/23 0:00    389632-135056   69294
7/29/23 0:00    389632-135056   16416
8/1/23 0:00 389634-135058   12202
7/16/23 0:00    389628-135052   74739
7/13/23 0:00    389628-135052   78198
7/16/23 0:00    389630-135054   70980
7/31/23 0:00    389632-135056   50267
7/26/23 0:00    389634-135058   77612
7/31/23 0:00    389631-135055   45171
7/22/23 0:00    389630-135054   70867
7/15/23 0:00    389634-135058   67374
7/31/23 0:00    389633-135057   50583
7/19/23 0:00    389629-135053   72029
7/22/23 0:00    389628-135052   75503
7/14/23 0:00    389632-135056   65704
7/12/23 0:00    389634-135058   54886
7/21/23 0:00    389634-135058   70547
7/16/23 0:00    389634-135058   68285
7/27/23 0:00    389628-135052   68078
7/17/23 0:00    389630-135054   70450
7/31/23 0:00    389629-135053   50228
7/28/23 0:00    389629-135053   65580
7/13/23 0:00    389629-135053   69155
7/23/23 0:00    389630-135054   70885
7/14/23 0:00    389630-135054   67892
7/19/23 0:00    389634-135058   73446
7/27/23 0:00    389633-135057   60125
7/28/23 0:00    389633-135057   64199
7/29/23 0:00    389631-135055   33164
7/16/23 0:00    389631-135055   73831
7/24/23 0:00    389632-135056   70333
7/16/23 0:00    389632-135056   68069
7/18/23 0:00    389629-135053   67103
7/24/23 0:00    389631-135055   73852
7/20/23 0:00    389630-135054   72357
7/15/23 0:00    389630-135054   70673
7/12/23 0:00    389630-135054   57477
7/29/23 0:00    389629-135053   18198
7/14/23 0:00    389631-135055   63845
8/1/23 0:00 389632-135056   12744
7/28/23 0:00    389634-135058   67665
7/25/23 0:00    389633-135057   68534
7/12/23 0:00    389629-135053   53612
8/1/23 0:00 389628-135052   12663
7/20/23 0:00    389628-135052   75662
7/26/23 0:00    389631-135055   75014
7/22/23 0:00    389632-135056   68687
7/13/23 0:00    389630-135054   72816
7/23/23 0:00    389629-135053   68272

Solution

The issue with the existing code is .count mirrors the pandas.Series.count method. You may not use . notation if the column name mirrors a pandas method.
- df[df.key == k]['count'] not df[df.key == k].count
Additionally, using plt.plot will still cause issues, as seen in this plot. plt.plot also requires sorting x, and y relative to x.

One option is to use sns.relplot with kind='line', or use sns.lineplot.

import seaborn as sns

g = sns.relplot(kind='line', data=df, x='event_date', y='count', hue='key', height=7.5, aspect=1)

To iteratively create the plot by selecting each group, create a figure and Axes to repeatable plot each group onto.

fig, ax = plt.subplots(figsize=(10, 10))
for g in df.key.unique():
    df[df.key.eq(g)].plot(x='event_date', y='count', ax=ax, label=g)

Use pandas.DataFrame.pivot to reshape from a long to wide form and plot without a loop.
Use pandas.DataFrame.pivot_table to aggregate data if the dates in each group have more than one value.

# pivot the dataframe into a wide format
dfp = df.pivot(index='event_date', columns='key', values='count')

# and then plot without a loop
ax = dfp.plot(figsize=(10, 10))

Sample DataFrame

import pandas as pd

data = {'event_date': [pd.Timestamp('2023-07-23 00:00:00'), pd.Timestamp('2023-07-28 00:00:00'), pd.Timestamp('2023-07-12 00:00:00'), pd.Timestamp('2023-07-18 00:00:00'), pd.Timestamp('2023-07-26 00:00:00'), pd.Timestamp('2023-07-27 00:00:00'), pd.Timestamp('2023-07-20 00:00:00'), pd.Timestamp('2023-07-15 00:00:00'), pd.Timestamp('2023-07-23 00:00:00'), pd.Timestamp('2023-07-20 00:00:00'), pd.Timestamp('2023-07-21 00:00:00'), pd.Timestamp('2023-07-25 00:00:00'), pd.Timestamp('2023-07-15 00:00:00'), pd.Timestamp('2023-07-12 00:00:00'), pd.Timestamp('2023-07-14 00:00:00'), pd.Timestamp('2023-07-25 00:00:00'), pd.Timestamp('2023-07-15 00:00:00'), pd.Timestamp('2023-07-18 00:00:00'), pd.Timestamp('2023-07-17 00:00:00'), pd.Timestamp('2023-07-20 00:00:00'), pd.Timestamp('2023-07-15 00:00:00'), pd.Timestamp('2023-07-30 00:00:00'), pd.Timestamp('2023-07-21 00:00:00'), pd.Timestamp('2023-07-12 00:00:00'), pd.Timestamp('2023-07-19 00:00:00'), pd.Timestamp('2023-07-21 00:00:00'), pd.Timestamp('2023-07-18 00:00:00'), pd.Timestamp('2023-08-01 00:00:00'), pd.Timestamp('2023-07-17 00:00:00'), pd.Timestamp('2023-07-25 00:00:00'), pd.Timestamp('2023-07-17 00:00:00'), pd.Timestamp('2023-07-12 00:00:00'), pd.Timestamp('2023-07-18 00:00:00'), pd.Timestamp('2023-07-24 00:00:00'), pd.Timestamp('2023-07-30 00:00:00'), pd.Timestamp('2023-07-26 00:00:00'), pd.Timestamp('2023-07-30 00:00:00'), pd.Timestamp('2023-07-22 00:00:00'), pd.Timestamp('2023-07-18 00:00:00'), pd.Timestamp('2023-07-22 00:00:00'), pd.Timestamp('2023-07-18 00:00:00'), pd.Timestamp('2023-07-16 00:00:00'), pd.Timestamp('2023-07-14 00:00:00'), pd.Timestamp('2023-07-24 00:00:00'), pd.Timestamp('2023-07-16 00:00:00'), pd.Timestamp('2023-07-19 00:00:00'), pd.Timestamp('2023-07-17 00:00:00'), pd.Timestamp('2023-07-13 00:00:00'), pd.Timestamp('2023-07-31 00:00:00'), pd.Timestamp('2023-07-26 00:00:00'), pd.Timestamp('2023-08-01 00:00:00'), pd.Timestamp('2023-07-21 00:00:00'), pd.Timestamp('2023-07-30 00:00:00'), pd.Timestamp('2023-07-19 00:00:00'), pd.Timestamp('2023-08-01 00:00:00'), pd.Timestamp('2023-07-21 00:00:00'), pd.Timestamp('2023-07-29 00:00:00'), pd.Timestamp('2023-07-20 00:00:00'), pd.Timestamp('2023-07-24 00:00:00'), pd.Timestamp('2023-07-27 00:00:00'), pd.Timestamp('2023-07-31 00:00:00'), pd.Timestamp('2023-07-22 00:00:00'), pd.Timestamp('2023-07-23 00:00:00'), pd.Timestamp('2023-07-25 00:00:00'), pd.Timestamp('2023-07-29 00:00:00'), pd.Timestamp('2023-07-13 00:00:00'), pd.Timestamp('2023-07-13 00:00:00'), pd.Timestamp('2023-07-19 00:00:00'), pd.Timestamp('2023-07-23 00:00:00'), pd.Timestamp('2023-07-27 00:00:00'), pd.Timestamp('2023-07-26 00:00:00'), pd.Timestamp('2023-07-17 00:00:00'), pd.Timestamp('2023-07-15 00:00:00'), pd.Timestamp('2023-07-29 00:00:00'), pd.Timestamp('2023-07-27 00:00:00'), pd.Timestamp('2023-07-28 00:00:00'), pd.Timestamp('2023-07-23 00:00:00'), pd.Timestamp('2023-07-30 00:00:00'), pd.Timestamp('2023-07-25 00:00:00'), pd.Timestamp('2023-07-31 00:00:00'), pd.Timestamp('2023-07-24 00:00:00'), pd.Timestamp('2023-07-13 00:00:00'), pd.Timestamp('2023-07-27 00:00:00'), pd.Timestamp('2023-08-01 00:00:00'), pd.Timestamp('2023-07-26 00:00:00'), pd.Timestamp('2023-07-14 00:00:00'), pd.Timestamp('2023-07-30 00:00:00'), pd.Timestamp('2023-07-29 00:00:00'), pd.Timestamp('2023-07-14 00:00:00'), pd.Timestamp('2023-07-30 00:00:00'), pd.Timestamp('2023-07-17 00:00:00'), pd.Timestamp('2023-07-24 00:00:00'), pd.Timestamp('2023-07-28 00:00:00'), pd.Timestamp('2023-07-25 00:00:00'), pd.Timestamp('2023-07-21 00:00:00'), pd.Timestamp('2023-07-20 00:00:00'), pd.Timestamp('2023-07-22 00:00:00'), pd.Timestamp('2023-07-19 00:00:00'), pd.Timestamp('2023-07-28 00:00:00'), pd.Timestamp('2023-07-29 00:00:00'), pd.Timestamp('2023-08-01 00:00:00'), pd.Timestamp('2023-07-16 00:00:00'), pd.Timestamp('2023-07-13 00:00:00'), pd.Timestamp('2023-07-16 00:00:00'), pd.Timestamp('2023-07-31 00:00:00'), pd.Timestamp('2023-07-26 00:00:00'), pd.Timestamp('2023-07-31 00:00:00'), pd.Timestamp('2023-07-22 00:00:00'), pd.Timestamp('2023-07-15 00:00:00'), pd.Timestamp('2023-07-31 00:00:00'), pd.Timestamp('2023-07-19 00:00:00'), pd.Timestamp('2023-07-22 00:00:00'), pd.Timestamp('2023-07-14 00:00:00'), pd.Timestamp('2023-07-12 00:00:00'), pd.Timestamp('2023-07-21 00:00:00'), pd.Timestamp('2023-07-16 00:00:00'), pd.Timestamp('2023-07-27 00:00:00'), pd.Timestamp('2023-07-17 00:00:00'), pd.Timestamp('2023-07-31 00:00:00'), pd.Timestamp('2023-07-28 00:00:00'), pd.Timestamp('2023-07-13 00:00:00'), pd.Timestamp('2023-07-23 00:00:00'), pd.Timestamp('2023-07-14 00:00:00'), pd.Timestamp('2023-07-19 00:00:00'), pd.Timestamp('2023-07-27 00:00:00'), pd.Timestamp('2023-07-28 00:00:00'), pd.Timestamp('2023-07-29 00:00:00'), pd.Timestamp('2023-07-16 00:00:00'), pd.Timestamp('2023-07-24 00:00:00'), pd.Timestamp('2023-07-16 00:00:00'), pd.Timestamp('2023-07-18 00:00:00'), pd.Timestamp('2023-07-24 00:00:00'), pd.Timestamp('2023-07-20 00:00:00'), pd.Timestamp('2023-07-15 00:00:00'), pd.Timestamp('2023-07-12 00:00:00'), pd.Timestamp('2023-07-29 00:00:00'), pd.Timestamp('2023-07-14 00:00:00'), pd.Timestamp('2023-08-01 00:00:00'), pd.Timestamp('2023-07-28 00:00:00'), pd.Timestamp('2023-07-25 00:00:00'), pd.Timestamp('2023-07-12 00:00:00'), pd.Timestamp('2023-08-01 00:00:00'), pd.Timestamp('2023-07-20 00:00:00'), pd.Timestamp('2023-07-26 00:00:00'), pd.Timestamp('2023-07-22 00:00:00'), pd.Timestamp('2023-07-13 00:00:00'), pd.Timestamp('2023-07-23 00:00:00')],
        'key': ['389628-135052', '389628-135052', '389631-135055', '389632-135056', '389632-135056', '389630-135054', '389632-135056', '389632-135056', '389634-135058', '389629-135053', '389631-135055', '389629-135053', '389629-135053', '389633-135057', '389634-135058', '389628-135052', '389631-135055', '389631-135055', '389632-135056', '389633-135057', '389633-135057', '389630-135054', '389630-135054', '389632-135056', '389633-135057', '389633-135057', '389628-135052', '389631-135055', '389629-135053', '389634-135058', '389634-135058', '389628-135052', '389630-135054', '389629-135053', '389629-135053', '389630-135054', '389632-135056', '389634-135058', '389633-135057', '389633-135057', '389634-135058', '389633-135057', '389633-135057', '389633-135057', '389629-135053', '389628-135052', '389631-135055', '389631-135055', '389634-135058', '389629-135053', '389633-135057', '389628-135052', '389628-135052', '389630-135054', '389630-135054', '389632-135056', '389633-135057', '389631-135055', '389630-135054', '389632-135056', '389630-135054', '389629-135053', '389631-135055', '389632-135056', '389630-135054', '389632-135056', '389633-135057', '389631-135055', '389632-135056', '389634-135058', '389633-135057', '389633-135057', '389628-135052', '389628-135052', '389631-135055', '389631-135055', '389633-135057', '389634-135058', '389631-135055', '389628-135052', '389634-135058', '389634-135058', '389629-135053', '389629-135053', '389628-135052', '389628-135052', '389631-135055', '389634-135058', '389629-135053', '389633-135057', '389628-135052', '389628-135052', '389630-135054', '389630-135054', '389629-135053', '389634-135058', '389631-135055', '389632-135056', '389632-135056', '389632-135056', '389634-135058', '389628-135052', '389628-135052', '389630-135054', '389632-135056', '389634-135058', '389631-135055', '389630-135054', '389634-135058', '389633-135057', '389629-135053', '389628-135052', '389632-135056', '389634-135058', '389634-135058', '389634-135058', '389628-135052', '389630-135054', '389629-135053', '389629-135053', '389629-135053', '389630-135054', '389630-135054', '389634-135058', '389633-135057', '389633-135057', '389631-135055', '389631-135055', '389632-135056', '389632-135056', '389629-135053', '389631-135055', '389630-135054', '389630-135054', '389630-135054', '389629-135053', '389631-135055', '389632-135056', '389634-135058', '389633-135057', '389629-135053', '389628-135052', '389628-135052', '389631-135055', '389632-135056', '389630-135054', '389629-135053'],
        'count': [74858, 75139, 60910, 68850, 33704, 119679, 71281, 68854, 69020, 59536, 71065, 66887, 66150, 53096, 62948, 74872, 73870, 74548, 68402, 54665, 64637, 123113, 67368, 55618, 70942, 68221, 76602, 13252, 64287, 68104, 66301, 61841, 71472, 68495, 122907, 26650, 134425, 62225, 61047, 60926, 67725, 64254, 61383, 66471, 66548, 75846, 73452, 82725, 41786, 68862, 12333, 72381, 77991, 68765, 12798, 66499, 16644, 74593, 72015, 98245, 56117, 62669, 74936, 69935, 23579, 71917, 67979, 74154, 71347, 57570, 60073, 66860, 75962, 69251, 69051, 74231, 66237, 130063, 73097, 75428, 69958, 72563, 63235, 12444, 80514, 72334, 130862, 21234, 62070, 117653, 74903, 76074, 58201, 70594, 70020, 59776, 73678, 68493, 69294, 16416, 12202, 74739, 78198, 70980, 50267, 77612, 45171, 70867, 67374, 50583, 72029, 75503, 65704, 54886, 70547, 68285, 68078, 70450, 50228, 65580, 69155, 70885, 67892, 73446, 60125, 64199, 33164, 73831, 70333, 68069, 67103, 73852, 72357, 70673, 57477, 18198, 63845, 12744, 67665, 68534, 53612, 12663, 75662, 75014, 68687, 72816, 68272]}
df = pd.DataFrame(data)

`dfp`

key         389628-135052  389629-135053  389630-135054  389631-135055  389632-135056  389633-135057  389634-135058
event_date                                                                                                         
2023-07-12          61841          53612          57477          60910          55618          53096          54886
2023-07-13          78198          69155          72816          82725          71917          67979          72563
2023-07-14          72334          62070          67892          63845          65704          61383          62948
2023-07-15          75962          66150          70673          73870          68854          64637          67374
2023-07-16          74739          66548          70980          73831          68069          64254          68285
2023-07-17          74903          64287          70450          73452          68402          66860          66301
2023-07-18          76602          67103          71472          74548          68850          61047          67725
2023-07-19          75846          72029          68765          74154          68493          70942          73446
2023-07-20          75662          59536          72357          74593          71281          54665          59776
2023-07-21          72381          70020          67368          71065          66499          68221          70547
2023-07-22          75503          62669          70867          73678          68687          60926          62225
2023-07-23          74858          68272          70885          74936          71347          66237          69020
2023-07-24          76074          68495          72015          73852          70333          66471          69958
2023-07-25          74872          66887          70594          73097          69935          68534          68104
2023-07-26          80514          68862          26650          75014          33704          60073          77612
2023-07-27          68078          63235         119679          69051          98245          60125          57570
2023-07-28          75139          65580          58201          74231          69294          64199          67665
2023-07-29          69251          18198          23579          33164          16416          16644          21234
2023-07-30          77991         122907         123113         130862         134425         117653         130063
2023-07-31          75428          50228          56117          45171          50267          50583          41786
2023-08-01          12663          12444          12798          13252          12744          12333          12202

Answered By - Trenton McKinney

This Answer collected from stackoverflow and tested by PythonFixing community admins, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Saturday, December 23, 2023

[FIXED] plotting timeseries line graph for unique values in a column

Issue

Solution

Sample DataFrame

`dfp`

0 comments:

Post a Comment

Popular Posts

Labels