import pandas as pd
import os
import pyperclip

df = pd.read_excel(os.path.join('data_src','防卫精品.xlsx'),engine='openpyxl')
pyperclip.copy(df.to_csv(index=False))
df_havenot = df[df.have==0].drop(columns=['have'])

# pivoted to melt
df_melt = df_havenot.melt(
    id_vars=['id','val','rarity','name'],
    var_name='level_seq',
    value_name='level'
).drop(columns=['level_seq'])

level_sanity = pd.DataFrame({
    'level': list(range(1,10)),
    'sanity':[9,9,9,12,12,12,18,18,18]
})
df_melt = df_melt.join(level_sanity.set_index('level'),on='level')
df_melt['rarity_per_sanity'] = df_melt['rarity'].div(df_melt['sanity'])
df_melt['value_per_sanity'] = df_melt['val'].div(df_melt['sanity'])
df_melt_group = df_melt.groupby('level').sum()

df_melt2 = df.drop(columns=['have']).melt(
    id_vars=['id','val','rarity','name'],
    var_name='level_seq',
    value_name='level'
)
rarity2_not_in_lv2_3_list = df_melt2[((df_melt2.rarity==2)|(df_melt2.rarity==3))&((df_melt2.level!=2)&(df_melt2.level!=3))]
rarity2_not_in_lv2_3_list_piv = rarity2_not_in_lv2_3_list.pivot(
    index='name',
    columns='level_seq',
    values='level'
)