CODEWITHSUNDEEP
pythonpandasdataframe
user12252991
user12252991

Reputation: 115

Make new dataframe from existing dataframe columns

I have one Dataframe1 from which I need to form new Dataframe2 as given below. Column n1 will pick the value from Status column in Dataframe1 if Dataframe1.name = A and similarly column n2 will take the value from column Status if Dataframe1.Name = B. Also, Timestamp and id will have unique values. Can anybody please help?

Input Dataframe1:

id Timestamp Name Status
1 02:15:00 A FALSE
1 02:15:00 B TRUE
2 03:00:00 A TRUE
2 03:00:00 B FALSE

Output Dataframe2:

id Timestamp n1 n2
1 02:15:00 FALSE TRUE
2 03:00:00 TRUE FALSE

Upvotes: 2

Views: 546

Answers (3)

Wilian
Wilian

Reputation: 1257

using pivot_table and then adjusting the result header.

import pandas as pd
df = pd.read_excel('test.xls', index_col = False)

df2 = df.pivot_table(index = ['id', 'Timestamp'], columns = 'Name', values = 'Status').reset_index().rename_axis(None, axis=1).rename(columns = {'A': 'n1', 'B': 'n2'})


print(df2)

Upvotes: 0

alparslan mimaroğlu
alparslan mimaroğlu

Reputation: 1490

What you are trying to do is taking a pivot of the data with special names. If you rename A and b values as you want to n1 and n2 only thing you have to do is to use the pandas.pivot_table function. because as its aggregation function it uses mean strings don't work out of the box. You have to provide your own aggregation function. Because in our situation every row is unique we can just give the aggregation function to take the value of that row.

dataframe1['Name'] = dataframe1['Name'].replace({'A': 'n1', 'b': 'n2'})
dataframe1.pivot_table(index=['id', 'Timestamp'], 
                       columns='Name', 
                       values='Status', 
                       aggfunc=lambda x:x).reset_index()

Upvotes: 1

I'mahdi
I'mahdi

Reputation: 24069

You can use pandas.pivot_table:

df2 = df.pivot_table(index=['id','Timestamp'], columns='Name', values='Status').reset_index().set_index('id')
df2.columns = ['Timestamp','n1','n2']

Output:

>>> df2
    Timestamp   n1     n2
id  
1   02:15:00    FALSE   TRUE
2   03:00:00    TRUE    FALSE

Upvotes: 0

Related Questions