1

I have the following Series:

Period Ending

0      7/2017
1      4/2017
2      1/2017
3     10/2016
4      7/2016
5      4/2016

I want to convert the month to fewer options (call them quarters) .

For this operation I built a dictionary:

period_dict = {3: [1,2,3], 6:[4,5,6], 9:[7,8,9], 12:[10,11,12]}

Keys are quarters and values are months.

My idea is that if the month value in the pandas column is in a dictionary value (list) then replace it using the key value.

Output should look like this:

NEW Period Ending

0      9/2017
1      6/2017
2      3/2017
3     12/2016
4      9/2016
5      6/2016

I did lots of research but everything points to matching a key and replacing with a single dictionary value.

Remap values in pandas column with a dict

Also:

Python Pandas: How to replace a characters in a column of a dataframe?

Conceptually the closest I came up with is:

for k, v in period_dict.items():
    if table['Period Ending'] in v:
        table['Period Ending'].replace([i[0] for i in table['Period Ending']],k)

I get

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

I appreciate any ideas.

EDIT 1

As part of the solution I was able to 'isolate' the month string but still do not know how to convert it using the dictionary.

    print 'GET Month String'
    ''' Split date con / '''
    # de https://stackoverflow.com/questions/27387415/how-would-i-get-everything-before-a-in-a-string-python
    split_df = tabla['Period Ending'].astype(str).str.split('/')
    split_date = split_df.str[0]
    print split_date
    print

Output:

GET Month String
0      7
1      4
2      1
3     10
4      7
5      4
Diego
  • 637
  • 3
  • 10
  • 24

2 Answers2

3

Here's one way

Use new_map with value to be replaced as key, and value as final group

In [3998]: new_map = {str(x): str(k) for k, v in period_dict.iteritems() for x in v}

Split month and year part

In [3999]: dff = df['Period Ending'].str.split('/', expand=True)

Use map with month series on new_map mapping and then join the year series

In [4000]: dff[0].map(new_map) + '/' + dff[1]
Out[4000]:
0     9/2017
1     6/2017
2     3/2017
3    12/2016
4     9/2016
5     6/2016
dtype: object

Or, use apply

In [4009]: df['Period Ending'].str.split('/', expand=True).apply(
                     lambda x: new_map[x[0]] + '/' + x[1], axis=1)
Out[4009]:
0     9/2017
1     6/2017
2     3/2017
3    12/2016
4     9/2016
5     6/2016
dtype: object

Details

In [4001]: new_map
Out[4001]:
{'1': '3',
 '10': '12',
 '11': '12',
 '12': '12',
 '2': '3',
 '3': '3',
 '4': '6',
 '5': '6',
 '6': '6',
 '7': '9',
 '8': '9',
 '9': '9'}

In [4002]: period_dict
Out[4002]: {3: [1, 2, 3], 6: [4, 5, 6], 9: [7, 8, 9], 12: [10, 11, 12]}

In [4003]: df
Out[4003]:
  Period Ending
0        7/2017
1        4/2017
2        1/2017
3       10/2016
4        7/2016
5        4/2016
Zero
  • 74,117
  • 18
  • 147
  • 154
  • This so great!!! Thanks a lot! Not only it is a great solution but there is a lot for me to learn in your code. – Diego Sep 26 '17 at 17:41
3

Here is a way using datetime functionality. Convert the months to quarter and then parse to months.

df['Period Ending'] = pd.PeriodIndex(pd.to_datetime(df['Period Ending']), freq='Q')\
.strftime('%m/%Y')

You get

    Period Ending
0   09/2017
1   06/2017
2   03/2017
3   12/2016
4   09/2016
5   06/2016
Vaishali
  • 37,545
  • 5
  • 58
  • 86
  • One-liner. Very elegant! Thank you so much! – Diego Sep 26 '17 at 17:58
  • @Diego, Thank you :) – Vaishali Sep 26 '17 at 18:01
  • This was an XY problem and you went back and fixed X. – piRSquared Sep 26 '17 at 18:02
  • @piRSquared, I am not sure but the original question just just asked for the months to be converted to period end in the form of quarter end. I was looking for something more robust than a dictionary mapping. – Vaishali Sep 26 '17 at 18:08
  • [**See This**](https://meta.stackexchange.com/q/66377/326787) Exactly! You identified that rather than solving the problem they asked to be solved, it is better to do something different in the first place. – piRSquared Sep 26 '17 at 18:15
  • My 2 cents, this code outputs format 09/2017, since I need 9/2017, the code will work better with %#m/%Y (for Windows) and %-m/%Y for Unix – Diego Sep 26 '17 at 18:51