问题描述
我有一个 pandas 数据框，该数据框是从Excel文件中读取的。由于Excel文件中的第1行具有重复的值，例如 245、245、245 ，因此我将它们读取为 pd.read_excel（file，'myfile'，标题=无），因此我可以防止熊猫创建标题 245、245.1、245.2 等。
I have a pandas Data Frame that I read in from an Excel file. Since row 1 in Excel file had repeating values such as 245, 245, 245, I read them as pd.read_excel(file, 'myfile', header = None), so I can prevent pandas creating headers 245, 245.1, 245.2 etc.
这是我的 df 的样子：
0 1 2 3 4
0 245 245 245 867 867
1 Reddit NaN NaN Facebook NaN
2 ColumnNeeded NaN ColumnValue ColumnNeeded ColumnValue
3 RedditInsight NaN C FacbookInsights A
4 RedditText NaN H FacbookText L
我需要这样的输出（ needed_df ），
I need my output like this (needed_df),
ID Company ColumnNeeded ColumnValue
0 245 Reddit RedditInsight C
1 245 Reddit RedditText H
2 867 Facebook FacbookInsight A
3 867 Facebook FacbookText L
不确定，如何在 pandas 。我试图从 df 中获取第1行中的所有唯一值。
Not sure, how to go about this in pandas. I tried to take all the unique values in Row 1 from df.
id_s = []
for i in df.iloc[0]:
id_s.append(i)
print(set(id_s))
unique_ids列表
list of unique_ids'
unique_id = list(set(id_s))
print(unique_id )
>> [867,245]
然后我想遍历 df's 第1行，然后在 unique_id 列表中找到所有匹配的值，然后将它们拆分为单独的小型数据框。
And then I wanted to iterate through df's row 1 and find all the matching values in unique_id list and then split them into a separate mini dataframes.
我无法得到那份工作。我的想法是创建迷你df1迷你数据帧，即：
I could not get that work. My thinking was to create mini data frames, mini df1 i.e.:
0 1 2
0 245 245 245
1 Reddit NaN NaN
2 ColumnNeeded NaN ColumnValue
3 RedditInsight NaN C
4 RedditText NaN H
迷你df2：
0 1
0 867 867
1 Facebook NaN
2 ColumnNeeded ColumnValue
3 FacbookInsights A
4 FacbookText L
I am thinking to do manipulation (possibly using a function, so I can apply to all mini dfs) to these mini dataframes and finally append them to a big dataframe. Or is there any other ideas or ways to do this to get my output dataframe?
推荐答案
您的DataFrame的创建如下：
Your DataFrame was created like the one below:
import pandas as pd
import numpy as np
df = pd.DataFrame([[245,245,245,867,867], ['Reddit', np.nan, np.nan,'Facebook',np.nan], ['ColumnNeeded',np.nan, 'ColumnValue', 'ColumnNeeded','ColumnValue'],
['RedditInsight', np.nan, 'C', 'FacebookInsights', 'A'], ['RedditText', np.nan, 'H', 'FacbookText', 'L']])
您的DataFrame看起来像这样：
Your DataFrame looks like this:
0 1 2 3 4
0 245 245.0 245 867 867
1 Reddit NaN NaN Facebook NaN
2 ColumnNeeded NaN ColumnValue ColumnNeeded ColumnValue
3 RedditInsight NaN C FacebookInsights A
4 RedditText NaN H FacbookText L
以及现在的代码。
new_header = df.iloc[0] #Grab the first row for the header
df = df[1:] #Take the data less the header row
df.columns = new_header #Set the header row as the df header
#Drop the column with all NaNs
df = df.dropna(axis=1, how='all')
df = df.T #Transpose
#Must find a way to do this part programtically
#Manually changing the index currently
df.index = [245.0, 245.1, 867.0, 867.1]
iPrev = ""
l1 = []
for i in df.index:
indexNow = str(i)[:3]
#print(indexNow)
if iPrev == indexNow:
#print(df.at[i, 3], df.at[i, 4])
l2.append(df.at[i, 3])
l3.append(df.at[i, 4])
l1.append(l2)
l1.append(l3)
l2 = []
l3 = []
else:
iPrev = indexNow
l2 = [i, df.at[i, 1], df.at[i, 3]]
l3 = [i, df.at[i, 1], df.at[i, 4]]
#print(l2)
result = pd.DataFrame(l1, columns = ['ID','Company','ColumnNeeded','ColumnValue'])
print(result)
礼物
ID Company ColumnNeeded ColumnValue
0 245.0 Reddit RedditInsight C
1 245.0 Reddit RedditText H
2 867.0 Facebook FacebookInsights A
3 867.0 Facebook FacbookText L
这篇关于使用大型数据框的行值创建迷你数据框-Pandas的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！