0

I have the following dataframe 

import pandas as pd
       df = pd.DataFrame({'fmc': [1, 2],
                       'id_r': [1, 1],
                       'id_b': ['a', 'b'],
                       'id_c': ['br', 'br'],
                       'fmc_': ['aa_bb', 'cc_dd']})

I would like to dcast this df with index=['id_r', 'id_b', 'id_c'] and values='fmc'.

I would like the output to be like 

import numpy as np
        dff = pd.DataFrame({'id_r': [1, 1],
                      'id_b': ['a', 'b'],
                      'id_c': ['br', 'br'],
                      'fmc_aa_bb':[1, np.nan],
                      'fmc_cc_dd':[np.nan, 2]})

I followed a previous question of mine

  df = df.pivot_table(index=['id_r', 'id_b', 'id_c'], columns='fmc_', values='fmc')
        df.columns = df.columns.map('_'.join)
        df = df.reset_index()

But does not give the desired output.

Any help ?

quant
  • 4,062
  • 5
  • 29
  • 70

1 Answers1

1

Modify your code with add_prefix

s=df.pivot_table(index=['id_r', 'id_b', 'id_c'], columns='fmc_', values='fmc').add_prefix('fmc_').reset_index()
Out[190]: 
fmc_  id_r id_b id_c  fmc_aa_bb  fmc_cc_dd
0        1    a   br        1.0        NaN
1        1    b   br        NaN        2.0
BENY
  • 317,841
  • 20
  • 164
  • 234
  • Thank you ! What is this `fmc_` column at the beginning ? – quant Oct 24 '19 at 15:18
  • @quantq that is column name , you can do rename_axis(None) – BENY Oct 24 '19 at 15:19
  • Adding it to the end ? like `df.pivot_table(index=['id_r', 'id_b', 'id_c'], columns='fmc_', values='fmc').add_prefix('fmc_').reset_index().rename_axis(None)` ? because this gives the same result – quant Oct 24 '19 at 15:22
  • 1
    @quant `df.pivot_table(index=['id_r', 'id_b', 'id_c'], columns='fmc_', values='fmc').add_prefix('fmc_').reset_index().rename_axis(None,axis=1) ` – BENY Oct 24 '19 at 15:24