Converting a data frame into tables with indexes

user_10836309 0
Viewed 62

I have the following sample data frame:

import pandas as pd
array = {'name': ['John', 'Rob', 'Rob', 'John'], 'color': ['Green', 'Black', 'Blue', 'Black']}
df = pd.DataFrame(array)
df

It looks like this:

    name    color
0   John    Green
1   Rob     Black
2   Rob     Blue
3   John    Black

I am looking for a way to create three separate tables or data frames:

Main:

    name    color
0    1       1
1    2       2
2    2       3
3    1       2

Names:

        name
   1    John
   2    Rob

Colors:

        color
   1    Green
   2    Black
   3    Blue
pandas dataframe
2 Answers

Use factorize with unique values and then Series.map:

a1, b1 = pd.factorize(df['color'].unique())
colors = pd.DataFrame({'color':b1}, index=a1+1)
print (colors)
   color
1  Green
2  Black
3   Blue

a2, b2 = pd.factorize(df['name'].unique())
names = pd.DataFrame({'name':b2}, index=a2+1)
print (names)
   name
1  John
2   Rob

main = df.copy()
main['name'] = main['name'].map(dict(zip(b2, a2+1)))
main['color'] = main['color'].map(dict(zip(b1, a1+1)))
print (main)
   name  color
0     1      1
1     2      2
2     2      3
3     1      2
user_29010020

You can try the categorical Pandas type.

The simplest way if you are OK with different category codes than the ones in your question

Note: The Pandas category type is indexing from 0 and categories are getting those codes based on order

Cast a Pandas object to the category type:

df = df.astype("category")

Get codes for every category:

df.apply(lambda x: x.cat.codes)

Out:
   name  color
0     0      2
1     1      0
2     1      1
3     0      0

Get names and colors:

df["name"].cat.categories.to_frame(index=False, name="name")

Out:
    name
0   John
1   Rob

and

df["color"].cat.categories.to_frame(index=False, name="color")

Out:
    color
0   Black
1   Blue
2   Green

If you want to use the category codes from the question indexed from 1

Note: This is not ideal when you have a lot of categories where you want to define a custom order, because you will have to always set the order; see below. But it might be solved with some utility function according to your needs.

You can use pd.CategoricalDtype.

Set dtypes (with the preferred category order):

from pandas.api.types import CategoricalDtype

df.name = df.name.astype(CategoricalDtype(["John", "Rob"]))
df.color = df.color.astype(CategoricalDtype(["Green", "Black", "Blue"]))

Get codes ("+1" because we want to index from 1, not 0):

df.apply(lambda x: x.cat.codes + 1)

Out:
   name  color
0     1      1
1     2      2
2     2      3
3     1      2

Get names and colors:

categories = df["name"].cat.categories
pd.DataFrame({"name": categories}, index=pd.RangeIndex(1, len(categories) + 1))

Out:
    name
1   John
2   Rob

and

categories = df["color"].cat.categories
pd.DataFrame({"color": categories}, index=pd.RangeIndex(1, len(categories) + 1))

Out:
    color
1   Green
2   Black
3   Blue
user_79640980
Related