1

(You can assume the date column is datetime)

This may be relevant to the problem, but I also have a list of dates with the following values:

['2018-01-01', '2018-02-01', '2018-03-01', 
'2018-04-01', '2018-05-01']

I have the following table:

date         arb1      arb2      amount
2018-01-02   Z         F         5
2018-01-03   G         H         10
2018-02-04   V         T         51
2018-03-03   R         S         54

I want this output, where each date has been set to the first of its corresponding month:

date         arb1      arb2      amount
2018-01-01   Z         F         5
2018-01-01   G         H         10
2018-02-01   V         T         51
2018-03-01   R         S         54

Bonus (not required): What I'm actually trying to do is add up the amounts after grouping by date (the month), arb1, and arb2, and the final output has each date as the first of the month. I figured an easy way to get here would be to first change each date to the first of the month, but if there is an easy way to do this in pandas then the above steps aren't required and you can skip to this instead

JesusMonroe
  • 1,421
  • 3
  • 13
  • 20
  • Interesting task. What have you tried so far? Can you provide a **[mcve]** of the problem you face? – jpp Aug 03 '18 at 14:52
  • Did you know reading Pandas documentations helps in answering 90% of questions? Pandas has awesome documentation that is way to good to not read it. This question could be answered by reading this section:https://pandas.pydata.org/pandas-docs/stable/timeseries.html#using-offsets-with-series-datetimeindex – Prayson W. Daniel Aug 03 '18 at 14:58
  • 1
    @jpp the other question you linked doesn't deal with the case of one of the dates already being the first of a month – JesusMonroe Aug 03 '18 at 15:26
  • @JesusMonroe, See the second dupe. It has the answer you accepted. – jpp Aug 03 '18 at 15:38

1 Answers1

2

You need below to get the first date of the month.

df['date'] = df['date'].dt.to_period('M').dt.to_timestamp()

Output:

        date arb1 arb2  amount
0 2018-02-01    Z    F       5
1 2018-01-01    G    H      10
2 2018-02-01    V    T      51
3 2018-03-01    R    S      54
harvpan
  • 8,571
  • 2
  • 18
  • 36