CODEWITHSUNDEEP
pythonpandaslistdataframeduplicates
Tom Kealy
Tom Kealy

Reputation: 2669

Reorder a pandas data frame based off a list with duplicated values

I have a dataframe in the following form:

    company  col1     col2   col3
    name
 0  A             0    130      0
 1  C           173      0      0
 2  Z             0      0    150
 3  A             0    145      0
 4  Z             0      0    140
 5  Z             0      0    110

And I would like to reorder the dataframe based on the following list:

list=['A', 'Z', 'Z', 'A', 'C', 'Z']

I can't set 'company name' as the index and the use df.reindex(list) as I have duplicate entries in that index. It's crucial that I have duplicate entries (the data is dummy data).

How do I reorder a df in this case?

Upvotes: 2

Views: 397

Answers (1)

jezrael
jezrael

Reputation: 863031

You can use merge with helper DataFrame created with list and counter column by cumcount:

Notice:
Dont use variable name list, because python code word. 

L = ['A', 'Z', 'Z', 'A', 'C', 'Z']

df1 = pd.DataFrame({'company':L})
df1['g'] = df1.groupby('company').cumcount()
df['g'] = df.groupby('company').cumcount()

df = df1.merge(df)
print (df)
  company  g  col1  col2  col3
0       A  0     0   130     0
1       Z  0     0     0   150
2       Z  1     0     0   140
3       A  1     0   145     0
4       C  0   173     0     0
5       Z  2     0     0   110

Upvotes: 1

Related Questions