Speeding up loop for reorganizing pandas DataFrame into numpy array using slicing throws exception - what am I missing?

Question

I have a pandas DataFrame like so:

raw_data = DataFrame({
    'date_idx': [0, 1, 2, 0, 1, 2],
    'element_idx': [0, 0, 0, 1, 1, 1],
    'a': [10, 20, 30, 40, 50, 60],
    'b': [11, 21, 31, 41, 51, 61],
    'c': [12, 22, 32, 42, 52, 62],
})

I call the columns other than date_idx and element_idx "inputs". I want to reorganize it into a 3d numpy array by date_idx -> input_idx -> element_idx, so that the result is like so:

[[[10. 40.]
  [11. 41.]
  [12. 42.]]

 [[20. 50.]
  [21. 51.]
  [22. 52.]]

 [[30. 60.]
  [31. 61.]
  [32. 62.]]]

I did it with two for loops, and it works well:

date_idx = [0, 1, 2, 0, 1, 2]
element_idx = [0, 0, 0, 1, 1, 1]
raw_data = DataFrame({
    'date_idx': date_idx,
    'element_idx': element_idx,
    'a': [10.0, 20.0, 30.0, 40.0, 50.0, 60.0],
    'b': [11.0, 21.0, 31.0, 41.0, 51.0, 61.0],
    'c': [12.0, 22.0, 32.0, 42.0, 52.0, 62.0],
})

inputs = ['a', 'b', 'c']

unique_dates = set(date_idx)
unique_elements = set(element_idx)
data = np.zeros(shape=(len(unique_dates), len(inputs), len(unique_elements)), dtype=np.float64)

for i in range(len(raw_data)):
    row = raw_data.iloc[i]
    date_idx = int(row['date_idx'])
    element_idx = int(row['element_idx'])

    for input_idx in range(len(inputs)):
        data[date_idx][input_idx][element_idx] = float(row[inputs[input_idx]])

print(data)

However, this is very slow. I have millions of entries for the date_idx array, and dozens for both inputs and element_idx. It takes 7 hours on my machine for this to complete with my real data set.

I have a feeling this could be done with slicing, no loops, but my attempts always fail - I'm missing something.

For example, I tried to eliminate the inner loop with:

for i in range(len(raw_data)):
    row = raw_data.iloc[i]
    date_idx = int(row['date_idx'])
    element_idx = int(row['element_idx'])

    data[date_idx][:][element_idx] = list(dict(row[inputs]).values())

And it fails with:

Traceback (most recent call last):
  File "/home/stark/Work/mmr6/test2.py", line 84, in <module>
    data[date_idx][:][element_idx] = list(dict(row[inputs]).values())
    ~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
ValueError: could not broadcast input array from shape (3,) into shape (2,)

My question is, can slicing and / or fast technique be used to reorganize this DataFrame in that fashion on the plain numpy array, or do I really need the loops here?

In data[date_idx][:][element_idx], the [:] does NOTHING. For 3d use data use data[date_idx, :, element_idx] indexing syntax. — hpaulj
– hpaulj, Commented Jun 4, 2023 at 21:14

Andrej Kesely · Accepted Answer · 2023-06-04 17:25:22Z

0

I think you're searching for pivoting the dataframe and then convert it to numpy array:

num_unique_elements = raw_data['element_idx'].nunique()
num_unique_inputs = 3 # a, b, c

df = pd.pivot(raw_data, index='date_idx', columns='element_idx')
df = df.stack(level=0)
print(df.to_numpy().reshape(-1, num_unique_inputs, num_unique_elements))

Prints:

[[[10 40]
  [11 41]
  [12 42]]

 [[20 50]
  [21 51]
  [22 52]]

 [[30 60]
  [31 61]
  [32 62]]]

Steps:

df = pd.pivot(raw_data, index='date_idx', columns='element_idx')
print(df)

              a       b       c    
element_idx   0   1   0   1   0   1
date_idx                           
0            10  40  11  41  12  42
1            20  50  21  51  22  52
2            30  60  31  61  32  62

Then reshape it using .stack()

df = df.stack(level=0)
print(df)

element_idx   0   1
date_idx           
0        a   10  40
         b   11  41
         c   12  42
1        a   20  50
         b   21  51
         c   22  52
2        a   30  60
         b   31  61
         c   32  62

Then convert it to numpy array:

print(df.to_numpy().reshape(-1, num_unique_inputs, num_unique_elements))

[[[10 40]
  [11 41]
  [12 42]]

 [[20 50]
  [21 51]
  [22 52]]

 [[30 60]
  [31 61]
  [32 62]]]

answered Jun 4, 2023 at 17:25

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Edy Bourne Over a year ago

Ohhh, nice! I will study this now, thanks!!

Andrej Kesely Over a year ago

@EdyBourne Maybe the num_unique_inputs should be num_unique_inputs = raw_data['date_idx'].nunique() (it's not clear to me from the input DataFrame). But the the main thing, this is just dataframe pivoting/numpy reshaping problem. Maybe you can use this SO question for the last step: stackoverflow.com/questions/46079042/…

Edy Bourne Over a year ago

I just noticed when running my larger data set that the only reason it worked was because the columns were in alphabetical order. Ir turns out that if they are not, this method does not seem to respect the columns as given to the dataframe, but instead seems to rely on it's internal structure such that the output always works as if they were in alphabetical order. The issue is at the stack() call, which reorders the data in alphabetical order. I wonder if there is a way to keep the column order at that step?

Andrej Kesely Over a year ago

@EdyBourne To not clutter the comment section I suggest to open a new question here on StackOverflow. I try to look at it.

Edy Bourne Over a year ago

I just noticed the stack() step is actually not needed - we can get the numpy array directly after pivoting and performing that reshape, and the result comes out correct. I was investigating alternatives because the stack call was taking over an hour to complete. Now the whole thing is incredibly fast - thought I'd share!

Collectives™ on Stack Overflow

Speeding up loop for reorganizing pandas DataFrame into numpy array using slicing throws exception - what am I missing?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related