[FIXED] How to plot when columns are rows of dictionaries with a numpy array

Issue

I have a dataframe with a dict inside each cell that contains data like the pic attached, I want to plot column 1 and 2 (index order) to get something like this:

n = len(df3)
fig,ax = plt.subplots() 
for i in range(n):
       ax.plot([*df3[i][:,1].values()], [*df3[i][:,2].values()],label=df3[i][:,0])
plt.show()

I tried the code below but isnt working, any guide ples thanks!!

Here is the pk file: https://www.mediafire.com/file/6zzzrd01k3hyywr/df3result.pk/file

Sample data

data =\
{'Time': [pd.Timestamp('2018-01-27 00:00:00'), pd.Timestamp('2018-05-07 00:00:00'), pd.Timestamp('2018-08-15 00:00:00'), pd.Timestamp('2018-11-23 00:00:00'), pd.Timestamp('2019-03-03 00:00:00')], 'Liqwt': [{'Liqwt': np.array([   18.1123,   969.674 ,  1921.23  ,  2872.8   ,  3824.36  ,
        4775.92  ,  5727.48  ,  6679.04  ,  7630.6   ,  8582.17  ,
        9533.73  , 10485.3   , 11436.8   , 12388.4   , 13340.    ,
       14291.5   , 15243.1   , 16194.7   , 17146.2   , 18097.8   ])}, {'Liqwt': np.array([   40.  ,  2141.47,  4242.95,  6344.42,  8445.89, 10547.4 ,
       12648.8 , 14750.3 , 16851.8 , 18953.3 , 21054.7 , 23156.2 ,
       25257.7 , 27359.2 , 29460.6 , 31562.1 , 33663.6 , 35765.  ,
       37866.5 , 39968.  ])}, {'Liqwt': np.array([   38.0128,  2035.08  ,  4032.16  ,  6029.23  ,  8026.3   ,
       10023.4   , 12020.4   , 14017.5   , 16014.6   , 18011.7   ,
       20008.7   , 22005.8   , 24002.9   , 25999.9   , 27997.    ,
       29994.1   , 31991.2   , 33988.2   , 35985.3   , 37982.4   ])}, {'Liqwt': np.array([   40.  ,  2141.47,  4242.95,  6344.42,  8445.89, 10547.4 ,
       12648.8 , 14750.3 , 16851.8 , 18953.3 , 21054.7 , 23156.2 ,
       25257.7 , 27359.2 , 29460.6 , 31562.1 , 33663.6 , 35765.  ,
       37866.5 , 39968.  ])}, {'Liqwt': np.array([   40.  ,  2141.47,  4242.95,  6344.42,  8445.89, 10547.4 ,
       12648.8 , 14750.3 , 16851.8 , 18953.3 , 21054.7 , 23156.2 ,
       25257.7 , 27359.2 , 29460.6 , 31562.1 , 33663.6 , 35765.  ,
       37866.5 , 39968.  ])}], 'IPRwt': [{'IPRwt': np.array([3919.98 , 3782.97 , 3641.74 , 3496.72 , 3345.06 , 3185.46 ,
       3019.55 , 2846.7  , 2667.38 , 2482.27 , 2289.33 , 2087.15 ,
       1874.18 , 1647.9  , 1403.7  , 1127.59 ,  812.477,  436.561,
          0.   ,    0.   ])}, {'IPRwt': np.array([3823.82 , 3708.6  , 3578.81 , 3441.95 , 3299.97 , 3153.51 ,
       3002.86 , 2848.15 , 2689.46 , 2526.85 , 2360.33 , 2190.32 ,
       2017.   , 1839.47 , 1656.83 , 1469.83 , 1276.69 , 1073.84 ,
        854.08 ,  600.952])}, {'IPRwt': np.array([ 3.74833e+03,  3.61396e+03,  3.46258e+03,  3.30363e+03,
        3.13850e+03,  2.96272e+03,  2.77724e+03,  2.58063e+03,
        2.37299e+03,  2.15353e+03,  1.92058e+03,  1.66852e+03,
        1.38785e+03,  1.05660e+03,  7.01630e+02,  1.92702e+02,
        0.00000e+00,  0.00000e+00,  0.00000e+00, -1.00000e+00])}, {'IPRwt': np.array([3722.   , 3637.19 , 3522.64 , 3392.73 , 3250.45 , 3097.7  ,
       2935.76 , 2763.18 , 2580.25 , 2386.64 , 2179.46 , 1955.4  ,
       1709.25 , 1430.54 , 1112.86 ,  780.77 ,  327.827,    0.   ,
          0.   ,    0.   ])}, {'IPRwt': np.array([3581.5  , 3486.05 , 3372.4  , 3250.96 , 3123.71 , 2991.04 ,
       2852.21 , 2706.23 , 2552.53 , 2389.3  , 2216.65 , 2033.92 ,
       1839.21 , 1629.9  , 1400.3  , 1138.24 ,  838.932,  514.519,
          0.   ,    0.   ])}], 'LiqNod': [{'LiqNod': np.array([   40.  ,  2141.47,  4242.95,  6344.42,  8445.89, 10547.4 ,
       12648.8 , 14750.3 , 16851.8 , 18953.3 , 21054.7 , 23156.2 ,
       25257.7 , 27359.2 , 29460.6 , 31562.1 , 33663.6 , 35765.  ,
       37866.5 , 39968.  ])}, {'LiqNod': np.array([   38.0128,  2035.08  ,  4032.16  ,  6029.23  ,  8026.3   ,
       10023.4   , 12020.4   , 14017.5   , 16014.6   , 18011.7   ,
       20008.7   , 22005.8   , 24002.9   , 25999.9   , 27997.    ,
       29994.1   , 31991.2   , 33988.2   , 35985.3   , 37982.4   ])}, {'LiqNod': np.array([   40.  ,  2141.47,  4242.95,  6344.42,  8445.89, 10547.4 ,
       12648.8 , 14750.3 , 16851.8 , 18953.3 , 21054.7 , 23156.2 ,
       25257.7 , 27359.2 , 29460.6 , 31562.1 , 33663.6 , 35765.  ,
       37866.5 , 39968.  ])}, {'LiqNod': np.array([   40.  ,  2141.47,  4242.95,  6344.42,  8445.89, 10547.4 ,
       12648.8 , 14750.3 , 16851.8 , 18953.3 , 21054.7 , 23156.2 ,
       25257.7 , 27359.2 , 29460.6 , 31562.1 , 33663.6 , 35765.  ,
       37866.5 , 39968.  ])}, {'LiqNod': np.array([   40.  ,  2141.47,  4242.95,  6344.42,  8445.89, 10547.4 ,
       12648.8 , 14750.3 , 16851.8 , 18953.3 , 21054.7 , 23156.2 ,
       25257.7 , 27359.2 , 29460.6 , 31562.1 , 33663.6 , 35765.  ,
       37866.5 , 39968.  ])}], 'IPRNod': [{'IPRNod': np.array([4970.64, 4842.67, 4714.7 , 4586.73, 4458.76, 4330.79, 4202.82,
       4074.85, 3946.88, 3818.9 , 3690.93, 3562.96, 3434.99, 3307.02,
       3179.05, 3051.08, 2923.11, 2795.14, 2667.17, 2539.2 ])}, {'IPRNod': np.array([4862.36 , 4703.68 , 4545.01 , 4386.33 , 4227.48 , 4064.74 ,
       3896.13 , 3720.94 , 3538.32 , 3347.22 , 3146.31 , 2933.89 ,
       2707.67 , 2464.52 , 2199.87 , 1906.67 , 1572.86 , 1174.34 ,
        645.351, -261.368])}, {'IPRNod': np.array([4728.5 , 4609.39, 4488.23, 4364.17, 4236.96, 4106.37, 3972.09,
       3833.79, 3691.08, 3543.5 , 3390.5 , 3231.42, 3065.47, 2891.66,
       2708.71, 2515.01, 2308.36, 2085.72, 1842.6 , 1571.95])}, {'IPRNod': np.array([4647.81, 4532.68, 4417.56, 4302.43, 4187.31, 4072.18, 3957.06,
       3841.57, 3723.42, 3601.99, 3476.97, 3348.02, 3214.72, 3076.57,
       2933.  , 2783.26, 2626.47, 2461.49, 2286.82, 2100.47])}, {'IPRNod': np.array([4539.7  , 4402.95 , 4266.19 , 4129.43 , 3992.67 , 3855.73 ,
       3715.47 , 3570.37 , 3419.87 , 3263.34 , 3099.99 , 2928.84 ,
       2748.64 , 2557.8  , 2354.16 , 2134.76 , 1895.23 , 1628.76 ,
       1323.48 ,  954.945])}]}

