I'm looking for a way to transform table in pandas, what i have:
| date | BMW | Audi | Mercedes |
|---|---|---|---|
| 01.05.2020 | 100 | 111 | 1 |
| 02.05.2020 | 200 | 222 | 2 |
| 03.05.2020 | 300 | 333 | c |
here's what i need:
| car | date | value |
|---|---|---|
| BMW | 01.05.2020 | 100 |
| BMW | 02.05.2020 | 200 |
| BMW | 03.05.2020 | 300 |
| Audi | 01.05.2020 | 111 |
| Audi | 02.05.2020 | 222 |
| Audi | 03.05.2020 | 333 |
| Mercedes | 01.05.2020 | 1 |
| Mercedes | 02.05.2020 | 2 |
| Mercedes | 03.05.2020 | 3 |
I found some solution, but i am not sure it is good:
import pandas as pd
df = pd.read_excel(r".\test.xlsx")
dfs = []
for col in df:
if col == "date":
continue
df1 = df.loc[:, ['date', col]]
df1.rename(columns={col : "value"}, inplace = True)
df1['car'] = col
dfs.append(df1)
final_df = pd.concat(dfs, axis=0, ignore_index=True)
please let me know if there is a better way to do this, thanks!
