Python Pandas Reformat stacked columns to long(?) format

user_16374337 0
Viewed 34

I have a csv file that looks like this:

Current csv

And I want it to look like this:

Desired Format

Basically, the country, month, year, and code would repeat, and the Valor export and Volumen export would be unique for the row.. The ending columns can be in any order.

I have tried doing a series of melt, pivot, transpose etc. with no luck. Can any please provide any guidance? Any hints would be greatly appreciated. I am just stumped how to deal with the original csv having the year/month/export value being "stacked"...

Here is the dictionary that can be used to recreate the original csv as a Pandas Data Frame:

pd.DataFrame([{'Unnamed: 0': np.nan, 'Unnamed: 1': np.nan, '2017': 'Enero', 'Unnamed: 3': np.nan, 'Unnamed: 4': 'Febrero', 'Unnamed: 5': np.nan}, {'Unnamed: 0': np.nan, 'Unnamed: 1': np.nan, '2017': 'Valor export', 'Unnamed: 3': 'Volumen export', 'Unnamed: 4': 'Valor export', 'Unnamed: 5': 'Volumen export'}, {'Unnamed: 0': np.nan, 'Unnamed: 1': np.nan, '2017': np.nan, 'Unnamed: 3': np.nan, 'Unnamed: 4': np.nan, 'Unnamed: 5': np.nan}, {'Unnamed: 0': '080390110000 SA-2017', 'Unnamed: 1': 'USA', '2017': '29200.10725', 'Unnamed: 3': '67198.189', 'Unnamed: 4': '38631.16383', 'Unnamed: 5': '87962.196'}, {'Unnamed: 0': '090390110000 SA-2017', 'Unnamed: 1': 'Mexico', '2017': '9283.79255', 'Unnamed: 3': '21638.126', 'Unnamed: 4': '9785.40009', 'Unnamed: 5': '22863.867'}, {'Unnamed: 0': '010390110000 SA-2017 ', 'Unnamed: 1': 'Canada', '2017': '8017.55675', 'Unnamed: 3': '19352.178', 'Unnamed: 4': '11137.27057', 'Unnamed: 5': '27020.428'}, {'Unnamed: 0': '070390110000 SA-2017', 'Unnamed: 1': 'Brazil', '2017': '3786.44363', 'Unnamed: 3': '8704.871', 'Unnamed: 4': '4553.70795', 'Unnamed: 5': '10583.833'}, {'Unnamed: 0': '060390110000 SA-2017', 'Unnamed: 1': 'Italy', '2017': '4809.76636', 'Unnamed: 3': '12411.691', 'Unnamed: 4': '4304.02052', 'Unnamed: 5': '11198.063'}, {'Unnamed: 0': '000390110000 SA-2017 ', 'Unnamed: 1': 'Spain', '2017': '2290.65793', 'Unnamed: 3': '6227.269', 'Unnamed: 4': '3269.41957', 'Unnamed: 5': '9118.595'}, {'Unnamed: 0': '0990390110000 SA-2017 ', 'Unnamed: 1': 'Costa Rica', '2017': '1855.70035', 'Unnamed: 3': '4687.714', 'Unnamed: 4': '2668.57892', 'Unnamed: 5': '6425.365'}, {'Unnamed: 0': '0040390110000 SA-2017 ', 'Unnamed: 1': 'Honduras', '2017': '1823.358', 'Unnamed: 3': '4223.521', 'Unnamed: 4': '250.2036', 'Unnamed: 5': '603.392'}])

Again, any help would be greatly appreciated

melt pandas pivot python
1 Answers

Try:

df.iloc[:, 0] = df.iloc[:, 0].str.strip()

idx1 = pd.MultiIndex.from_product(
    [("2017",), ("Enero", "Febrero"), ("Valor Export", "Volumen Export")],
    names=("Year", "Month", "Export"),
)
idx2 = pd.MultiIndex.from_frame(df.iloc[3:, :2], names=("Code", "Country"))

df2 = (
    pd.DataFrame(df.iloc[3:, 2:].values, columns=idx1, index=idx2)
    .stack(level=(0, 1))
    .reset_index()
)
df2.columns.name = None
print(df2.sort_values(by=["Year", "Month"]).to_markdown(index=False))

Prints:

Code Country Year Month Valor Export Volumen Export
080390110000 SA-2017 USA 2017 Enero 29200.1 67198.2
090390110000 SA-2017 Mexico 2017 Enero 9283.79 21638.1
010390110000 SA-2017 Canada 2017 Enero 8017.56 19352.2
070390110000 SA-2017 Brazil 2017 Enero 3786.44 8704.87
060390110000 SA-2017 Italy 2017 Enero 4809.77 12411.7
000390110000 SA-2017 Spain 2017 Enero 2290.66 6227.27
0990390110000 SA-2017 Costa Rica 2017 Enero 1855.7 4687.71
0040390110000 SA-2017 Honduras 2017 Enero 1823.36 4223.52
080390110000 SA-2017 USA 2017 Febrero 38631.2 87962.2
090390110000 SA-2017 Mexico 2017 Febrero 9785.4 22863.9
010390110000 SA-2017 Canada 2017 Febrero 11137.3 27020.4
070390110000 SA-2017 Brazil 2017 Febrero 4553.71 10583.8
060390110000 SA-2017 Italy 2017 Febrero 4304.02 11198.1
000390110000 SA-2017 Spain 2017 Febrero 3269.42 9118.59
0990390110000 SA-2017 Costa Rica 2017 Febrero 2668.58 6425.36
0040390110000 SA-2017 Honduras 2017 Febrero 250.204 603.392
user_100359850
Related