df = pd.DataFrame(data)

# display(df)
        Time                                                                                                                                                                                           Liqwt                                                                                                                                                                                         IPRwt                                                                                                                                                                                           LiqNod                                                                                                                                                                                             IPRNod
0 2018-01-27   {'Liqwt': [18.1123, 969.674, 1921.23, 2872.8, 3824.36, 4775.92, 5727.48, 6679.04, 7630.6, 8582.17, 9533.73, 10485.3, 11436.8, 12388.4, 13340.0, 14291.5, 15243.1, 16194.7, 17146.2, 18097.8]}          {'IPRwt': [3919.98, 3782.97, 3641.74, 3496.72, 3345.06, 3185.46, 3019.55, 2846.7, 2667.38, 2482.27, 2289.33, 2087.15, 1874.18, 1647.9, 1403.7, 1127.59, 812.477, 436.561, 0.0, 0.0]}    {'LiqNod': [40.0, 2141.47, 4242.95, 6344.42, 8445.89, 10547.4, 12648.8, 14750.3, 16851.8, 18953.3, 21054.7, 23156.2, 25257.7, 27359.2, 29460.6, 31562.1, 33663.6, 35765.0, 37866.5, 39968.0]}      {'IPRNod': [4970.64, 4842.67, 4714.7, 4586.73, 4458.76, 4330.79, 4202.82, 4074.85, 3946.88, 3818.9, 3690.93, 3562.96, 3434.99, 3307.02, 3179.05, 3051.08, 2923.11, 2795.14, 2667.17, 2539.2]}
1 2018-05-07    {'Liqwt': [40.0, 2141.47, 4242.95, 6344.42, 8445.89, 10547.4, 12648.8, 14750.3, 16851.8, 18953.3, 21054.7, 23156.2, 25257.7, 27359.2, 29460.6, 31562.1, 33663.6, 35765.0, 37866.5, 39968.0]}  {'IPRwt': [3823.82, 3708.6, 3578.81, 3441.95, 3299.97, 3153.51, 3002.86, 2848.15, 2689.46, 2526.85, 2360.33, 2190.32, 2017.0, 1839.47, 1656.83, 1469.83, 1276.69, 1073.84, 854.08, 600.952]}  {'LiqNod': [38.0128, 2035.08, 4032.16, 6029.23, 8026.3, 10023.4, 12020.4, 14017.5, 16014.6, 18011.7, 20008.7, 22005.8, 24002.9, 25999.9, 27997.0, 29994.1, 31991.2, 33988.2, 35985.3, 37982.4]}  {'IPRNod': [4862.36, 4703.68, 4545.01, 4386.33, 4227.48, 4064.74, 3896.13, 3720.94, 3538.32, 3347.22, 3146.31, 2933.89, 2707.67, 2464.52, 2199.87, 1906.67, 1572.86, 1174.34, 645.351, -261.368]}
2 2018-08-15  {'Liqwt': [38.0128, 2035.08, 4032.16, 6029.23, 8026.3, 10023.4, 12020.4, 14017.5, 16014.6, 18011.7, 20008.7, 22005.8, 24002.9, 25999.9, 27997.0, 29994.1, 31991.2, 33988.2, 35985.3, 37982.4]}                 {'IPRwt': [3748.33, 3613.96, 3462.58, 3303.63, 3138.5, 2962.72, 2777.24, 2580.63, 2372.99, 2153.53, 1920.58, 1668.52, 1387.85, 1056.6, 701.63, 192.702, 0.0, 0.0, 0.0, -1.0]}    {'LiqNod': [40.0, 2141.47, 4242.95, 6344.42, 8445.89, 10547.4, 12648.8, 14750.3, 16851.8, 18953.3, 21054.7, 23156.2, 25257.7, 27359.2, 29460.6, 31562.1, 33663.6, 35765.0, 37866.5, 39968.0]}       {'IPRNod': [4728.5, 4609.39, 4488.23, 4364.17, 4236.96, 4106.37, 3972.09, 3833.79, 3691.08, 3543.5, 3390.5, 3231.42, 3065.47, 2891.66, 2708.71, 2515.01, 2308.36, 2085.72, 1842.6, 1571.95]}
3 2018-11-23    {'Liqwt': [40.0, 2141.47, 4242.95, 6344.42, 8445.89, 10547.4, 12648.8, 14750.3, 16851.8, 18953.3, 21054.7, 23156.2, 25257.7, 27359.2, 29460.6, 31562.1, 33663.6, 35765.0, 37866.5, 39968.0]}               {'IPRwt': [3722.0, 3637.19, 3522.64, 3392.73, 3250.45, 3097.7, 2935.76, 2763.18, 2580.25, 2386.64, 2179.46, 1955.4, 1709.25, 1430.54, 1112.86, 780.77, 327.827, 0.0, 0.0, 0.0]}    {'LiqNod': [40.0, 2141.47, 4242.95, 6344.42, 8445.89, 10547.4, 12648.8, 14750.3, 16851.8, 18953.3, 21054.7, 23156.2, 25257.7, 27359.2, 29460.6, 31562.1, 33663.6, 35765.0, 37866.5, 39968.0]}    {'IPRNod': [4647.81, 4532.68, 4417.56, 4302.43, 4187.31, 4072.18, 3957.06, 3841.57, 3723.42, 3601.99, 3476.97, 3348.02, 3214.72, 3076.57, 2933.0, 2783.26, 2626.47, 2461.49, 2286.82, 2100.47]}
4 2019-03-03    {'Liqwt': [40.0, 2141.47, 4242.95, 6344.42, 8445.89, 10547.4, 12648.8, 14750.3, 16851.8, 18953.3, 21054.7, 23156.2, 25257.7, 27359.2, 29460.6, 31562.1, 33663.6, 35765.0, 37866.5, 39968.0]}            {'IPRwt': [3581.5, 3486.05, 3372.4, 3250.96, 3123.71, 2991.04, 2852.21, 2706.23, 2552.53, 2389.3, 2216.65, 2033.92, 1839.21, 1629.9, 1400.3, 1138.24, 838.932, 514.519, 0.0, 0.0]}    {'LiqNod': [40.0, 2141.47, 4242.95, 6344.42, 8445.89, 10547.4, 12648.8, 14750.3, 16851.8, 18953.3, 21054.7, 23156.2, 25257.7, 27359.2, 29460.6, 31562.1, 33663.6, 35765.0, 37866.5, 39968.0]}     {'IPRNod': [4539.7, 4402.95, 4266.19, 4129.43, 3992.67, 3855.73, 3715.47, 3570.37, 3419.87, 3263.34, 3099.99, 2928.84, 2748.64, 2557.8, 2354.16, 2134.76, 1895.23, 1628.76, 1323.48, 954.945]}

