我试图粗略估计工作人员在给定月份可能完成的工作量。
我有一个大致像这样的 csv（虽然它要大得多）：
+--------+-------+---------------+
| Date | Name | Units of Work |
+--------+-------+---------------+
| 1/1/17 | Bob | 450 |
| 2/1/17 | Alice | 300 |
| 2/1/17 | Bob | 450 |
| 2/1/17 | Larry | 50 |
| 3/1/17 | Alice | 400 |
| 3/1/17 | Bob | 11 |
| 3/1/17 | Larry | 100 |
| 4/1/17 | Alice | 1000 |
| 4/1/17 | Bob | 240 |
| 4/1/17 | Larry | 33 |
+--------+-------+---------------+
我想要：
我什至不能满足要求 1，更不用说 2 到 5。我为每个日期获取一个文件。而不是带有中位数的列，我得到一个名为“NewColumn”的新列，其中填充了“中值”一词，如下所示：
# -*- coding: utf-8 -*-
import pandas as pd
df = pd.read_csv('source.csv')
df = df.sort_values('date_trunc').assign(NewColumn='median')
df.median(axis=None, skipna=None, level=None, numeric_only=None)
for i, g in df.groupby('date_trunc'):
g.to_csv('{}.csv'.format(i), header=True, index_label=False, index=False)
+---------+-------+---------------+-----------+
| Date | Name | Units of work | NewColumn |
+---------+-------+---------------+-----------+
| 12/1/16 | Alice | 6222 | median |
| 12/1/16 | Bob | 14530 | median |
| 12/1/16 | Larry | 16887 | median |
+---------+-------+---------------+-----------+
我知道我在这里可能做错了很多，但我真的很感激一些指导。
我想最终得到的是一个带有以下内容的 csv：
+---------+--------+
| Date | Median |
+---------+--------+
| 12/1/16 | 1110 |
| 1/1/17 | 1400 |
| 2/1/17 | 1200 |
+---------+--------+
我希望以下步骤能让您更接近所需的 CSV 输出。
首先，这是输入 DataFrame 的清晰再现，供其他希望复制粘贴到的人使用pd.read_clipboard()：
Date Name Units of Work
0 Jan-17 Bob 450.0
1 Feb-17 Alice 300.0
2 Feb-17 Bob 450.0
3 Feb-17 Larry 50.0
4 Mar-17 Alice 400.0
5 Mar-17 Bob 11.0
6 Mar-17 Larry 100.0
7 Apr-17 Alice 1000.0
8 Apr-17 Bob 240.0
9 Apr-17 Larry 33.0
# Docs on Python datetime format strings: https://docs.python.org/2/library/datetime.html#strftime-and-strptime-behavior
df['Date'] = pd.to_datetime(df['Date'].apply(lambda x: x.strip()), format='%b-%y')
meds = df.groupby('Date')[['Units of Work']].median()
meds
Units of Work
Date
2017-01-01 450.0
2017-02-01 300.0
2017-03-01 100.0
2017-04-01 240.0
# Set an index on which to merge the medians
df2 = df.set_index('Date')
# Pandas is smart enough to merge the 4-row meds DataFrame onto the 10-row df2 DataFrame based on matching index values
df2['Median'] = meds
# Build a boolean mask to pick out "hard workers" and "slackers"
mask = df2['Units of Work'] >= 0.2 * df2['Median']
# "Hard workers," where units of work >= 20% of that date's median
df2[mask]
Name Units of Work Median
Date
2017-01-01 Bob 450.0 450.0
2017-02-01 Alice 300.0 300.0
2017-02-01 Bob 450.0 300.0
2017-03-01 Alice 400.0 100.0
2017-03-01 Larry 100.0 100.0
2017-04-01 Alice 1000.0 240.0
2017-04-01 Bob 240.0 240.0
# Bonus: "slackers," where units of work < 20% of that date's median
df2[~mask]
Name Units of Work Median
Date
2017-02-01 Larry 50.0 300.0
2017-03-01 Bob 11.0 100.0
2017-04-01 Larry 33.0 240.0
df2[mask].groupby('Date').size().mul(meds['Units of Work'])
2017-01-01 450.0
2017-02-01 600.0
2017-03-01 200.0
2017-04-01 480.0
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句