Dataframe customisation using the values

Question

I have a dataframe that looks like below

I would like to convert this to following dataframe by adding headers and also put the value on last column as header as follows

Any help on this would be appreciated, i tried .groupby but it requires a column name which i dont have now.

What are the current columns, 0, 1, 2? – Meow Dec 25 '19 at 17:02 — Meow, Dec 25 '19 at 17:02
Yes you are right, its 0,1,2 – windowws Dec 25 '19 at 17:05 — windowws, Dec 25 '19 at 17:05

Prayson W. Daniel · Accepted Answer · 2019-12-25T18:49:53.143

4

You can use pandas.pivot_table

import numpy as np 
import pandas as pd

# ... your data in df

df = pd.read_csv(pd.compat.StringIO('''date value data
01/01/2019 30 data1
01/01/2019 40 data2
02/01/2019 20 data1
02/01/2019 10 data2'''), sep=' ')

results = pd.pivot_table(df, values='value', index=['date'],
                     columns=['data'], aggfunc=np.sum, fill_value=0)

print(results)

Results: enter image description here

edited Dec 25 '19 at 18:49

answered Dec 25 '19 at 17:05

Prayson W. Daniel

14,191
4
51
57

this is duplicate question. You shouldn't show images in your answer – ansev Dec 25 '19 at 17:39
Oh! I did not know. Do you know why I shouldn’t not show images? – Prayson W. Daniel Dec 25 '19 at 17:44
1

I think that an image (cut) in which the code you have already shown is partially seen is redundant and unnecessary, just show the output. https://stackoverflow.com/help/how-to-answer – ansev Dec 25 '19 at 17:47
1

@PraysonW.Daniel this worked, thanks for your effort – windowws Dec 25 '19 at 17:48
Windowws you are welcome. @ansev, I have cropped out redundancy:) thanks – Prayson W. Daniel Dec 25 '19 at 18:52

score 0 · Answer 2 · answered Dec 25 '19 at 17:17

df=pd.DataFrame({'X':['01/01/2009','01/01/2009','02/01/2009','02/01/2009'],
                'Y':[20,30,40,25],
                'data':['Data1','Data2','Data1','Data2']})
df.pivot(index='X',columns='data',values='Y')
df.columns = ['Date' , 'Data1' , 'Data2']
df

code snippet

score 0 · Answer 3 · answered Dec 25 '19 at 17:20

Alternative method using DataFrame.stack

import pandas as pd


data = {
    0: ['01-01-2009', '01-01-2009', '02-01-2009', '02-01-2009'],
    1: [20, 30, 40, 25],
    2: ['Data1', 'Data2', 'Data1', 'Data2']
}
df = pd.DataFrame(data)
renamed = df.rename(columns={0: 'date', 1: 'values', 2: 'source'})
new_index = renamed.set_index(['date', 'source'])
result = new_index.unstack(1)['values']

Dataframe customisation using the values

3 Answers3