Here is the dataframe of hourly traffic counts for van, truck:
| Date | Hour | Direction | Van | Truck |
|---|---|---|---|---|
| 2019-02-01 | 0 | EB | 1 | 2 |
| 2019-02-01 | 0 | WB | 3 | 4 |
Below is the table I desire:
| Date | Hour | Direction | Class | Count |
|---|---|---|---|---|
| 2019-02-01 | 0 | EB | Van | 1 |
| 2019-02-01 | 0 | EB | Truck | 2 |
| 2019-02-01 | 0 | WB | Van | 3 |
| 2019-02-01 | 0 | WB | Truck | 4 |
I tried simple df.stack() but failed, should I define more in stack()?
import pandas as pd
df = pd.DataFrame({
'Date': {0: '2019-02-01', 1: '2019-02-01'}, 'Hour': {0: 0, 1: 0},
'Direction': {0: 'EB', 1: 'WB'}, 'Van': {0: 1, 1: 3}, 'Truck': {0: 2, 1: 4}
})
stacked = df.set_index(['Date', 'Hour', 'Direction']).stack() \
.reset_index().rename(columns={'level_3': 'Class', 0: 'Count'})
print(stacked)
stacked:
Date Hour Direction Class Count
0 2019-02-01 0 EB Van 1
1 2019-02-01 0 EB Truck 2
2 2019-02-01 0 WB Van 3
3 2019-02-01 0 WB Truck 4
Or melt:
melted = df.melt(id_vars=['Date', 'Hour', 'Direction'],
var_name='Class',
value_name='Count')
print(melted)
melted:
Date Hour Direction Class Count
0 2019-02-01 0 EB Van 1
1 2019-02-01 0 WB Van 3
2 2019-02-01 0 EB Truck 2
3 2019-02-01 0 WB Truck 4
