CODEWITHSUNDEEP
pythonpandasdataframepivottranspose
Mine
Mine

Reputation: 861

Convert specific columns to rows by column order and keep the rest of the columns same in Python

I have a dataframe that looks like this:

Screenshot of the dataframe


df = pd.DataFrame({'Date': {0: '01/08/2016 0:00', 1: '01/08/2016 1:00', 2: '01/08/2016 2:00'},
 'amount_1': {0: 29189, 1: 27614, 2: 26579},
 'amount_2': {0: 26277, 1: 24992, 2: 23533},
 'target': {0: 28602, 1: 27112, 2: 25975}})

I want to keep Date and target columns the same and transpose amount_1 and amount_2 rows while keeping their order. So that 3 columns after Date column belong to amount_1 for each hour then comes amount_2 columns for each hour.

This is what I tried so far.


df_t = df.melt(id_vars=['Date','target']).drop('variable', 1).sort_values(['Date','target'])
df_t.T

My desired output is:


df_t = pd.DataFrame({'Date': {0: '01/08/2016 0:00', 1: '01/08/2016 1:00', 2: '01/08/2016 2:00'},
 'amount_1_hour0': {0: 29189, 1: 29189, 2: 29189},
 'amount_1_hour1': {0: 27614, 1: 27614, 2: 27614},
 'amount_1_hour2': {0: 26579, 1: 26579, 2: 26579},
 'amount_2_hour0': {0: 26277, 1: 26277, 2: 26277},
 'amount_2_hour1': {0: 24992, 1: 24992, 2: 24992},
 'amount_2_hour2': {0: 23533, 1: 23533, 2: 23533},
 'target': {0: 28602, 1: 27112, 2: 25975}})

Screenshot of the output

Upvotes: 1

Views: 579

Answers (1)

jezrael
jezrael

Reputation: 863301

I think you need:

#convert values to datetimes
df['Date'] = pd.to_datetime(df['Date'])
#create hour column
df['h'] = df['Date'].dt.hour

#melting by 3 columns
df = df.melt(id_vars=['Date','target', 'h'])
#add hours to amounts strings (variable column)
df['variable'] += '_hour' + df['h'].astype(str)
#pivoting
df = df.pivot_table(index=['Date','target'], columns='variable', values='value').reset_index()
#replace missing values per days
df = df.groupby(df['Date'].dt.date).apply(lambda x: x.ffill().bfill())
print (df)
variable                Date  target  amount_1_hour0  amount_1_hour1  \
0        2016-01-08 00:00:00   28602         29189.0         27614.0   
1        2016-01-08 01:00:00   27112         29189.0         27614.0   
2        2016-01-08 02:00:00   25975         29189.0         27614.0   

variable  amount_1_hour2  amount_2_hour0  amount_2_hour1  amount_2_hour2  
0                26579.0         26277.0         24992.0         23533.0  
1                26579.0         26277.0         24992.0         23533.0  
2                26579.0         26277.0         24992.0         23533.0

EDIT:

df = pd.DataFrame({'Date': {0: '01/08/2016 0:00', 1: '01/08/2016 2:00', 2: '01/08/2016 10:00'},
 'amount_1': {0: 29189, 1: 27614, 2: 26579},
 'amount_2': {0: 26277, 1: 24992, 2: 23533},
 'target': {0: 28602, 1: 27112, 2: 25975}})
print (df)
               Date  amount_1  amount_2  target
0   01/08/2016 0:00     29189     26277   28602
1   01/08/2016 2:00     27614     24992   27112
2  01/08/2016 10:00     26579     23533   25975

#convert values to datetimes
df['Date'] = pd.to_datetime(df['Date'])
#create hour column
df['h'] = df['Date'].dt.hour

#melting by 3 columns
df = df.melt(id_vars=['Date','target', 'h'])
#pivoting
df = df.pivot_table(index=['Date','target'], columns=['variable','h'], values='value')
#join MultiIndex with hours
df.columns = df.columns.map(lambda x: f'{x[0]}_hour{x[1]}')
df = df.reset_index()
#replace missing values per days
df = df.groupby(df['Date'].dt.date).apply(lambda x: x.ffill().bfill())
print (df)
                 Date  target  amount_1_hour0  amount_1_hour2  \
0 2016-01-08 00:00:00   28602         29189.0         27614.0   
1 2016-01-08 02:00:00   27112         29189.0         27614.0   
2 2016-01-08 10:00:00   25975         29189.0         27614.0   

   amount_1_hour10  amount_2_hour0  amount_2_hour2  amount_2_hour10  
0          26579.0         26277.0         24992.0          23533.0  
1          26579.0         26277.0         24992.0          23533.0  
2          26579.0         26277.0         24992.0          23533.0

Upvotes: 2

Related Questions