CODEWITHSUNDEEP
pythonpandas
Kartikey Singh
Kartikey Singh

Reputation: 892

Mapping row items to fixed columns set of another dataframe?

I have been trying to map time wise each channel's velocity and note to another data frame.

For example: I have a table in this format:

enter image description here

It is not visible in this table but for each time multiple channel like (1, 2, ... 16) may be available (each channel occurs only once for each time i.e. channel do no repeat for a particular time) and their corresponding note and velocity values.

As channels are in a fixed range from 1 to 16 only. So, this leads to the creation of 32 fixed columns of a new data frame like:

| time | C1 note| C1 velocity| C2 note| C2 velocity| ...... | C16 note| C16 velocity|

If a value is not found for a particular column fill it with 0.

Is there a way to do this in a pandish way?

Upvotes: 1

Views: 87

Answers (1)

Scott Boston
Scott Boston

Reputation: 153510

Try this MCVE:

import numpy as np
import pandas as pd
timelist = np.arange(1,21)
channels = [1,2,3,5]

df = pd.DataFrame({'notes':np.random.randint(100,200,80), 
                   'velocity':np.random.randint(1000,9000, 80)},
                 index=pd.MultiIndex.from_product([timelist, channels]))

df = df.rename_axis(['Time', 'Channel'])

Input dataframe:

              notes  velocity
Time Channel                 
1    1          199      7683
     2          106      5981
     3          111      8324
     5          177      6123
2    1          113      6608
...             ...       ...
19   5          120      1779
20   1          149      7591
     2          171      8513
     3          128      1341
     5          192      5942

[80 rows x 2 columns]

Try unstacking and flattening headers:

df_out = df.unstack().reindex(pd.MultiIndex.from_product([['notes','velocity'], 
                                                          [1,2,3,4,5]]), 
                              axis=1, 
                              fill_value=0)

df_out.columns = [f'C{j} {i}' for i,j in df_out.columns]
df_out

Output:

      C1 notes  C2 notes  C3 notes  C4 notes  C5 notes  C1 velocity  C2 velocity  C3 velocity  C4 velocity  C5 velocity
Time                                                                                                                   
1          199       106       111         0       177         7683         5981         8324            0         6123
2          113       115       151         0       115         6608         3461         8315            0         1142
3          142       160       160         0       130         4394         7909         1738            0         1283
4          128       175       148         0       197         4002         4245         7066            0         1851
5          148       129       196         0       144         7755         3266         4123            0         6621

Upvotes: 1

Related Questions