Rotate pandas dataframe to create a (marine) heatmap
I am new to Python and fairly new to SO.
I have a pandas dataframe named df that looks like this:
Text
Date Location
2015-07-08 San Diego, CA 1
2015-07-07 Bellevue, WA 1
Los Angeles, CA 1
New York, NY 1
Los Angeles, CA 1
Unknown 1
I want to collapse data using:
import pandas, numpy as np
df_pivoted = df.pivot_table(df, values=['Text'], index=['Date'],
columns=['Location'],aggfunc=np.sum)
The idea is to create a heatmap that shows the amount of "text" by "Location" and "Date".
I get an error:
TypeError: pivot_table() got multiple values for keyword argument 'values'
When using a simplified approach:
df = df.pivot_table('Date', 'Location', 'Text')
I get an error:
raise DataError('No numeric types to aggregate')
I am using Python 2.7 and pandas 0.16.2
In[2]: df.dtypes
Out[2]:
Date datetime64[ns]
Text object
Location object
dtype: object
Anyone have an idea?
+3
source to share
1 answer
import pandas as pd
import numpy as np
# just try to replicate your dataframe
# ==============================================
date = ['2015-07-08', '2015-07-07', '2015-07-07', '2015-07-07', '2015-07-07', '2015-07-07']
location = ['San Diego, CA', 'Bellevue, WA', 'Los Angeles, CA', 'New York, NY', 'Los Angeles, CA', 'Unknown']
text = [1] * 6
df = pd.DataFrame({'Date': date, 'Location': location, 'Text': text})
Out[141]:
Date Location Text
0 2015-07-08 San Diego, CA 1
1 2015-07-07 Bellevue, WA 1
2 2015-07-07 Los Angeles, CA 1
3 2015-07-07 New York, NY 1
4 2015-07-07 Los Angeles, CA 1
5 2015-07-07 Unknown 1
# processing
# ==============================================
pd.pivot_table(df, index='Date', columns='Location', values='Text', aggfunc=np.sum)
Out[142]:
Location Bellevue, WA Los Angeles, CA New York, NY San Diego, CA Unknown
Date
2015-07-07 1 2 1 NaN 1
2015-07-08 NaN NaN NaN 1 NaN
+3
source to share