mardi 1 mai 2018

In Python, how to do groupby + transform + a designed function to move the values in one column?

My data is like this:

   ARTICLE  Day  Row
        a    2   10
        a    3   10
        a    4   10
        a    5   10
        a    6   10
        a    7   10
        a    8   10
        a    9   10
        a   10   10
        a   11   10
        b    3    1

I want to generate a new column, called Date. Firstly, I group the data by ARTICLE. Then for every article group, if Row is 1, then the value in Date is the same as the one in Day. Otherwise, move all the values in Day one step upward and set the last value be 100. So, the new data should look like this:

   ARTICLE  Day  Row    Date
        a    2   10      3
        a    3   10      4
        a    4   10      5
        a    5   10      6
        a    6   10      7
        a    7   10      8
        a    8   10      9
        a    9   10      10
        a   10   10      11
        a   11   10      100
        b    3    1      3

I assume this can be done by groupby and transform. A function is taken to generate Date. So, my code is:

def myFUN_PostDate1(NRow,Date):
    if (NRow.unique()==1):
        return Date
    else:
        Date1 = Date[1:Date.shape[0]]
        Date1[Date1.shape[0] + 1] = 19800312
        return Date1

a = pd.DataFrame({'ARTICLE': ['a','a','a','a','a','a','a','a','a','a','b'],
                  'Day': [2,3,4,5,6,7,8,9,10,11,3],
                  'Row':[10,10,10,10,10,10,10,10,10,10,1]})
a.loc[:,'Date'] = a.groupby(['ARTICLE']).transform(lambda x: myFUN_PostDate1(x.loc[:,'Row'],x.loc[:,'Day']))

But I have the error information:

pandas.core.indexing.IndexingError: ('Too many indexers', 'occurred at index Day')

I also tried groupby + np.where. But I have got the same error.

I really cannot understand this. Could anyone please help me out here?

Aucun commentaire:

Enregistrer un commentaire