<p>用<code>pandas</code>怎么样?你知道吗</p>
<p>您可以以excel格式读取数据,只提取月份,并使用所需的计算值制作<code>pivot_table()</code>:</p>
<pre><code>import pandas as pd
import numpy as np
df = pd.read_excel('test.xlsx')
df['Date'] = df['Date'].dt.month
table = pd.pivot_table(df, values=['Hours'],
index=['Manager', 'User', 'Date'],
columns=['Category'], aggfunc=np.sum).fillna(0)
</code></pre>
<p><a href="https://i.stack.imgur.com/2JryR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2JryR.png" alt="enter image description here"/></a></p>
<p>接下来您可以用<code>orient='index'</code>导出它:</p>
<pre><code>table.to_json('out.json', orient='index')
</code></pre>
<p>结果:</p>
<pre><code>{
"["Manager1","User1",2]":{"["Hours","Category1"]":4.0,"["Hours","Category2"]":0.0},
"["Manager1","User2",4]":{"["Hours","Category1"]":9.0,"["Hours","Category2"]":4.0},
"["Manager2","User3",5]":{"["Hours","Category1"]":3.0,"["Hours","Category2"]":0.0},
"["Manager3","User4",1]":{"["Hours","Category1"]":0.0,"["Hours","Category2"]":3.0}
}
</code></pre>
<p>不完全是post中的格式,但是值在那里(或者对json做一些后处理)。你知道吗</p>
<p><strong>编辑</p>
<p>或者在调用<code>to_json()</code>之前将其转换回<code>DataFrame</code>:</p>
<pre><code>json.loads(pd.DataFrame(table.to_records()).to_json(orient='records'))
</code></pre>
<p>结果:</p>
<pre><code>[{'Manager': 'Manager1',
'User': 'User1',
'Date': 2,
"('Hours', 'Category1')": 4.0,
"('Hours', 'Category2')": 0.0},
{'Manager': 'Manager1',
'User': 'User2',
'Date': 4,
"('Hours', 'Category1')": 9.0,
"('Hours', 'Category2')": 4.0},
{'Manager': 'Manager2',
'User': 'User3',
'Date': 5,
"('Hours', 'Category1')": 3.0,
"('Hours', 'Category2')": 0.0},
{'Manager': 'Manager3',
'User': 'User4',
'Date': 1,
"('Hours', 'Category1')": 0.0,
"('Hours', 'Category2')": 3.0}]
</code></pre>