I have extracted data from the web as a table. This is the format of the table I got:
| Locality | Price |
|---|---|
| Loc1 | |
| 2001 | 102 |
| 2002 | 105 |
| Loc2 | |
| 2001 | 206 |
| 2002 | 209 |
| Loc3 | |
| 2001 | 412 |
| 2002 | 585 |
I need to reshape this table as:
| Locality | Year | Price |
|---|---|---|
| Loc1 | 2001 | 102 |
| Loc1 | 2002 | 105 |
| Loc2 | 2001 | 206 |
| Loc2 | 2002 | 209 |
| Loc3 | 2001 | 412 |
| Loc3 | 2001 | 585 |
I am using pandas library in python. Could anyone suggest me a script for that?
Thanks
CodePudding user response：
Some this like this? Separate the year based on the null values and forward fill the locality and finally dropna rows.
import numpy as np
df = df.assign(Year=np.where(df.Price.notnull(), df.Locality, np.nan))
df = df.assign(Locality=np.where(df.Year.notnull(), np.nan, df.Locality))
df['Locality'] = df['Locality'].fillna(method='ffill')
df.dropna(subset = ['Year'], axis=0)
Locality Price Year
1 Loc1 102.0 2001
2 Loc1 105.0 2002
4 Loc2 206.0 2001
5 Loc2 209.0 2002
7 Loc3 412.0 2001
8 Loc3 585.0 2002