I have a dataset that I would like to explore but it is not structured very well. The original excel had the Region and Variety of grape in the one column, the Region was indicated as the heading for the rows beneath by being in bold. When I loaded it into Python you can't tell which rows were regions or grape varieties.

Ideally I want to have those two columns separated so my ideal dataframe looks like table 2.

What I have done so far is add a 'is\_region" column and put "Yes" for values in the 'Region/variety' column that match my list of regions.

```
grapeyield_df = pd.read_excel (r'Regional varietal area, production and price data, 1999 to 2013.xlsx', 'Yield_since2001_tab',skiprows=[0])

regions = ['Adelaide Hills','Adelaide Plains','Alpine Valleys','Alpine Valleys/Beechworth','Australian Capital Territory','Barossa -other','Barossa Valley']

grapeyield_df["is_region"] = np.where(grapeyield_df["Region/Variety"].isin(regions), "Yes", "No")

```

Table 1:

| Region/Variety | 2011 | is\_region |
| --- | --- | --- |
| Adelaide Hills | 3452 | Yes |
| Chardonnay | 26357 | No |
| Pinot Grigio | 7876 | No |
| Barossa Valley | 7368 | Yes |

Table 2:

| Region | Variety | 2011 |
| --- | --- | --- |
| Adelaide Hills | Chardonnay | 26357 |
| Adelaide Hills | Pinot Grigio | 7876 |
| Barossa Valley | Chardonnay | 8787 |