python - 如何使用熊猫创建带有空单元格的数据透视表
问题描述
我有下表
tax rate Invoice No.
GST/HST NaN NaN
NaN NaN NaN
NaN 5.00% NaN
NaN NaN 100000
NaN NaN 100001
NaN NaN 100002
NaN NaN 100003
NaN NaN 100004
NaN NaN 100005
如何使用数据透视表生成这样的数据透视表:
Invoice No. tax rate
100000 GST/HST 5.00%
100001 GST/HST 5.00%
100002 GST/HST 5.00%
100003 GST/HST 5.00%
100004 GST/HST 5.00%
100005 GST/HST 5.00%
解决方案
你需要前向填充(ffill()
)和dropna()
out=df.ffill().dropna().iloc[:,[2,0,1]]
#df.ffill().dropna().loc[:,['Invoice No.','tax', 'rate']].reset_index(drop=True)
或者
使用正向填充和反向填充,然后删除重复项:
out=df.ffill().bfill().drop_duplicates().iloc[:,[2,0,1]]
输出out
:
Invoice No. tax rate
3 100000.0 GST/HST 5.00%
4 100001.0 GST/HST 5.00%
5 100002.0 GST/HST 5.00%
6 100003.0 GST/HST 5.00%
7 100004.0 GST/HST 5.00%
8 100005.0 GST/HST 5.00%
注意:解决方案中的 iloc 和 loc 也是完全可选的,如果不需要,您也可以删除它们