Applying linspace for every row in a pandas dataframe-CodePudding

If I have a dataframe say df like this for example

                     date     number_range  id
0 [2010-01-01, 2010-03-01]    [5, 10]       1
1 [2010-02-01, 2010-06-01]    [1, 3]        1
2 [2010-07-01, 2010-11-01]    [12-50]       1

I want to apply numpy.linspace to the above by finding the date diff and then applying linspace to all of the rows. For example, the date diff for row 0 would be 2, and apply linspace(5,10,2) and row 1 would be a diff of 4 and apply linspace(1,3,4).

final result
-------------
                     date     number_range  id   linspace
0 [2010-01-01, 2010-03-01]    [5, 10]       1    [5, 10]
1 [2010-02-01, 2010-06-01]    [1, 3]        1    [1, 1.66667, 2.3333, 3]
2 [2010-07-01, 2010-12-01]    [12-50]       1    [12, 21.5, 31, 40.5, 50]

I have tried doing df.apply(lambda row: np.linspace(row['start_value'], row['end value'], row['diff']) but I keep getting a type error saying that 'Series' object cannot be interpreted as an integer...and I have tried doing a diff.astype(int) with the same error...not sure where to go from there.

CodePudding user response：

Make sure you understand what row is:

In [133]: def foo(row):
     ...:     print(row)
     ...: 
In [134]: df.apply(foo)
0    [2010-01-01, 2010-03-01]
1    [2010-02-01, 2010-06-01]
2    [2010-07-01, 2010-11-01]
Name: date, dtype: object
0     [5, 10]
1      [1, 3]
2    [12, 50]
Name: number_range, dtype: object
0    1
1    1
2    1
Name: id, dtype: int64
Out[134]: 
date            None
number_range    None
id              None
dtype: object

In [136]: def foo(row):
     ...:     print(row['start_value'], row['end_value'], row['diff'])
     ...: 
In [137]: df.apply(foo)
Traceback (most recent call last): 
...
KeyError: 'start_value'

With the axis=1 suggested in the other answer:

In [148]: def foo(row):
     ...:     print(type(row))
     ...:     print(row)
     ...:     print(row['date'])
     ...:     print(row['number_range'])

In [149]: df.apply(foo, axis=1)
<class 'pandas.core.series.Series'>
date            [2010-01-01, 2010-03-01]
number_range                     [5, 10]
id                                     1
Name: 0, dtype: object
['2010-01-01', '2010-03-01']
[5, 10]
<class 'pandas.core.series.Series'>
... 
['2010-02-01', '2010-06-01']
[1, 3]
<class 'pandas.core.series.Series'>
....
['2010-07-01', '2010-11-01']
[12, 50]

Now we can pull the end points out of the number_range colume:

In [150]: def foo(row):
     ...:     nr = row['number_range']
     ...:     return np.linspace(nr[0],nr[1],3)
     ...: 
In [151]: df.apply(foo, axis=1)
Out[151]: 
0      [5.0, 7.5, 10.0]
1       [1.0, 2.0, 3.0]
2    [12.0, 31.0, 50.0]
dtype: object

I could generate the same numbers with one linspace:

In [159]: df['number_range'].to_numpy()
Out[159]: array([list([5, 10]), list([1, 3]), list([12, 50])], dtype=object)
In [160]: nr = np.stack(df['number_range'].to_numpy())
In [161]: nr
Out[161]: 
array([[ 5, 10],
       [ 1,  3],
       [12, 50]])
In [162]: np.linspace(nr[:,0],nr[:,1],3).T
Out[162]: 
array([[ 5. ,  7.5, 10. ],
       [ 1. ,  2. ,  3. ],
       [12. , 31. , 50. ]])

I chose 3 to use for all rows; I didn't try to figure out where you got 2,4,and 5.

CodePudding user response：

You can use apply() with axis=1 like below: (if diff = [2,4,5])

df['linspace'] = df.apply(lambda x: np.round(
    np.linspace(x['number_range'][0],  x['number_range'][1], x['diff']),3), 
                          axis=1)
print(df)

Or first, you can create start_value and end_value as your question then create linspace like below:

df[['start_value','end_value']] = pd.DataFrame(df['number_range'].to_list())
df['linspace'] = df.apply(lambda x: np.round(
    np.linspace(x['start_value'],  x['end_value'], x['diff']),3), axis=1)
print(df)

Output:

                       date number_range  diff                        linspace
0  [2010-01-01, 2010-03-01]      [5, 10]     2                     [5.0, 10.0]
1  [2010-02-01, 2010-06-01]       [1, 3]     4        [1.0, 1.667, 2.333, 3.0]
2  [2010-07-01, 2010-11-01]     [12, 50]     5  [12.0, 21.5, 31.0, 40.5, 50.0]