CODEWITHSUNDEEP
pythonregexcsvdataframereshape
Scott Riggs
Scott Riggs

Reputation: 65

Parse columns to reshape dataframe

I have a csv that I import as a dataframe with pandas. The columns are like:

Step1:A Step1:B Step1:C Step1:D Step2:A Step2:B Step2:D Step3:B Step3:D Step3:E
0         1        2        3       4      5       6       7       8     9

Where the step and parameter are separated by ':'. I want to reshape the dataframe to look like this:

    Step1   Step2   Step3
A   0          4    nan
B   1          5      7
C   2        nan    nan
D   3          6      8
E   nan      nan      9

Now, If I want to maintain column sequential order such that I have this case:

Step2:A  Step2:B    Step2:C  Step2:D    Step1:A   Step1:B   Step1:D  AStep3:B   AStep3:D    AStep3:E
0        1          2        3          4         5         6        7          8           9

Where the step and parameter are separated by ':'. I want to reshape the dataframe to look like this:

    Step2   Step1   AStep3
A   0          4    nan
B   1          5      7
C   2        nan    nan
D   3          6      8
E   nan      nan      9

Upvotes: 0

Views: 37

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150755

Try read_csv with delim_whitespace:

df = pd.read_csv('file.csv', delim_whitespace=True)
df.columns = df.columns.str.split(':', expand=True)
df.stack().reset_index(level=0, drop=True)

output:

   Step1  Step2  Step3
A    0.0    4.0    NaN
B    1.0    5.0    7.0
C    2.0    NaN    NaN
D    3.0    6.0    8.0
E    NaN    NaN    9.0

Upvotes: 1

Related Questions