Changing Data Frame style in pandas

Question

I have a dataframe like this:

+----------+---------------+---------------+-------------+
| Old_City | New_City_Code | New_City_Name |Old_City_Code|
+----------+---------------+---------------+-------------+
| a        | 101           | A             | 001         |
+----------+---------------+---------------+-------------+
| b        | 101           | A             | 002         |
+----------+---------------+---------------+-------------+
| c        | 102           | B             | 003         |
+----------+---------------+---------------+-------------+
| d        | 103           | C             | 004         |
+----------+---------------+---------------+-------------+
| e        | 103           | C             | 005         |
+----------+---------------+---------------+-------------+
| f        | 103           | C             | 006         |
+----------+---------------+---------------+-------------+

and I want to reshape this using pandas. The reshaped table should be:

+---------------+---------------+-----------+-----------+-----------+-----------+-----------+-----------+
| New_City_Code | New_City_Name | Old_City1 | Old_City2 | Old_City3 | Old_Code1 | Old_Code2 | Old_Code3 |
+---------------+---------------+-----------+-----------+-----------+-----------+-----------+-----------+
| 101           | A             | a         | b         |           | 001       | 002       |           |
+---------------+---------------+-----------+-----------+-----------+-----------+-----------+-----------+
| 102           | B             | c         |           |           | 003       |           |           |
+---------------+---------------+-----------+-----------+-----------+-----------+-----------+-----------+
| 103           | C             | d         | e         | f         | 004       | 005       | 006       |
+---------------+---------------+-----------+-----------+-----------+-----------+-----------+-----------+

Is there any way for this kind of conversion in pandas (or if there isn't in pandas, in R)? I tried pivot, but it didn't work (I got error ValueError: cannot label index with a null key).

jezrael · Accepted Answer · 2016-03-13 07:23:40Z

You can use groupby with cumcount for creatin column cols, then pivot_table with aggfunc='first' and last fillna by '' and reset_index:

print df
  Old_City  New_City_Code New_City_Name Old_City_Code
0        a            101             A           001
1        b            101             A           002
2        c            102             B           003
3        d            103             C           004
4        e            103             C           005
5        f            103             C           006

#create columns names for pivoting
df['cols'] = (df.groupby(['New_City_Name', 'New_City_Code']).cumcount() + 1).astype(str)

print df  
  Old_City  New_City_Code New_City_Name Old_City_Code cols
0        a            101             A           001    1
1        b            101             A           002    2
2        c            102             B           003    1
3        d            103             C           004    1
4        e            103             C           005    2
5        f            103             C           006    3    

df = pd.pivot_table(df, 
                    index=['New_City_Name', 'New_City_Code'], 
                    columns=['cols'], 
                    values=['Old_City','Old_City_Code'], 
                    aggfunc='first')

#remove multiindex in columns
df.columns = [''.join(col) for col in df.columns.values]
#replace NaN to '', reset index
df = df.fillna('').reset_index()

print df
  New_City_Name  New_City_Code Old_City1 Old_City2 Old_City3 Old_City_Code1  \
0             A            101         a         b                      001   
1             B            102         c                                003   
2             C            103         d         e         f            004   

  Old_City_Code2 Old_City_Code3  
0            002                 
1                                
2            005            006

Collectives™ on Stack Overflow

Changing Data Frame style in pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related