pythonpandasmatplotlibaxis

unexpected reversed secondary y axis on dataframe plot


I'm trying to plot a electrical consumption, first in mA with a date, and with secondary axis in W with julian day. I refered to this matplotlib article, and even if the example works perfectly, I can't figrure where mine differ of it. Because my secondary y axis is inverted as it's supposed to be.

Here my prog :

import pandas as pd
import matplotlib.pyplot as plt
import glob
import os
import matplotlib.dates as mdates
import datetime


path = '[...]TEMPORARY/CR1000_test_intergration/'
all_files = glob.glob(os.path.join(path , "*.dat"))
li = []
for filename in all_files:
    df = pd.read_csv(filename,
                     skiprows=[0,2,3], 
                     header=0, 
                     index_col=0
                     )
    li.append(df)

frame = pd.concat(li, axis=0)
frame=frame.sort_values('TIMESTAMP')
frame.fillna(0)
frame.index = pd.to_datetime(frame.index,format="%Y-%m-%d %H:%M:%S")

st_date = pd.to_datetime("2024-05-12 23:30:00", format='%Y-%m-%d %H:%M:%S')
en_date = frame.index[-1]


mask = frame.loc[st_date:en_date].index
window1 = frame.loc[(frame.index >= st_date) & (frame.index <= en_date)]

#PLOT   
fig, ax = plt.subplots(1,1, figsize=(20,6), dpi=150, sharex=True)
fig.suptitle('CUBE CONSO',fontsize=14, fontweight='bold')
fig.subplots_adjust(hspace=0)

plt.xticks(rotation=30)
ax.grid(True)
ax.xaxis.set_major_locator(mdates.HourLocator(interval=6))
ax.xaxis.set_major_formatter(mdates.DateFormatter('%m-%d | %H:%M')) 
ax.set_ylabel('A')

ax.plot(window1.index,window1['R2_voltage_Avg'], color='r', linewidth=2)

def date2yday(x):
    y = x - mdates.date2num(datetime.datetime(2024, 1, 1))
    return y

def yday2date(x):
    y = x + mdates.date2num(datetime.datetime(2024, 1, 1))
    return y

secax_x = ax.secondary_xaxis(
    'top', functions=(date2yday, yday2date))
secax_x.set_xlabel('julian day [2024]')

def ma_to_w(x):
    return (x * 12.5)

def w_to_ma(x):
    return (12.5 / (x+0.0001)) #avoid divide by 0

secax_y = ax.secondary_yaxis(
    'right', functions=(ma_to_w,w_to_ma))
secax_y.set_ylabel('W')

And here a sample of data (the concatened dataframe) :

TIMESTAMP   RECORD  R1_voltage_Avg  R2_voltage_Avg  out1_voltage_Avg    out2_voltage_Avg
2024-05-13 00:00:00 34155   0.286   0.099   78.56   3.949
2024-05-13 00:01:00 34156   0.797   0.104   20.91   0.057
2024-05-13 00:02:00 34157   0.599   0.091   41.6    0.966
2024-05-13 00:03:00 34158   0.519   0.097   27.76   0.824
2024-05-13 00:04:00 34159   0.814   0.096   27.39   0.455
2024-05-13 00:05:00 34160   0.828   0.101   19.75   0.398
2024-05-13 00:06:00 34161   0.664   0.098   58.36   1.193
2024-05-13 00:07:00 34162   0.081   0.1 49.98   1.023
2024-05-13 00:08:00 34163   0.414   0.098   50.26   0.739
2024-05-13 00:09:00 34164   0.708   0.101   45.97   0.568
2024-05-13 00:10:00 34165   0.698   0.099   82.2    3.552
2024-05-13 00:11:00 34166   0.524   0.101   40.6    -0.54
2024-05-13 00:12:00 34167   0.793   0.093   63.76   3.864
2024-05-13 00:13:00 34168   0.72    0.086   12.76   -0.256
2024-05-13 00:14:00 34169   0.564   0.096   23.44   0.881
2024-05-13 00:15:00 34170   0.67    0.094   30.17   2.33

And finally a plot :

enter image description here


Solution

  • It is unclear what you are expecting, but here is what I would have done:

    import pandas as pd
    import matplotlib.pyplot as plt
    import matplotlib.dates as mdates
    import datetime
    
    # Sample data
    data = {
        'TIMESTAMP': [
            '2024-05-13 00:00:00', '2024-05-13 00:01:00', '2024-05-13 00:02:00', '2024-05-13 00:03:00', '2024-05-13 00:04:00',
            '2024-05-13 00:05:00', '2024-05-13 00:06:00', '2024-05-13 00:07:00', '2024-05-13 00:08:00', '2024-05-13 00:09:00',
            '2024-05-13 00:10:00', '2024-05-13 00:11:00', '2024-05-13 00:12:00', '2024-05-13 00:13:00', '2024-05-13 00:14:00',
            '2024-05-13 00:15:00'
        ],
        'R2_voltage_Avg': [0.099, 0.104, 0.091, 0.097, 0.096, 0.101, 0.098, 0.1, 0.098, 0.101, 0.099, 0.101, 0.093, 0.086, 0.096, 0.094]
    }
    
    df = pd.DataFrame(data)
    df['TIMESTAMP'] = pd.to_datetime(df['TIMESTAMP'])
    df.set_index('TIMESTAMP', inplace=True)
    
    fig, ax = plt.subplots(1, 1, figsize=(20, 6), dpi=150, sharex=True)
    fig.suptitle('CUBE CONSO', fontsize=14, fontweight='bold')
    fig.subplots_adjust(hspace=0)
    
    plt.xticks(rotation=30)
    ax.grid(True)
    ax.xaxis.set_major_locator(mdates.HourLocator(interval=1))
    ax.xaxis.set_major_formatter(mdates.DateFormatter('%m-%d | %H:%M')) 
    ax.set_ylabel('A')
    
    ax.plot(df.index, df['R2_voltage_Avg'], color='r', linewidth=2)
    
    def date2yday(x):
        y = x - mdates.date2num(datetime.datetime(2024, 1, 1))
        return y
    
    def yday2date(x):
        y = x + mdates.date2num(datetime.datetime(2024, 1, 1))
        return y
    
    secax_x = ax.secondary_xaxis('top', functions=(date2yday, yday2date))
    secax_x.set_xlabel('Julian day [2024]')
    
    def ma_to_w(x):
        voltage = 12.5  
        return (x / 1000) * voltage  
    
    def w_to_ma(x):
        voltage = 12.5  
        return (x / voltage) * 1000  
    
    secax_y = ax.secondary_yaxis('right', functions=(ma_to_w, w_to_ma))
    secax_y.set_ylabel('W')
    
    plt.show()
    

    which gives

    enter image description here