python求平均工资,Python - 按月汇总并计算平均值

I have a csv which looks like this:

Date,Sentiment

2014-01-03,0.4

2014-01-04,-0.03

2014-01-09,0.0

2014-01-10,0.07

2014-01-12,0.0

2014-02-24,0.0

2014-02-25,0.0

2014-02-25,0.0

2014-02-26,0.0

2014-02-28,0.0

2014-03-01,0.1

2014-03-02,-0.5

2014-03-03,0.0

2014-03-08,-0.06

2014-03-11,-0.13

2014-03-22,0.0

2014-03-23,0.33

2014-03-23,0.3

2014-03-25,-0.14

2014-03-28,-0.25

etc

And my goal is to aggregate date by months and calculate average of months. Dates might not start with 1. or January. Problem is that I have a lot of data, that means I have more years. For this purpose I would like to find the soonest date (month) and from there start counting months and their averages. For example:

Month count, average

1, 0.4 (<= the earliest month)

2, -0.3

3, 0.0

...

12, 0.1

13, -0.4 (<= new year but counting of month is continuing)

14, 0.3

I'm using Pandas to open csv

data = pd.read_csv("pks.csv", sep=",")

so in data['Date'] I have dates and in data['Sentiment'] I have values. Any idea how to do it?

解决方案

Probably the simplest approach is to use the resample command. First, when you read in your data make sure you parse the dates and set the date column as your index (ignore the StringIO part and the header=True ... I am reading in your sample data from a multi-line string):

>>> df = pd.read_csv(StringIO(data),header=True,parse_dates=['Date'],

index_col='Date')

>>> df

Sentiment

Date

2014-01-03 0.40

2014-01-04 -0.03

2014-01-09 0.00

2014-01-10 0.07

2014-01-12 0.00

2014-02-24 0.00

2014-02-25 0.00

2014-02-25 0.00

2014-02-26 0.00

2014-02-28 0.00

2014-03-01 0.10

2014-03-02 -0.50

2014-03-03 0.00

2014-03-08 -0.06

2014-03-11 -0.13

2014-03-22 0.00

2014-03-23 0.33

2014-03-23 0.30

2014-03-25 -0.14

2014-03-28 -0.25

>>> df.resample('M',how='mean')

Sentiment

2014-01-31 0.088

2014-02-28 0.000

2014-03-31 -0.035

And if you want a month counter, you can add it after your resample:

>>> agg = df.resample('M',how='mean')

>>> agg['cnt'] = range(len(agg))

>>> agg

Sentiment cnt

2014-01-31 0.088 0

2014-02-28 0.000 1

2014-03-31 -0.035 2

You can also do this with the groupby method and the TimeGrouper function (group by month and then call the mean convenience method that is available with groupby).

>>> df.groupby(pd.TimeGrouper(freq='M')).mean()

Sentiment

2014-01-31 0.088

2014-02-28 0.000

2014-03-31 -0.035