[FIXED] python moving row data to the column above. Not working well with shift or merge

Issue

Here is a sample of flat file test data I have. Having no luck with shift or merge. Values end up all over the place. I need the values on the second line to be new columns lined up with data of the row above then remove or filter out the row the data came from. Data repeats like this for thousands of different items

001	050702XX005MUCX	363824014654	MUCX C/C LQ MAX STR6OZ	3.00	21.00	.00	.00	20.00	4.00
				28.88	190.55	.00	.00	184.31	35.12

The file space delimited, but not uniform in the spaces. I read the file in with df = pd.read_fwf('Store 001 Inventory FY22.txt',colspecs='infer'). everything appears to line up fine in df. I tried naming columns adding a date column and shifting:

df['adjusted_value'] = np.where(df['adjusted_units'] > 0, df.adjusted_units.shift(-1), " ")
df['sold_value'] = np.where(df['sold_units'] > 0, df.adjusted_units.shift(-1), " ")
df['opening_value'] = np.where(df['opening_units'] > 0, df.adjusted_units.shift(-1), " ")
df['received_value'] = np.where(df['received_units'] > 0, df.adjusted_units.shift(-1), " ")
df['trans_net_value'] = np.where(df['trans_net_units'] > 0, df.adjusted_units.shift(-1), " ")
df['closing_value'] = np.where(df['closing_units'] > 0, df.adjusted_units.shift(-1), " ")

df['date']= date

Also tried to merge:

df = df.loc[(df.index % 2) == 0].assign(jid=lambda d: d.index // 2).merge(
    df.loc[(df.index % 2) == 1].assign(jid=lambda d: d.index // 2), on="jid")[enter image description here][1]

Here is what the text data looks like
  [1]: https://i.stack.imgur.com/nesIM.jpg

Solution

Figured this out. may be helpful for someone in the future. I read the flat file in with read_fwf header=None and infer_nrows=5000. this allowed python to accurately delimit the file properly. I then made a df from every other row, a second df from every other row starting with 2nd line, then concat the two df with ignore_index=True.

Answered By - cytotox