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

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'?

CodePudding user response：

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()