[FIXED] How to plot two panda data frames with different time-ticks on the x-axis in the same plot, while reformatting the ticks?

Issue

I want to plot two data frames together in one plot. It is time-series data that was recorded asynchronously with different timestamps. Here is my question about reformatting the ticks on the x-axis into human-readable form, with a working answer for one data frame.

For two data frames, this fails. I tried two approaches, which fail, but in different but maybe related ways:

import pandas as pd
import matplotlib.pyplot as plt


mydict = [{'time': 1651816500, 'b': 2},
          {'time': 1651816800, 'b': 200},
          {'time': 1651817100, 'b': 2000}]
mydf = pd.DataFrame(mydict)
plt.plot(pd.to_datetime(mydf['time'], unit='s').dt.strftime('%Y-%m-%d %H:%M'), mydf['b'])

yourdict = [{'time': 1651816490, 'b': 20},
          {'time': 1651816805, 'b': 170},
          {'time': 1651817105, 'b': 1000}]
yourdf = pd.DataFrame(yourdict)
plt.plot(pd.to_datetime(yourdf['time'], unit='s').dt.strftime('%Y-%m-%d %H:%M'), yourdf['b'])

start = mydf.iloc[0, 0]
end = mydf.iloc[-1, 0]
plt.xlim(start, end)

plt.show()

This creates an empty plot without data and no ticks on the x-axis.

import pandas as pd
import matplotlib.pyplot as plt


mydict = [{'time': 1651816500, 'b': 2},
          {'time': 1651816800, 'b': 200},
          {'time': 1651817100, 'b': 2000}]
mydf = pd.DataFrame(mydict)
plt.plot(pd.to_datetime(mydf['time'], unit='s').dt.strftime('%Y-%m-%d %H:%M'), mydf['b'])

yourdict = [{'time': 1651816490, 'b': 20},
          {'time': 1651816805, 'b': 170},
          {'time': 1651817105, 'b': 1000}]
yourdf = pd.DataFrame(yourdict)
plt.plot(pd.to_datetime(yourdf['time'], unit='s').dt.strftime('%Y-%m-%d %H:%M'), yourdf['b'])

start = mydf.iloc[0, 0]
end = mydf.iloc[-1, 0]
plt.xlim(start, end)

plt.show()

this fails with this traceback:

Traceback (most recent call last):
  File "/home/andreas/src/misc-aux-code/epoch_matplotlib_demo.py", line 9, in <module>
    ax = mydf.plot(pd.to_datetime(mydf['time'], unit='s').dt.strftime('%Y-%m-%d %H:%M'), mydf['b'])
  File "/home/andreas/src/misc-aux-code/venv/lib/python3.9/site-packages/pandas/plotting/_core.py", line 937, in __call__
    elif not isinstance(data[x], ABCSeries):
  File "/home/andreas/src/misc-aux-code/venv/lib/python3.9/site-packages/pandas/core/frame.py", line 3511, in __getitem__
    indexer = self.columns._get_indexer_strict(key, "columns")[1]
  File "/home/andreas/src/misc-aux-code/venv/lib/python3.9/site-packages/pandas/core/indexes/base.py", line 5782, in _get_indexer_strict
    self._raise_if_missing(keyarr, indexer, axis_name)
  File "/home/andreas/src/misc-aux-code/venv/lib/python3.9/site-packages/pandas/core/indexes/base.py", line 5842, in _raise_if_missing
    raise KeyError(f"None of [{key}] are in the [{axis_name}]")
KeyError: "None of [Index(['2022-05-06 05:55', '2022-05-06 06:00', '2022-05-06 06:05'], dtype='object')] are in the [columns]"

How can I work around this limitation? I would really prefer if the data was handled with epoch format internally and only once the ticks were "agreed upon", those were converted. For some reason it works well, to plot both data frames without reformatting the epoch times.

Solution

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

mydict = [{'time': 1651816500, 'b': 2},
          {'time': 1651816800, 'b': 200},
          {'time': 1651817100, 'b': 2000}]

yourdict = [{'time': 1651816490, 'b': 20},
          {'time': 1651816805, 'b': 170},
          {'time': 1651817105, 'b': 1000}]

df1 = pd.DataFrame(mydict)
df2 = pd.DataFrame(yourdict)

# converts the time column to proper pandas datetime
df1.time = pd.to_datetime(df1.time, unit='s')
df2.time = pd.to_datetime(df2.time, unit='s')
# Alternatively you could create new columns with something like:
# df1['timestamp'] = pd.to_datetime(df1.time, unit='s')

# Makes a new column in each dataframe to identify where it came from.
df1['df'] = 'mydict'
df2['df'] = 'yourdict'

# Combines the two dataframes into a single one.
df = pd.concat([df1, df2], ignore_index=True)

# Seaborn's lineplot, I use it here over matplotlib 
# because of the ability to define a 'hue'. 
sns.lineplot(data=df, x='time', y='b', hue='df') # swap time for timestamp here if you created a new column
plt.show()

Output:

Answered By - BeRT2me