I have pandas data frame url like
location dom_category 3 'edu' 3 'gov' 3 'edu' 4 'org' 4 'others' 4 'org'
and i want this data frame to be like
location edu gov org others 3 2 1 0 0 4 0 0 2 1
the edu,gov,org and others contains the count for specific location. i have right the code but i know its not the optimized
url['val']=1 url_final=url.pivot_table(index=['location'],values='val',columns= ['dom_category'],aggfunc=np.sum)
score:6
First if necessary remove '
by str.strip
.
Then use groupby
with aggregating size
and reshape by unstack
:
df['dom_category'] = df['dom_category'].str.strip("\'")
df = df.groupby(['location','dom_category']).size().unstack(fill_value=0)
print (df)
dom_category edu gov org others
location
3 2 1 0 0
4 0 0 2 1
Or use pivot_table
:
df['dom_category'] = df['dom_category'].str.strip("\'")
df=df.pivot_table(index='location',columns='dom_category',aggfunc='size', fill_value=0)
print (df)
dom_category edu gov org others
location
3 2 1 0 0
4 0 0 2 1
Last is possible convert index to column and remove columns name dom_category
by reset_index
+ rename_axis
:
df = df.reset_index().rename_axis(None, axis=1)
print (df)
location edu gov org others
0 3 2 1 0 0
1 4 0 0 2 1
score:2
Let's use str.strip
, get_dummies
and groupby
:
df['dom_category'] = df.dom_category.str.strip("\'")
df.assign(**df.dom_category.str.get_dummies()).groupby('location').sum().reset_index()
Output:
location edu gov org others
0 3 2 1 0 0
1 4 0 0 2 1
score:3
Using groupby
and value_counts
House Keeping
get rid of '
df.dom_category = df.dom_category.str.strip("'")
Rest of Solution
df.groupby('location').dom_category.value_counts().unstack(fill_value=0)
dom_category edu gov org others
location
3 2 1 0 0
4 0 0 2 1
To get the formatting just right
df.groupby('location').dom_category.value_counts().unstack(fill_value=0) \
.reset_index().rename_axis(None, 1)
location edu gov org others
0 3 2 1 0 0
1 4 0 0 2 1
Credit To: stackoverflow.com
Related Query
- pivot_table with group and without value field
- Bar plot from pivot table with grand total and percentage per group aggregation
- Pivot table with duplicate indexes and without an aggregation function in Pandas
- Group by Year and Month Panda Pivot Table
- Python/Pandas - How to group by two columns and count rows with value from third column between two numbers
- Pandas: Pivot table without sorting index and columns
- Join Two Pivot Table and obtain multi value per cell in panda
- Pivot table and group by month
- Pandas Pivot Table Column with empty value do not show
- How to shift the pandas column value with group by and create new column using python?
- First non-null value per group in a table with many columns
- How to pivot table with unique names and avoid null values?
- Group and sum data by common prefix from column value with different length prefixes and inconsistent delimiters in a Pandas dataframe
- Group consecutive events with same atributes and calculate the cumulative value of other column
- pandas pivot table with parameter "columns" but no value for each category of the column
- Pivot Table in Pandas with two column(Index and Value)
- How to pivot a table and obtain the total with Pandas?
- How to group phone number with and without country code
- How to create a pivot table uing python pandas with column entries pivoted to column heading and a new column for blank entries?
- How do I get the maximum value for every group and rank with all other groups?
- How to get first and last value of each group in pandas with no group by column?
- Creating pivot table in pandas with different functions for each value
- Pandas - Pivot and Rearrange Table With Multiple Labels in Same Header
- how to pivot pandas dataframe with multiple aggregate field and multiple index fields to sumIfs in python?
- Duplicate entries and rename column row in pandas pivot table without aggregation
- Pandas group by with multiple columns and max value
- Count how many times a value of a column changes for more than n consecutive times, together with the changes, with group by, and condition in pandas
- How to select data from Pandas pivot table and fill missing values with 0?
- Pandas Multiindex Pivot Table Date Format Change with Sorting and Loss of Precision
- group by two columns and use third column as value without using pivot_table
More Query from same tag
- Python pandas data frame warning, suggest to use .loc instead?
- How can I add rows for all dates between two columns?
- How to parse and evaluate a math expression with Pandas Dataframe columns?
- Finding local minimum values in pandas
- Can I force pandas to return a view when I query a dataframe?
- text response from get request into a python pandas data frame excluding begin and end lines
- Return dataframe rows where the values in a column are not of type date
- How can I conditionally transform a pandas dataframe column
- Select first row that meets certain condition
- Sort Pandas dataframe according to list of column names
- How to calculate shift and rolling sum over missing dates without adding them to data frame in Pandas?
- Date is not displayed correctly when plot pandas dataframe
- Output pandas grouped dataframe without aggregation
- Getting month's end data in python
- Model decay in pandas data frame