cᴏʟᴅsᴘᴇᴇᴅ cᴏʟᴅsᴘᴇᴇᴅ - 1 year ago 48
Python Question

How to handle custom named index when copying a dataframe using pd.read_clipboard?

Given this data frame from some other question:

Constraint Name TotalSP Onpeak Offpeak
Constraint_ID
77127 aaaaaaaaaaaaaaaaaa -2174.5 -2027.21 -147.29
98333 bbbbbbbbbbbbbbbbbb -1180.62 -1180.62 0
1049 cccccccccccccccccc -1036.53 -886.77 -149.76


It seems like there is an index
Constraint_ID
. When I try to read it in with
pd.read_clipboard
, this is how it gets loaded:

Constraint Name TotalSP Onpeak Offpeak
0 Constraint_ID NaN NaN NaN NaN
1 77127 aaaaaaaaaaaaaaaaaa -2174.50 -2027.21 -147.29
2 98333 bbbbbbbbbbbbbbbbbb -1180.62 -1180.62 0.00
3 1049 cccccccccccccccccc -1036.53 -886.77 -149.76


This is clearly wrong. How can I correct this?

Answer Source

read_clipboard by default uses whitespace to separate the columns. The problem you see is because of the whitespace in the first column. If you specify two or more spaces as the separator, based on the table format it will figure out the index column itself:

df = pd.read_clipboard(sep='\s{2,}')

df
Out: 
                  Constraint Name  TotalSP   Onpeak  Offpeak
Constraint_ID                                               
77127          aaaaaaaaaaaaaaaaaa -2174.50 -2027.21  -147.29
98333          bbbbbbbbbbbbbbbbbb -1180.62 -1180.62     0.00
1049           cccccccccccccccccc -1036.53  -886.77  -149.76

index_col argument can also be used to tell pandas the first column is the index, in case the structure cannot be inferred from the separator alone:

df = pd.read_clipboard(index_col=0, sep='\s{2,}')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download