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