Plotting aggregated values from specific column of multiple dataframe indexed by timedate

Question

I have three dataframe as below:

import pandas as pd
labels=['1','2','3','Aggregated']
df1 = {'date_time': ["2022-10-06 17:23:11","2022-10-06 17:23:12","2022-10-06 17:23:13","2022-10-06 17:23:14","2022-10-06 17:23:15","2022-10-06 17:23:16"],
        'value': [4, 5, 6, 7, 8, 9]}

df2 = {'date_time': ["2022-10-06 17:23:13","2022-10-06 17:23:14","2022-10-06 17:23:15","2022-10-06 17:23:16","2022-10-06 17:23:17","2022-10-06 17:23:18"],
        'value': [4, 5, 6, 7, 8, 9]}

df3 = {'date_time': ["2022-10-06 17:23:16","2022-10-06 17:23:17","2022-10-06 17:23:18","2022-10-06 17:23:19","2022-10-06 17:23:20","2022-10-06 17:23:21"],
        'value': [4, 5, 6, 7, 8, 9]}

I need to create another dataframe df that contains all the datetime elements from all three df1,df2,df3 such that the common valued are summed up in-terms of common timestamps (excluding the millisecond parts) as shown below.

df=

{'date_time': ["2022-10-06 17:23:11","2022-10-06 17:23:12","2022-10-06 17:23:13","2022-10-06 17:23:14","2022-10-06 17:23:15","2022-10-06 17:23:16","2022-10-06 17:23:17","2022-10-06 17:23:18","2022-10-06 17:23:19","2022-10-06 17:23:20","2022-10-06 17:23:21"],
        'value': [4, 5, 6+4, 7+5, 8+6, 9+7+4, 8+5, 9+6, 7, 8, 9]}

For adding the columns I used following:

df = (pd.concat([df1,df2,df3],axis=0)).groupby('date_time')['value'].sum().reset_index()

For plotting I used following which results in df2 and df3 to time shift towards df1.

for dataFrame,number in zip([df1,df2,df3,df],labels):
    dataFrame["value"].plot(label=number)

How can I plot the three df1,df2,df3 without time shifting and also plot the aggregated df on the same plot for dataframe column 'value'?

@mozway labels=['1','2','3','Aggregated'] is plot legend labels. I have updated the code, my bad — H.Jamil
– H.Jamil, Commented Nov 3, 2022 at 0:12

Rabinzel · Accepted Answer · 2022-11-03 08:54:51Z

1

IIUC you search for something like this:

labels=["Aggregated","1","2","3"]
color_dict = {"Aggregated": "orange", "1": "darkred", "2": "green", "3": "blue"}

fig, ax = plt.subplots()
for i, (d, label) in enumerate(zip([df, df1, df2, df3], labels),1):
    ax.plot(d["date_time"], d["value"], lw=3/i, color=color_dict[label], label=label)
plt.setp(ax.get_xticklabels(), ha="right", rotation=45)
plt.legend()
plt.show()

edited Nov 3, 2022 at 8:54

answered Nov 3, 2022 at 8:49

Rabinzel

7,9533 gold badges12 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

Plotting aggregated values from specific column of multiple dataframe indexed by timedate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related