Transform Pandas Dataset into its Values and Column Value

Question

I Have a dictionary that I want to transform into a pandas dataframe with two columns (value and column name).

mydict = {'government agency': ['FBI', 'CIA','NSA'], 'international organization': ['EU', 'NATO', 'World Bank'], 'company': ['Amazon', 'Google', 'Microsoft']}

mydict=pd.DataFrame(mydict)

I want the table to be formatted like so

Instead of how pandas reads it:

does my dictionary data structure need to be changed or is this something that can be implemented in pandas (i.e. pandas.melt)?

Hi @coolio don' forget you can upvote and accept answers, see [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers), thanks :) — yatu, Aug 22 '19 at 13:56

score 3 · Accepted Answer · answered Aug 22 '19 at 12:39

Build the dataframe and use melt:

pd.DataFrame(mydict).melt(var_name='Column')

                  Column             value
0           government agency         FBI
1           government agency         CIA
2           government agency         NSA
3  international organization          EU
4  international organization        NATO
5  international organization  World Bank
6                     company      Amazon
7                     company      Google
8                     company   Microsoft

2

Cool, didn't know that one. – Artur Aug 22 '19 at 12:46

score 3 · Answer 2 · answered Aug 22 '19 at 12:43

Here's a one-liner:

import pandas

mydict = {'government agency': ['FBI', 'CIA','NSA'], 'international organization': ['EU', 'NATO', 'World Bank'], 'company': ['Amazon', 'Google', 'Microsoft']}
df = pandas.DataFrame(data=[(k,v) for k in mydict for v in mydict[k]], columns=["value", "column"])

Output:

                        value      column
0                     company      Amazon
1                     company      Google
2                     company   Microsoft
3           government agency         FBI
4           government agency         CIA
5           government agency         NSA
6  international organization          EU
7  international organization        NATO
8  international organization  World Bank

Transform Pandas Dataset into its Values and Column Value

2 Answers2