The average of two different data frames

Question

I have a df like:

material plant  Order                
24990   89952   4568789,5098710     
24990   89952   9448609,1007081     
166621  3062    18364103            
166621  3062    78309139            
240758  3062    55146035            
276009  3062    38501581,857542     

and df1 as:

material plant   Order      m1     m2      m3   m4   m5
24990     89952 4568789     0.123  0.214   0.0  0.0  0.0
24990     89952 5098710     1.000  0.363   0.0  0.0  0.0
24990     89952 9448609     0.0    0.345   0.0  1.0  0.0
24990     89952 1007081     0.0    0.756   0.0  1.0  0.0
166621    3062  18364103    0.0    0.0     0.0  0.0  0.0
166621    3062  78309139    0.0    1.0     0.0  0.0  0.0
240758    3062  55146035    1.0    1.0     1.0  0.0  0.0
276009    3062  38501581    1.0    1.0     1.0  0.0  0.0
276009    3062  38575428    1.0    1.0     1.0  0.0  0.0

I want to iterate through Order in df1, and when there is an Order match in df2, find the average value from m1 to m5. I want to achieve df2 as:

material plant  Order              avg m1 avgm2 avgm3 avgm4 avgm5
24990   89952   4568789,5098710    0.5615 0.2885 0.0   0.0   0.0
24990   89952   9448609,1007081     
166621  3062    18364103            
166621  3062    78309139            
240758  3062    55146035            
276009  3062    38501581,857542 

I am trying to use different ways to achieve df2, for example:

df2 = (df.groupby(df1, sort=False)['Order'].apply(lambda x: ','.split(x.astype(str)))
   .mean() 
   .reset_index()
.reindex(columns=df.columns))
 print (df2)

second:

group = df.columns[np.r_[0:3, 3:len(df.columns)]]
 res = df1.groupby(group)['Order'].apply(list).mean().reset_index()

But I'm not sure if this is the right way to get it.

Answer 1

You can use:

df = (df1.join(df1.set_index(['material','plant'], append=True)['Order']
          .str.split(',', expand=True)
          .stack()
          .astype(int)
          .reset_index(name='Order')
          .merge(df2, on=['material','plant','Order'], how='left') 
          .drop(['material','plant','Order','level_3'], axis=1)
          .groupby('level_0')
          .mean())
          )
print (df)
   material  plant            Order      m1      m2   m3   m4   m5
0     24990  89952  4568789,5098710  0.5615  0.2885  0.0  0.0  0.0
1     24990  89952  9448609,1007081  0.0000  0.5505  0.0  1.0  0.0
2    166621   3062         18364103  0.0000  0.0000  0.0  0.0  0.0
3    166621   3062         78309139  0.0000  1.0000  0.0  0.0  0.0
4    240758   3062         55146035  1.0000  1.0000  1.0  0.0  0.0
5    276009   3062  38501581,857542  1.0000  1.0000  1.0  0.0  0.0

Explanation

• First create DataFrame splitandstack
• Join the second DataFramec mergeand left join
• Remove unnecessary columns with drop
• Then aggregate mean
• Output joined series before the firstDataFrame

Detail

df3 = (df1.set_index(['material','plant'], append=True)['Order']
          .str.split(',', expand=True)
          .stack()
          .astype(int)
          .reset_index(name='Order')
          .merge(df2, on=['material','plant','Order'], how='left'))
print (df3)
   level_0  material  plant  level_3     Order     m1     m2   m3   m4   m5
0        0     24990  89952        0   4568789  0.123  0.214  0.0  0.0  0.0
1        0     24990  89952        1   5098710  1.000  0.363  0.0  0.0  0.0
2        1     24990  89952        0   9448609  0.000  0.345  0.0  1.0  0.0
3        1     24990  89952        1   1007081  0.000  0.756  0.0  1.0  0.0
4        2    166621   3062        0  18364103  0.000  0.000  0.0  0.0  0.0
5        3    166621   3062        0  78309139  0.000  1.000  0.0  0.0  0.0
6        4    240758   3062        0  55146035  1.000  1.000  1.0  0.0  0.0
7        5    276009   3062        0  38501581  1.000  1.000  1.0  0.0  0.0
8        5    276009   3062        1    857542    NaN    NaN  NaN  NaN  NaN

Answer 2

numpy .

# map Order to values with a dictionary
mapper = dict(zip(df1['Order'], df1[['m'+str(i) for i in range(1, 6)]].values))

# map comma-separated numbers to list of integers
df2_orders = [list(map(int, i)) for i in df2['Order'].str.split(',')]

# calculate mean
res = [np.mean([mapper.get(o, [0]*5) for o in order], axis=0).tolist() \
       for order in df2_orders]

# join results to dataframe
df2 = df2.join(pd.DataFrame(res, columns=['avg_m'+str(i) for i in range(1, 6)]))

, (, order 857542), , , 0s.

   material  plant            Order  avg_m1  avg_m2  avg_m3  avg_m4  avg_m5
0     24990  89952  4568789,5098710  0.5615  0.2885     0.0     0.0     0.0
1     24990  89952  9448609,1007081  0.0000  0.5505     0.0     1.0     0.0
2    166621   3062         18364103  0.0000  0.0000     0.0     0.0     0.0
3    166621   3062         78309139  0.0000  1.0000     0.0     0.0     0.0
4    240758   3062         55146035  1.0000  1.0000     1.0     0.0     0.0
5    276009   3062  38501581,857542  0.5000  0.5000     0.5     0.0     0.0