Solution

• In the case of this data, the 'Time' column is datetime64[ns] Dtype, and the columns to be plotted, contain rows of dict with a np.array, the columns don't contain string literals.

1. Extract all the arrays from the dictionaries
   • Use .apply with .map(dict.values), and pd.Series.explode
   • .apply(lambda x: x.map(dict.values)) produces:
     • dict_values([array([ 18.1123, 969.674 , ... , 17146.2 , 18097.8 ])])
   • Each array is then extracted from dict_values(...) with .apply(pd.Series.explode).
2. Create colors for the plots, because the standard color cycler doesn't have enough unique colors. The easiest way is the use sns.color_palette. seaborn is a high-level API for matplotlib.
   • See creating over 20 unique legend colors using matplotlib for other alternatives.
   • Choosing Colormaps in Matplotlib & Choosing color palettes in seaborn

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

# load the data from the file, or use the sample df in the OP
df = pd.read_pickle('data/df3result.pk')

# 1.
df.loc[:, ['Liqwt', 'IPRwt', 'LiqNod', 'IPRNod']] = df.loc[:, ['Liqwt', 'IPRwt', 'LiqNod', 'IPRNod']].apply(lambda x: x.map(dict.values)).apply(pd.Series.explode)

# 2.
colors = sns.color_palette('husl', n_colors=len(df))

# plot
plt.rcParams.update({'font.size': 16})
fig, ax = plt.subplots(figsize=(12, 9)) 

# iterate through each row of the dataframe, zipped to the color for that row
for (_, (time, liqwt, iprwt)), color in zip(df[['Time', 'Liqwt', 'IPRwt']].iterrows(), colors):
    ax.plot(liqwt, iprwt, '.-', color=color, label=time.date())

ax.set(xlabel='Oil Rate, BOPD', ylabel='$P_{wf},psi$')
ax.legend(bbox_to_anchor=(1, 1.02), loc='upper left', frameon=False)
plt.show()

Answered By - Trenton McKinney