How to create a pandas dataframe with 2 dataframes one as columns and one as rows

user_8794465 0
Viewed 162

I have a 2 dataframes as follow:

df1 = pd.DataFrame({'Barcode':[1,2,3,4],'Store':['s1','s2','s3','s4']})    

df2 = pd.DataFrame({'Date':['2020-10-10','2020-10-09','2020-10-08','2020-10-07','2020-10-06']})

How to have a dataframe which has df1 as rows and df2 as columns and consequently generate cells with null values.Something like below:

enter image description here

And the final step is to fill the cells with join on another table(df4):

df4 = pd.DataFrame({'Barcode':[1,2,3,4],'Store':['s1','s2','s3','s4'],'2020-10-10':[1,2,5,np.nan],'2020-10-09':[np.nan,2,3,0],'2020-10-08':[0,0,2,3],'2020-10-07':[np.nan,1,np.nan,2]})

Final df should look like bellow:

enter image description here

Any help is truly appreciated.

pandas merge join python
3 Answers

I hope I've understood your question right. You have 3 dataframes:

df1 = pd.DataFrame({'Barcode':[1,2,3,4],'Store':['s1','s2','s3','s4']})    
df2 = pd.DataFrame({'Date':['2020-10-10','2020-10-09','2020-10-08','2020-10-07','2020-10-06']})
df4 = pd.DataFrame({'Barcode':[1,2,3,4],'Store':['s1','s2','s3','s4'],'2020-10-10':[1,2,5,np.nan],'2020-10-09':[np.nan,2,3,0],'2020-10-08':[0,0,2,3],'2020-10-07':[np.nan,1,np.nan,2]})

Then:

df1 = pd.DataFrame(df1, columns= df1.columns.tolist() + df2['Date'].tolist())
df1 = df1.set_index('Barcode')
df4 = df4.set_index('Barcode')

print(df1.fillna(df4))

Prints:

        Store  2020-10-10  2020-10-09  2020-10-08  2020-10-07  2020-10-06
Barcode                                                                  
1          s1         1.0         NaN         0.0         NaN         NaN
2          s2         2.0         2.0         0.0         1.0         NaN
3          s3         5.0         3.0         2.0         NaN         NaN
4          s4         NaN         0.0         3.0         2.0         NaN
user_100359850

First create a temporary DataFrame:

wrk = pd.DataFrame('', index=pd.MultiIndex.from_frame(df1),
    columns=df2.Date.rename(None)); wrk

It is filled with empty strings, and has required column names from df2. For now Barcode and Store are index columns. This arrangement will be needed soon.

Then update it (in-place) with the data from df4:

wrk.update(df4.set_index(['Barcode', 'Store']))

And the last step is:

result = wrk.reset_index()

The result is:

   Barcode Store 2020-10-10 2020-10-09 2020-10-08 2020-10-07 2020-10-06
0        1    s1          1                     0                      
1        2    s2          2          2          0          1           
2        3    s3          5          3          2                      
3        4    s4                     0          3          2
user_73884770 
for item in df2.Date.tolist():
    df1[item] = np.nan

dfinal = df1.fillna(df4)
dfinal = dfinal.set_index('Barcode')
display(dfinal)
user_114503540
Related