Search code examples
pythonpandasdataframejoinconcatenation

How can I merge two data frames when I need to combine the information of a row to columns?

I have two data frames, the first one contains statistics for a product and the second one is an empty data frame I created. I need to take the data from the first dataframe and insert it into the second. I only know how to join data from columns and rows, is there a way for me to do this?

product = 'PRDQM0011122TR131'
prod_results = pm.summary(trace_dict_prodlevel[product])
prod_results

          | mean     | sd                 |
          | -------- | --------           |
Slope     | -0.012   | -0.012            |
 Intercept| 0.906    | 0.356              |

prod_results1 = pd.DataFrame(columns = ['product', 'slope_mean', 'slope_sd', 'int_mean', 'int_sd'])
prod_results1

| product | slope_mean | slope_sd  |int_mean |int_sd|
|---------|------------|-----------|---------|------|

the desired output would be this:

| product | slope_mean | slope_sd |int_mean |int_sd|

|PRDQM0011122TR131|-0.012 |-0.012 |0.906 |0.356 |

I thought pd.concat or pd.join would work but I don't know how to apply them to this case.

Solution

  • You could input data directly from your prod_results dataframe to your prod_results1 dataframe. This works and given the case is simple, using something like melt might be too much.

    Since you do not provide code to reproduce your data, this is my guess for working code:

    prod_results1['product'] = product
prod_results1['slope_mean'] = prod_results.loc[prod_results.index=='Slope', 'mean']
prod_results1['slope_sd'] = prod_results.loc[prod_results.index=='Slope', 'sd']
prod_results1['int_mean'] = prod_results.loc[prod_results.index=='Intercept', 'mean']
prod_results1['int_sd'] = prod_results.loc[prod_results.index=='Intercept', 'sd']