Breaking down a column in Pandas into a separate CSV for display in Tableau My data is coming from a CSV, which should be visualized in Tableau.

However, the data contains the column category_list, which consists of values separated by a vertical bar (|).

Since Tableau can't handle arrays inside of attributes, I used Python (Pandas) to load the CSV and manipulate the data:

import pandas as pd
companies = pd.read_csv("companies.csv")


I assume that the category_list column needs to be broken down and stored into another CSV (containing the permalink (unique ID) and category pairs).

Something like this:

permalink,category
/organization/-qounter,Application Platforms
/organization/-qounter,Real Time
/organization/-qounter,Social Network Media
/organization/-the-one-of-them-inc-,Apps
/organization/-the-one-of-them-inc-,Games
/organization/-the-one-of-them-inc-,Mobile
/organization/1-4-all,Entertainment
/organization/1-4-all,Games
/organization/1-4-all,Software
/organization/1-800-publicrelations-inc-,Internet
/organization/1-800-publicrelations-inc-,Marketing
/organization/1-800-publicrelations-inc-,Media
/organization/1-800-publicrelations-inc-,Public Relations
/organization/1-mainstream,Apps
/organization/1-mainstream,Cable
/organization/1-mainstream,Distribution
/organization/1-mainstream,Software
...


How to achieve it?

Excerpt of the original CSV:

permalink,category_list,...
/organization/-qounter,Application Platforms|Real Time|Social Network Media,...
/organization/-the-one-of-them-inc-,Apps|Games|Mobile,...
/organization/1-4-all,Entertainment|Games|Software,...
/organization/1-800-publicrelations-inc-,Internet|Marketing|Media|Public Relations,...
/organization/1-mainstream,Apps|Cable|Distribution|Software,...
...
If you don't need other columns, here is a solution. It splits the column, stacks in vertically and combines with "permalink" column

df.set_index('permalink').category_list.str.split('|', expand=True).stack().reset_index('permalink').rename(columns={0:'category'})

permalink   category
0   /organization/-qounter  Application Platforms
1   /organization/-qounter  Real Time
2   /organization/-qounter  Social Network Media
0   /organization/-the-one-of-them-inc- Apps
1   /organization/-the-one-of-them-inc- Games
2   /organization/-the-one-of-them-inc- Mobile
0   /organization/1-4-all   Entertainment
1   /organization/1-4-all   Games
2   /organization/1-4-all   Software
0   /organization/1-800-publicrelations-inc-    Internet
1   /organization/1-800-publicrelations-inc-    Marketing
2   /organization/1-800-publicrelations-inc-    Media
3   /organization/1-800-publicrelations-inc-    Public Relations
0   /organization/1-mainstream  Apps
1   /organization/1-mainstream  Cable
2   /organization/1-mainstream  Distribution
3   /organization/1-mainstream  Software


Then you can save it to csv.