I have this little issue I cannot get my head around. I have imported a rather messy csv which I initially created by converting a pdf to the said csv. Now, after some housekeeping (e.g. removing some rows containing unwanted string), I am left with a df that looks ALMOST ok.
Unnamed: 0 Benämning Unnamed: 2 id col1 col2 col3 \
0 1 item1 2090006400001 NaN 1 706,92 68 154,93 -2 730,37
1 2 item2 2386135200002 NaN 789,38 31 520,02 554,14
2 3 item3 7311041052112 C 1 935,00 29 495,95 9 015,04
3 4 item4 7311041072226 C 778,00 27 207,10 915,71
4 5 item5 2090213700000 C 1 189,70 24 888,11 9 450,73
col4 col5 col6 col7 col8 Col9 Col10 Col11 \
0 NaN -4,49 1 706,92 68 154,93 -2 730,37 -4,49 243,85 9 736,42
1 NaN 1,97 789,38 31 520,02 554,14 1,97 112,77 4 502,86
2 NaN 34,23 0,00 0,00 0,00 0,00 276,43 4 213,71
3 NaN 3,77 775,00 27 072,25 885,46 3,66 111,14 3 886,73
4 NaN 42,53 1 189,37 24 881,63 9 449,00 42,53 169,96 3 555,44
col12
0 -390,05
1 79,16
2 1 287,86
3 130,82
4 1 350,10
The only problem is that the columns ['Unnamed: 2','id','col1','col2','col3'] need to be shifted 1 step to the right. That is, the content of column ´´´Unnamed: 2should be in columnid, idinCol1``` and so on. But, these are the only columns that need to be shifted. Everything else is fine.
DF = DF.shift(axis=1) shifts all columns right and thus not only missplaces columns that originally were positioned where they should but also overwrites the last columns.
Is there a method to do this nicely without having to split the dataframe, do the shifting and then reassemble it?
Thankful for any insight.
Data: I provide a dict if anyone wants to try a solution:
{'Unnamed: 0': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5},
'Benämning': {0: 'item1', 1: 'item2', 2: 'item3', 3: 'item4', 4: 'item5'},
'Unnamed: 2': {0: 2090006400001,
1: 2386135200002,
2: 7311041052112,
3: 7311041072226,
4: 2090213700000},
'id': {0: nan, 1: nan, 2: 'C', 3: 'C', 4: 'C'},
'col1': {0: '1 706,92',
1: '789,38',
2: '1 935,00',
3: '778,00',
4: '1 189,70'},
'col2': {0: '68 154,93',
1: '31 520,02',
2: '29 495,95',
3: '27 207,10',
4: '24 888,11'},
'col3': {0: '-2 730,37',
1: '554,14',
2: '9 015,04',
3: '915,71',
4: '9 450,73'},
'col4': {0: nan, 1: nan, 2: nan, 3: nan, 4: nan},
'col5': {0: '-4,49', 1: '1,97', 2: '34,23', 3: '3,77', 4: '42,53'},
'col6': {0: '1 706,92', 1: '789,38', 2: '0,00', 3: '775,00', 4: '1 189,37'},
'col7': {0: '68 154,93',
1: '31 520,02',
2: '0,00',
3: '27 072,25',
4: '24 881,63'},
'col8': {0: '-2 730,37', 1: '554,14', 2: '0,00', 3: '885,46', 4: '9 449,00'},
'Col9': {0: '-4,49', 1: '1,97', 2: '0,00', 3: '3,66', 4: '42,53'},
'Col10': {0: '243,85', 1: '112,77', 2: '276,43', 3: '111,14', 4: '169,96'},
'Col11': {0: '9 736,42',
1: '4 502,86',
2: '4 213,71',
3: '3 886,73',
4: '3 555,44'},
'col12': {0: '-390,05',
1: '79,16',
2: '1 287,86',
3: '130,82',
4: '1 350,10'}}
CodePudding user response：
You can copy the columns and drop the 'Unnamed' one:
df[['id', 'col1', 'col2', 'col3', 'col4']] = df[['Unnamed: 2', 'id', 'col1', 'col2', 'col3']]
print(df.drop(columns='Unnamed: 2'))
if you don't want to worry about selecting the columns, use loc for automatic column alignment, but this will be slightly less efficient (need to shift all columns):
df.loc[:, ['id', 'col1', 'col2', 'col3', 'col4']] = df.shift(axis=1)
print(df.drop(columns='Unnamed: 2'))
output:
Unnamed: 0 Benämning id col1 col2 col3 col4 \
0 1 item1 2090006400001 NaN 1 706,92 68 154,93 -2 730,37
1 2 item2 2386135200002 NaN 789,38 31 520,02 554,14
2 3 item3 7311041052112 C 1 935,00 29 495,95 9 015,04
3 4 item4 7311041072226 C 778,00 27 207,10 915,71
4 5 item5 2090213700000 C 1 189,70 24 888,11 9 450,73
col5 col6 col7 col8 Col9 Col10 Col11 col12
0 -4,49 1 706,92 68 154,93 -2 730,37 -4,49 243,85 9 736,42 -390,05
1 1,97 789,38 31 520,02 554,14 1,97 112,77 4 502,86 79,16
2 34,23 0,00 0,00 0,00 0,00 276,43 4 213,71 1 287,86
3 3,77 775,00 27 072,25 885,46 3,66 111,14 3 886,73 130,82
4 42,53 1 189,37 24 881,63 9 449,00 42,53 169,96 3 555,44 1 350,10
CodePudding user response：
You can use rename where you use a dict comprehension to shift column names one place to the right.
cols = ['Unnamed: 2','id','col1','col2','col3','col4']
df = df.rename(columns={i:j for i,j in zip(cols, cols[1:])}).dropna(how='all', axis=1)
Per @jezrael (a cleaner way)
df = df.rename(columns=dict(zip(cols, cols[1:]))).dropna(how='all', axis=1)
Output:
Unnamed: 0 Benämning id col2 col3 col4 col5 \
0 1 item1 2090006400001 1 706,92 68 154,93 -2 730,37 -4,49
1 2 item2 2386135200002 789,38 31 520,02 554,14 1,97
2 3 item3 7311041052112 1 935,00 29 495,95 9 015,04 34,23
3 4 item4 7311041072226 778,00 27 207,10 915,71 3,77
4 5 item5 2090213700000 1 189,70 24 888,11 9 450,73 42,53
col6 col7 col8 Col9 Col10 Col11 col12
0 1 706,92 68 154,93 -2 730,37 -4,49 243,85 9 736,42 -390,05
1 789,38 31 520,02 554,14 1,97 112,77 4 502,86 79,16
2 0,00 0,00 0,00 0,00 276,43 4 213,71 1 287,86
3 775,00 27 072,25 885,46 3,66 111,14 3 886,73 130,82
4 1 189,37 24 881,63 9 449,00 42,53 169,96 3 555,44 1 350,10