CODEWITHSUNDEEP
pythonpandasdataframe
Night Walker
Night Walker

Reputation: 21280

pandas adding grouped data frame to another data frame as row

I get following dataframe:

category_name             amount
Blades & Razors & Foam     158
Diaper                     486
Empty                      193
Fem Care                  2755
HairCare                  3490
Irrelevant                1458
Laundry                    889
Oral Care                 2921
Others                      69
Personal Cleaning Care    1543
Skin Care                  645

I want to add it as row to following dataframe that has additional retailer column that is absent with the first dataframe.

categories_columns = ['retailer'] + self.product_list.category_name.unique().tolist()
categories_df = pd.DataFrame(columns=categories_columns)

And if some category is missing I just want zero value.

Any ideas ?

Upvotes: 1

Views: 451

Answers (2)

unutbu
unutbu

Reputation: 880279

Use set_index to move the category_name column into the index. Then taking the transpose (.T) will move the category_names into the column index:

In [35]: df1
Out[35]: 
   amount cat
0       0   A
1       1   B
2       2   C

In [36]: df1.set_index('cat').T
Out[36]: 
cat     A  B  C
amount  0  1  2

Once the category names (cat, above) are in the column index, you can concatenate the reshaped DataFrame with the second DataFrame using append or `pd.concat.

pd.concat fills missing values with NaN. Use fillna(0) to replace the NaNs with 0.

import numpy as np
import pandas as pd

df1 = pd.DataFrame({'amount': range(3), 'cat': list('ABC')})
df2 = pd.DataFrame(np.arange(2*4).reshape(2, 4), columns=list('ABCD'))
result = df2.(df1.set_index('cat').T).fillna(0)
print(result)

yields

        A  B  C    D
0       0  1  2  3.0
1       4  5  6  7.0
amount  0  1  2  0.0

Upvotes: 0

B. M.
B. M.

Reputation: 18658

Just append and replace Nan :

pd.DataFrame(columns=products).append(df.T).fillna(0)

Upvotes: 0

Related Questions