Is there a way to include the column (not rows!) index name in the output when calling Pandas' dataframe.to_csv() method? For example:
import pandas as pd
iris = pd.read_csv('https://raw.githubusercontent.com/mwaskom/seaborn-data/master/iris.csv')
pivot_iris = iris.pivot_table(index='species', columns='sepal_length', values='sepal_width')
print(pivot_iris.columns)
print(pivot_iris)
pivot_iris.to_csv('pivot_iris.csv', index=True, header=True)
After calling pivot, the column index name is set to sepal_length as you can see in the prints
Index([4.3, 4.4, 4.5, 4.6, 4.7, 4.8, 4.9, 5.0, 5.1, 5.2, 5.3, 5.4, 5.5, 5.6,
5.7, 5.8, 5.9, 6.0, 6.1, 6.2, 6.3, 6.4, 6.5, 6.6, 6.7, 6.8, 6.9, 7.0,
7.1, 7.2, 7.3, 7.4, 7.6, 7.7, 7.9],
dtype='float64', name='sepal_length')
and
sepal_length 4.3 4.4 4.5 4.6 4.7 ... 7.3 7.4 7.6 7.7 7.9
species ...
setosa 3.0 3.033333 2.3 3.325 3.2 ... NaN NaN NaN NaN NaN
versicolor NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN
virginica NaN NaN NaN NaN NaN ... 2.9 2.8 3.0 3.05 3.8
[3 rows x 35 columns]
Unfortunately the output file produced with to_csv() is missing the label in front of the column names:
species,4.30,4.40,4.50,4.60,4.70,4.80,4.90,5.00,5.10,5.20,5.30,5.40,5.50,5.60,5.70,5.80,5.90,6.00,6.10,6.20,6.30,6.40,6.50,6.60,6.70,6.80,6.90,7.00,7.10,7.20,7.30,7.40,7.60,7.70,7.90
setosa,3.00,3.03,2.30,3.33,3.20,3.18,3.20,3.36,3.60,3.67,3.70,3.66,3.85,,4.10,4.00,,,,,,,,,,,,,,,,,,,
versicolor,,,,,,,2.40,2.15,2.50,2.70,,3.00,2.44,2.82,2.82,2.67,3.10,2.80,2.88,2.55,2.70,3.05,2.80,2.95,3.07,2.80,3.10,3.20,,,,,,,
virginica,,,,,,,2.50,,,,,,,2.80,2.50,2.73,3.00,2.60,2.80,3.10,2.93,2.92,3.05,,3.04,3.10,3.13,,3.00,3.27,2.90,2.80,3.00,3.05,3.80
is there a way to include it?
You can't really include the index names in a CSV.
What you could do is to create a MultiIndex:
pivot_iris = (pd.concat({'sepal_length':
iris.pivot_table(index='species', columns='sepal_length', values='sepal_width')},
axis=1)
.rename_axis(columns=(None, 'species')).reset_index()
)
pivot_iris.to_csv('pivot_iris.csv', index=False)
Output:
species,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length,sepal_length
,4.3,4.4,4.5,4.6,4.7,4.8,4.9,5.0,5.1,5.2,5.3,5.4,5.5,5.6,5.7,5.8,5.9,6.0,6.1,6.2,6.3,6.4,6.5,6.6,6.7,6.8,6.9,7.0,7.1,7.2,7.3,7.4,7.6,7.7,7.9
setosa,3.0,3.03,2.3,3.32,3.2,3.18,3.2,3.36,3.6,3.67,3.7,3.66,3.85,,4.1,4.0,,,,,,,,,,,,,,,,,,,
versicolor,,,,,,,2.4,2.15,2.5,2.7,,3.0,2.44,2.82,2.82,2.67,3.1,2.8,2.88,2.55,2.7,3.05,2.8,2.95,3.07,2.8,3.1,3.2,,,,,,,
virginica,,,,,,,2.5,,,,,,,2.8,2.5,2.73,3.0,2.6,2.8,3.1,2.93,2.92,3.05,,3.04,3.1,3.13,,3.0,3.27,2.9,2.8,3.0,3.05,3.8