Splitting Dataframe into different group by product

Question

I am trying to split my Dataframe such that each Dataframe is for a product. Given below is how my Dataframe looks like:

row_label, jan, feb, mar, apr
prod_a, 100, 120, 123, 150
prod_b, 123, 233, 200, 40
prod_c, 10, 23, 34, 33

Expected output:

Dataframe for prod_a

row_label, value
jan, 100
feb, 120
mar, 123
apr, 150

Dataframe for prod_b

row_label, value
jan, 123
feb, 233
mar, 200
apr, 40

Dataframe for prod_c

row_label, value
jan, 10
feb, 23
mar, 34
apr, 33

jezrael · Accepted Answer · 2021-01-20T12:23:42.477

4

I suggest here create dictionary of DataFrames in dictionary comprehension:

df1 = df.set_index('row_label').T

dfs = {x: df1[x].to_frame(name='value') for x in df1.columns}

print (dfs['prod_a'])

Variables from strings are not recommended, but possible, e.g. with globals:

for x in df1.columns:
    globals()[x] =  df1[x].to_frame('value')

print (prod_a)

Another idea with groupby, thank you @Lunalo John for idea:

dfs = {k: v.T for k,  v in df.set_index('row_label').groupby('row_label')}
print (dfs['prod_a'])
row_label  prod_a
jan           100
feb           120
mar           123
apr           150

edited Jan 20 '21 at 12:23

answered Jan 20 '21 at 12:19

jezrael

822,522
95
1,334
1,252

1

You can also store them in a list `[df_val for df, df_val in df.groupby("row_label")]` – Lunalo John Jan 20 '21 at 12:23
@jezrael thanks for this, I am able to get the dict as expected. However when I try to consolidate this dict to a master Dataframe I get `ValueError: If using all scalar values, you must pass an index` – Kevin Nash Jan 20 '21 at 12:29
@jezrael, I did `pd.DataFrame(dfs)` – Kevin Nash Jan 20 '21 at 12:31
1

@KevinNash - I suggest use `pd.concat(dfs, axis=1)` – jezrael Jan 20 '21 at 12:32
1

@KevinNash - and if need consolidate better is change `.to_frame('value')` to `.to_frame()` for avoid duplicated columns names – jezrael Jan 20 '21 at 12:33

Splitting Dataframe into different group by product

1 Answers1