Find the first time a value occurs in a data frame

Question

I have a quarter-quarter data frame (e.g. 2015-Q4), customer_ID and registered amount, as well as many other columns that are currently out of date. I want to create a column in which each customer made a reservation. I tried this:

alldata.sort_values(by=['Total_Apps_Reseller_Bookings_USD', 'Year_Quarter'], 
                    ascending=[1, 1], 
                    inplace=True)
first_q = alldata[['Customer_ID', 'Year_Quarter']].groupby(by='Customer_ID').first()

but i'm not sure if it worked.

In addition, I then want to have another column that tells me how many quarters after the first reservation was made. I did not use substitution and dictionary, so I used merging. I create a numeric identifier for each quarter of the reservation and the first quarter on top, and then subtract two:

q_booking_num = pd.DataFrame({'Year_Quarter': x, 'First_Quarter_id': np.arange(28)})

alldata = pd.merge(alldata, q_booking_num, on='Year_Quarter', how='outer')
q_first_num = pd.DataFrame({'First_Quarter': x, 'First_Quarter_id': np.arange(28)})
alldata = pd.merge(alldata, q_first_num, on='First_Quarter', how='outer')

this does not seem to have worked, as I see the “first quarters” that have already been made after some orders.

Answer 1

, :

first_q = (alldata[['Customer_ID','Year_Quarter']]
           .groupby(by='Customer_ID')
           .Year_Quarter
           .first()
          )

:

df = pd.DataFrame({'customer_ID': [1, 
                                   2, 2, 
                                   3, 3, 3], 
                   'Year_Quarter': ['2010-Q1', 
                                    '2010-Q1', '2011-Q1', 
                                    '2010-Q1', '2011-Q1', '2012-Q1'], 
                   'Total_Apps_Reseller_Bookings_USD': [1, 
                                                        2, 3, 
                                                        4, 5, 6]})

(, "2010-Q1" ) , int (df.Year_Quarter.str[:4].astype(int)). . , .

transform groupby, , . transform , .

quarters_since_first_order .

df['quarters'] = df.Year_Quarter.str[:4].astype(int) * 4 + df.Year_Quarter.str[-1].astype(int)
first_order_quarter_no = df.groupby('customer_ID').quarters.transform(min)
df['quarters_since_first_order'] = quarters - first_order_quarter_no
del df['quarters']  # Clean-up.

>>> df
   Total_Apps_Reseller_Bookings_USD Year_Quarter  customer_ID  quarters_since_first_order
0                                 1      2010-Q1            1                           0
1                                 2      2010-Q1            2                           0
2                                 3      2011-Q1            2                           4
3                                 4      2010-Q1            3                           0
4                                 5      2011-Q1            3                           4
5                                 6      2012-Q1            3                           8

Answer 2

1:

, - , :

alldata.sort_values(by=['Customer_ID', 'Year_Quarter', 
                        'Total_Apps_Reseller_Bookings_USD'], 
                    ascending=[1, 1],inplace=True)
first_q = alldata[['Customer_ID','Year_Quarter']].groupby(by='Customer_ID').head(1)

2:

1, . , .

- :

def qt_sub(val, first):
    year_dif = val[0:4] - first[0:4]
    qt_dif = val[6] - first[6]
    return 4 * int(year_dif) + int(qt_dif)

alldata['diff_from_first'] = alldata.apply(lambda x: qt_sub(x['Year_Quarter'], 
                                                            x['First_Sale']),
                                           axis=1)