Fun problem : Doing transpose & join in Python

An interesting problem I came across recently.

Tip(s):

[1] : http://chrisalbon.com/python/pandas_join_merge_dataframe.html

Input

User Level Features (100M rows)

UserId

FeatureName

FeatureValue

a

X

1

a

Y

2

a

Z

3

b

Y

4

b

Z

5

Doc Level Features (100M rows)

DocId

FeatureName

FeatureValue

1

A

1

1

B

0

1

C

1

2

A

0

2

B

1

Labeled Data (10M rows)

UserId

DocId

Label

a

1

1

a

2

0

a

3

1

b

1

0

b

2

1

Expected Output:

User Level Features

Document Level Features

UserId

DocId

Label

X

Y

Z

A

B

C

a

1

1

1

2

3

1

0

1

a

2

0

1

2

3

0

1

a

3

1

1

2

3

b

1

0

4

5

1

0

1

b

2

1

4

5

0

1

 

Python Solution:

import pandas as pd

df_userfeatures = pd.read_csv(r’E:\datasets\12Dec\UserFeatures.txt’, sep=’\t’)
df_docfeatures = pd.read_csv(r’E:\datasets\12Dec\DocFeatures.txt’, sep=’\t’)
df_labeleddata = pd.read_csv(r’E:\datasets\12Dec\LabeledData.txt’, sep=’\t’)

byuseridfeatures = df_userfeatures.groupby([‘UserId’, ‘FeatureName’])
bydocidfeatures = df_docfeatures.groupby([‘DocId’, ‘FeatureName’])

df_byuserid_transpose = byuseridfeatures[‘FeatureValue’].mean().unstack().reset_index()
df_bydocid_transpose = bydocidfeatures[‘FeatureValue’].mean().unstack().reset_index()

df_joinintermediate = pd.merge(df_labeleddata, df_byuserid_transpose, left_on=’UserId’, right_on=’UserId’, how=’left’)
df_joined = pd.merge(df_joinintermediate, df_bydocid_transpose, left_on=’DocId’, right_on=’DocId’, how=’left’)

print df_joined
df_joined.to_csv(r’E:\datasets\12Dec\TransposeJoin_Python.txt’, index=False, sep=’\t’)

Output:

transposeJoin.JPG

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s