For example, suppose I had the array of integers [5, 3, 7, 6, 4]. I'm hoping to find an efficient way to create a pandas dataframe which looks like this:
This should be such that the first row in the dataframe contains the numbers 1 to 5, the second should contain the numbers 1 to 3, and so on.
Is there a way to achieve this without looping?
A simple one-liner with range function
pd.DataFrame(range(1, x+1) for x in [5, 3, 7, 6, 4])
Output
0 1 2 3 4 5 6
0 1.0 2.0 3.0 4.0 5.0 NaN NaN
1 1.0 2.0 3.0 NaN NaN NaN NaN
2 1.0 2.0 3.0 4.0 5.0 6.0 7.0
3 1.0 2.0 3.0 4.0 5.0 6.0 NaN
4 1.0 2.0 3.0 4.0 NaN NaN NaN
setting up a df:
df = pd.DataFrame([[1,2,3,4,5,6,7]]*5)
filtering the maximum values row wise:
max_row_val = [5, 3, 7, 6, 4]
df.ge(max_row_val, axis=0)
0 1 2 3 4 5 6
0 False False False False False True True
1 False False False True True True True
2 False False False False False False False
3 False False False False False False True
4 False False False False True True True
And just set the df to nan at True
df[df.ge(max_row_val, axis=0)] = np.nan
0 1 2 3 4 5 6
0 1 2 3 4.0 5.0 NaN NaN
1 1 2 3 NaN NaN NaN NaN
2 1 2 3 4.0 5.0 6.0 7.0
3 1 2 3 4.0 5.0 6.0 NaN
4 1 2 3 4.0 NaN NaN NaN
This achieved 0.0003582255399999667s per loop on my machine for this size df.
With numpy and masked arrays for performance:
a = [5, 3, 7, 6, 4]
n = np.repeat(np.arange(1, max(a)+1)[None, :], len(a), axis=0)
m = n > np.array(a)[:, None]
df = pd.DataFrame(np.ma.array(n, mask=m))
where we first form n that is 1..max(a) repeated by length of a and then find m that masks the appropriate places for np.NaN. Then, masked array is passed to the frame constructor,
to get
0 1 2 3 4 5 6
0 1.0 2.0 3.0 4.0 5.0 NaN NaN
1 1.0 2.0 3.0 NaN NaN NaN NaN
2 1.0 2.0 3.0 4.0 5.0 6.0 7.0
3 1.0 2.0 3.0 4.0 5.0 6.0 NaN
4 1.0 2.0 3.0 4.0 NaN NaN NaN
timings:
For the given setup:
a = [5, 3, 7, 6, 4]
# @Vishnudev's solution
%timeit pd.DataFrame(range(1, x+1) for x in a)
553 µs ± 25.2 µs per loop
# @Tom Mclean's solution (a bit modified for generalization)
%%timeit
df = pd.DataFrame([list(range(1, max(a)+1))]*len(a))
df[df.ge(a, axis=0)] = np.nan
2.14 ms ± 43.9 µs per loop
# This solution
%%timeit
n = np.repeat(np.arange(1, max(a)+1)[None, :], len(a), axis=0)
m = n > np.array(a)[:, None]
pd.DataFrame(np.ma.array(n, mask=m))
139 µs ± 2.22 µs per loop
For a large array:
a = np.random.randint(3, 10_000, size=5_000)
# @Vishnudev solution
%timeit pd.DataFrame(range(1, x+1) for x in a)
8.12 s ± 76 ms per loop
# @Tom Mclean's solution (a bit modified for generalization)
%%timeit
df = pd.DataFrame([list(range(1, max(a)+1))]*len(a))
df[df.ge(a, axis=0)] = np.nan
15 s ± 199 ms per loop
# This solution
%%timeit
n = np.repeat(np.arange(1, max(a)+1)[None, :], len(a), axis=0)
m = n > np.array(a)[:, None]
pd.DataFrame(np.ma.array(n, mask=m))
583 ms ± 16.1 ms per loop
You can do it with the .apply() method. Though, if you need to do this on a big dataframe, the performance will be really bad. solution with apply methode
