读取按nan行拆分的数据框，并在Python中将其重塑为多个数据框

采用：

#add header=None for default columns names
df = pd.read_excel('./data1.xlsx', sheet_name = 'Sheet1', header=None)

#convert columns by second row
df.columns = df.iloc[1].rename(None)

#create new column `city` by forward filling non missing values by second column
df.insert(0, 'city', df.iloc[:, 0].mask(df.iloc[:, 1].notna()).ffill())
#convert floats to integers 
df.columns = [int(x) if isinstance(x, float) else x for x in df.columns]
#convert column year to index
df = df.set_index('year')

print (df)
         city    2018    2019    2020  sum
year                                      
bj         bj     NaN     NaN     NaN  NaN
year       bj  2018.0  2019.0  2020.0  sum
price      bj    12.0     4.0     5.0   21
quantity   bj     5.0     5.0     3.0   13
NaN        bj     NaN     NaN     NaN  NaN
sh         sh     NaN     NaN     NaN  NaN
year       sh  2018.0  2019.0  2020.0  sum
price      sh     5.0     6.0     7.0   18
quantity   sh     7.0     5.0     4.0   16
NaN        sh     NaN     NaN     NaN  NaN
NaN        sh     NaN     NaN     NaN  NaN
gz         gz     NaN     NaN     NaN  NaN
year       gz  2018.0  2019.0  2020.0  sum
price      gz     2.0     3.0     1.0    6
quantity   gz     6.0     9.0     3.0   18
NaN        gz     NaN     NaN     NaN  NaN
NaN        gz     NaN     NaN     NaN  NaN
sz         sz     NaN     NaN     NaN  NaN
year       sz  2018.0  2019.0  2020.0  sum
price      sz     8.0     2.0     3.0   13
quantity   sz     5.0     4.0     3.0   12

df1 = df.loc['price'].reset_index(drop=True)
print (df1)
  city  2018  2019  2020 sum
0   bj  12.0   4.0   5.0  21
1   sh   5.0   6.0   7.0  18
2   gz   2.0   3.0   1.0   6
3   sz   8.0   2.0   3.0  13

df2 = df.loc['quantity'].reset_index(drop=True)
print (df2)
  city  2018  2019  2020 sum
0   bj   5.0   5.0   3.0  13
1   sh   7.0   5.0   4.0  16
2   gz   6.0   9.0   3.0  18
3   sz   5.0   4.0   3.0  12

上次写DataFrames到现有文件可以通过mode='a'参数的链接：

with pd.ExcelWriter('data1.xlsx', mode='a') as writer:  
    df1.to_excel(writer, sheet_name='price')
    df2.to_excel(writer, sheet_name='quantity')