Read dataframe split by nan rows and reshape them into multiple dataframes in Python

user_8410477 0
Viewed 128

I have a example excel file data1.xlsx from here, which has a Sheet1 as follows:

enter image description here

Now I want to read it with openpyxl or pandas, then convert them into new df1 and df2, I will finally save them as price and quantity sheet:

price sheet:

enter image description here

and quantity sheet

enter image description here

Code I have used:

df = pd.read_excel('./data1.xlsx', sheet_name = 'Sheet1')
df_list = np.split(df, df[df.isnull().all(1)].index) 

for df in df_list:
    print(df, '\n')

Out:

         bj  Unnamed: 1  Unnamed: 2  Unnamed: 3 Unnamed: 4
0      year      2018.0      2019.0      2020.0        sum
1     price        12.0         4.0         5.0         21
2  quantity         5.0         5.0         3.0         13 

         bj  Unnamed: 1  Unnamed: 2  Unnamed: 3 Unnamed: 4
3       NaN         NaN         NaN         NaN        NaN
4        sh         NaN         NaN         NaN        NaN
5      year      2018.0      2019.0      2020.0        sum
6     price         5.0         6.0         7.0         18
7  quantity         7.0         5.0         4.0         16 

    bj  Unnamed: 1  Unnamed: 2  Unnamed: 3 Unnamed: 4
8  NaN         NaN         NaN         NaN        NaN 

          bj  Unnamed: 1  Unnamed: 2  Unnamed: 3 Unnamed: 4
9        NaN         NaN         NaN         NaN        NaN
10        gz         NaN         NaN         NaN        NaN
11      year      2018.0      2019.0      2020.0        sum
12     price         2.0         3.0         1.0          6
13  quantity         6.0         9.0         3.0         18 

     bj  Unnamed: 1  Unnamed: 2  Unnamed: 3 Unnamed: 4
14  NaN         NaN         NaN         NaN        NaN 

          bj  Unnamed: 1  Unnamed: 2  Unnamed: 3 Unnamed: 4
15       NaN         NaN         NaN         NaN        NaN
16        sz         NaN         NaN         NaN        NaN
17      year      2018.0      2019.0      2020.0        sum
18     price         8.0         2.0         3.0         13
19  quantity         5.0         4.0         3.0         12

How could I do that in Python? Thanks a lot.

openpyxl pandas dataframe python-3.x
1 Answers

Use:

#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

Last write DataFrames to existing file is possible by mode='a' parameter, link:

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