0

I have this dataframe:

import pandas as pd
import numpy as np
import datetime

def creatingDataFrame():

    dateList=[]
    dateList.append(datetime.date(2002,1,1))
    dateList.append(datetime.date(2002,2,1))
    dateList.append(datetime.date(2002,1,1))
    dateList.append(datetime.date(2002,1,1))
    dateList.append(datetime.date(2002,2,1))



    raw_data = {'Date': dateList,            
                'Company': ['A', 'B', 'B', 'C' , 'C'],                
                'var1': [10, 20, 30, 40 , 50]}

    df = pd.DataFrame(raw_data, columns = ['Date','Company', 'var1'])
    df.loc[1, 'var1'] = np.nan
    return df


if __name__=="__main__":
    df=creatingDataFrame()
    print(df)
         Date Company  var1
0  2002-01-01       A  10.0
1  2002-02-01       B   NaN
2  2002-01-01       B  30.0
3  2002-01-01       C  40.0
4  2002-02-01       C  50.0

I would like to transform this data in the following dataframe:

        Date      A   B   C
0  2002-01-01     10  30 40
1  2002-02-01     NaN NaN 50

The only way that I see how to do that is by filtering by each company and merging each result, but there must be an easy way to do that (?).

DanielTheRocketMan
  • 3,199
  • 5
  • 36
  • 65

1 Answers1

1

Use pivot:

df.pivot(index='Date', columns='Company', values='var1')

Company        A     B     C
Date                        
2002-01-01  10.0  30.0  40.0
2002-02-01   NaN   NaN  50.0
sacuL
  • 49,704
  • 8
  • 81
  • 106