import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
%matplotlib inline
import warnings
warnings.filterwarnings('ignore')

df = pd.DataFrame({
          'asin':list('ABCDA'),
          'rank':[1,1,2,3,3],
          'cate_name':['a','b','c','a','d'],
          'date':['2010-01-01','2010-01-03','2010-01-03','2010-01-04','2010-01-06']})
df

image.png

df['scaled_rank'] = (df['rank'] - df["rank"].max()).abs()
df

image.png

df.date = pd.to_datetime(df.date)
time_range = pd.DataFrame(pd.date_range('2010-01-01','2010-01-07',freq='D'), columns=['date']).set_index("date")
time_range

image.png

df = pd.pivot_table(df, columns='asin', index='date')
df

image.png

df = df.merge(time_range, how="right", left_index=True, right_index=True)
df[('scaled_rank',)] = df[('scaled_rank',)].fillna(0)
df.T

image.png