python pandas: Shape of passed values is (84, 1), indices imply (84, 84)

Question

I would like to insert values from one dataframe (imported from an excel) to a bigger one I have created in python. It is similar to a VLOOKUP function in Excel. After importing from excel and filtering only the data I want, I am not able to merge them since an error message shows ('Shape of passed values is (84, 1), indices imply (84, 84)').

This is the table I have created myself to collect the data from excel

import pandas as pd

modelo = [2032,  *range(2056, 2060, 1),*range(2111,2151,1),*range(2155,2157,1)] 
color=['negro','cuero','rojo','taupe','camel','beige','burdeos']
talla=['36','37','38','39','40']

index=pd.MultiIndex.from_product([modelo,color,talla ], names=["modelo",'color', "talla"])

df0=pd.DataFrame(data=0,columns=['ucendo','fran',   'car',  'miguel',   'ivan', 'puerta',   'caye', 'ofi',  'oscar','portugal', 'Total'],index=index)

print(df0)
                      ucendo  fran  car  miguel  ...  ofi  oscar  portugal  Total
modelo color   talla                             ...                             
2032   negro   36          0     0    0       0  ...    0      0         0      0
               37          0     0    0       0  ...    0      0         0      0
               38          0     0    0       0  ...    0      0         0      0
               39          0     0    0       0  ...    0      0         0      0
               40          0     0    0       0  ...    0      0         0      0
                     ...   ...  ...     ...  ...  ...    ...       ...    ...
2156   burdeos 36          0     0    0       0  ...    0      0         0      0
               37          0     0    0       0  ...    0      0         0      0
               38          0     0    0       0  ...    0      0         0      0
               39          0     0    0       0  ...    0      0         0      0
               40          0     0    0       0  ...    0      0         0      0

[1645 rows x 11 columns]

The following is the excel file from which I would like to get the values and insert them into the previous dataframe.

df1=pd.read_excel('Book2.xlsx',skiprows=1,usecols=[*range(2,11,1)],header=1,index_col=[0,1],)
df1=df1.drop(index=(' ',' ')) #data cleaning
df1=df1.stack()  #to get the third index I need to merge
df1.index.names=["modelo",'color', "talla"]#so that index names are the same in both dataframes
print(df1)
modelo  color  talla
2032    BEIG   35       1
               36       2
               37       3
               38       4
               39       5
                       ..
2117    NEGRO  37       3
               38       4
               39       5
               40       6
               41       7
Length: 84, dtype: object

#I have also tried five ways to merge/join them:

s=pd.DataFrame(data = df1.values , columns = df1.index).transpose()
df0.join(s)

#pd.merge(df0,s, left_index=True,right_index=True)
#df0['e'] = df1
#df1 = df1.assign(e=df1.values)
#df=df0.merge(df1.to_frame(),left_index=True, right_index=True)

No matter which method I use to convert the series (which used to be a dataframe but became a series after .stack()), the same error message shows when I try to merge/join: 'Shape of passed values is (84, 1), indices imply (84, 84)'.

If there is an easier way out of this, please let me know. Thank you.

question from:https://stackoverflow.com/questions/66068602/python-pandas-shape-of-passed-values-is-84-1-indices-imply-84-84

Answer 1

Waitting for answers